Diagnose DAU drop with SQL by country

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.

Diagnose Country-Level DAU Drop with Experiments and New/Returning Split

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).

Table users

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
country	VARCHAR(10)	NOT NULL
created_at	DATE	NOT NULL

Sample data:

country	created_at	user_id
US	2025-05-10	1
US	2025-05-26	2
IN	2025-05-01	3
IN	2025-05-30	4
US	2025-04-15	5
IN	2025-05-27	6

Table events

column	type	constraints
event_date	DATE	NOT NULL
user_id	INT	NOT NULL
event_type	VARCHAR(20)	NOT NULL
device	VARCHAR(20)	NOT NULL
app_version	VARCHAR(10)	NOT NULL
is_bot	BOOLEAN	NOT NULL

Sample data:

app_version	device	event_date	event_type	is_bot	user_id
10.2	ios	2025-05-25	login	false	1
10.2	ios	2025-05-31	open_mail	false	1
10.2	ios	2025-06-01	open_mail	false	1
10.3	android	2025-05-28	login	false	2
10.3	android	2025-06-01	login	false	2
-	web	2025-05-27	login	false	3
-	web	2025-05-30	open_mail	false	3
-	web	2025-06-01	open_mail	false	3
10.1	android	2025-05-31	login	false	4
10.1	android	2025-06-01	login	false	4
10.0	ios	2025-05-25	login	false	5
10.0	ios	2025-05-31	login	false	5
10.1	ios	2025-06-01	login	false	5
-	web	2025-06-01	login	true	6

Table experiments

column	type	constraints
user_id	INT	NOT NULL
exp_name	VARCHAR(50)	NOT NULL
exp_group	VARCHAR(20)	NOT NULL
start_date	DATE	NOT NULL
end_date	DATE	NOT NULL

Sample data:

end_date	exp_group	exp_name	start_date	user_id
2025-06-10	treatment	mail_redesign	2025-05-29	2
2025-06-10	control	mail_redesign	2025-05-29	3

Expected output

One row per top-3 country, showing the 7-day baseline average DAU, DAU on 2025-06-01, absolute and percentage drop (baseline minus today), and the split between returning and new users for 2025-06-01.

country	dau_prev7d_avg	dau_today	dau_today_new	dau_today_returning	drop_abs	drop_pct
IN	0.429	2	1	1	-1.571	-3.662
US	0.714	2	0	2	-1.286	-1.801

Hints

1. 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).
2. 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.