Diagnose DAU drop with SQL by country
Company: Yahoo
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Write a single SQL query to diagnose a country-level DAU drop for an email product. Assume today is 2025-09-01. DAU is the count of distinct user_id with at least one of event_type IN ('login','open_mail','send_mail','push_click') on a date. Exclude is_bot = TRUE events. Also exclude users who are in experiments where exp_name = 'mail_redesign' AND exp_group = 'treatment' if the experiment is active on the date (start_date <= date <= end_date). Compare 2025-09-01 to the average DAU over the prior 7 days (2025-08-25..2025-08-31). Output the top 3 countries by absolute DAU drop with columns: country, dau_prev7d_avg, dau_today, drop_abs, drop_pct. Additionally, for each of those countries, also output dau_today_returning and dau_today_new, where returning users have created_at < '2025-08-25' and new users have created_at BETWEEN '2025-08-25' AND '2025-09-01'. Break ties in drop_abs by larger drop_pct. Use only standard SQL constructs (CTEs, window functions allowed). Schema and tiny sample data:
users
| user_id | country | created_at |
|--------:|---------|-------------|
| 1 | US | 2025-08-10 |
| 2 | US | 2025-08-26 |
| 3 | IN | 2025-08-01 |
| 4 | IN | 2025-08-30 |
| 5 | US | 2025-07-15 |
| 6 | IN | 2025-08-27 |
events
| event_date | user_id | event_type | device | app_version | is_bot |
|------------|---------|-------------|---------|-------------|--------|
| 2025-08-25 | 1 | login | ios | 10.2 | 0 |
| 2025-08-31 | 1 | open_mail | ios | 10.2 | 0 |
| 2025-09-01 | 1 | open_mail | ios | 10.2 | 0 |
| 2025-08-28 | 2 | login | android | 10.3 | 0 |
| 2025-09-01 | 2 | login | android | 10.3 | 0 |
| 2025-08-27 | 3 | login | web | - | 0 |
| 2025-08-30 | 3 | open_mail | web | - | 0 |
| 2025-09-01 | 3 | open_mail | web | - | 0 |
| 2025-08-31 | 4 | login | android | 10.1 | 0 |
| 2025-09-01 | 4 | login | android | 10.1 | 0 |
| 2025-08-25 | 5 | login | ios | 10.0 | 0 |
| 2025-08-31 | 5 | login | ios | 10.0 | 0 |
| 2025-09-01 | 5 | login | ios | 10.1 | 0 |
| 2025-09-01 | 6 | login | web | - | 1 |
experiments
| user_id | exp_name | exp_group | start_date | end_date |
|---------|-----------------|-----------|------------|------------|
| 2 | mail_redesign | treatment | 2025-08-29 | 2025-09-10 |
| 3 | mail_redesign | control | 2025-08-29 | 2025-09-10 |
Be careful to: (a) de-duplicate users per day across multiple events/devices, (b) filter experiment treatment users only for dates when the experiment is active, (c) compute the 7-day average correctly even if a country has zero DAU on some baseline days.
Overview: This question evaluates SQL data-manipulation and analytical skills, including DAU aggregation with per-day deduplication, date-range baseline averaging, experiment-treatment exclusion, country-level cohorting, and new vs returning user segmentation.
Write a single SQL query to diagnose a country-level DAU drop for an email product. Assume today is 2025-06-01.
DAU for a given date is the count of distinct user_id who have at least one event with event_type IN ('login', 'open_mail', 'send_mail', 'push_click') on that date. Exclude any events where is_bot = TRUE.
Also exclude users who are in experiments where exp_name = 'mail_redesign' AND exp_group = 'treatment' on dates when that experiment is active (start_date <= event_date AND event_date <= end_date).
Compare 2025-06-01 to the average DAU over the prior 7 days, from 2025-05-25 to 2025-05-31 (inclusive). For each country, compute:
- dau_prev7d_avg: average DAU in the 7-day window 2025-05-25..2025-05-31
- dau_today: DAU on 2025-06-01
- drop_abs: dau_prev7d_avg - dau_today
- drop_pct: drop_abs / dau_prev7d_avg (NULL if dau_prev7d_avg = 0)
Return the top 3 countries by absolute DAU drop (ORDER BY ABS(drop_abs) DESC), breaking ties by larger drop_pct (DESC).
Additionally, for each of those top-3 countries, also output:
- dau_today_returning: count of today's DAU users with created_at < '2025-05-25'
- dau_today_new: count of today's DAU users with created_at BETWEEN '2025-05-25' AND '2025-06-01'
Be careful to:
(a) De-duplicate users per day across multiple events/devices (i.e., each user counts at most once per country per day).
(b) Filter experiment treatment users only for dates when the experiment is active.
(c) Compute the 7-day average correctly even if a country has zero DAU on some of the baseline days (those days should still count in the denominator of 7).
Use only standard SQL constructs (CTEs and window functions are allowed).
Tables
users(user_id INT, country VARCHAR(10), created_at DATE)
events(event_date DATE, user_id INT, event_type VARCHAR(20), device VARCHAR(20), app_version VARCHAR(10), is_bot BOOLEAN)
experiments(user_id INT, exp_name VARCHAR(50), exp_group VARCHAR(20), start_date DATE, end_date DATE)
Hints
- First build a CTE that filters events for valid event types, removes bots, and excludes users in an active 'mail_redesign' treatment arm on that date, then de-duplicate by (event_date, user_id).
- Create a calendar CTE for the baseline 7 days and cross join it with the list of countries so that days with zero DAU are still included when computing the 7-day average.