Coding questions · Meta

Write SQL/pandas for KPI anomaly

This question evaluates proficiency with SQL window functions and equivalent pandas operations for KPI anomaly detection, covering baseline computation using same-weekday medians over eight weeks, per-country and per-post_type Likes-per-DAU metrics with deduplication of users for DAU, outage filtering, attribution of declines between new and existing users, and time-series comparisons like friend-request acceptance rates while explicitly handling duplicates, sparse or missing DAU rows, and timezone issues. It is commonly asked in Data Manipulation (SQL/Python) interviews to assess practical data engineering and analytical skills, primarily testing practical application with an underlying need for conceptual understanding of baselining, attribution, and data-quality edge cases.

Write SQL (and outline equivalent pandas) for a KPI anomaly investigation. Assume today = '2025-09-01'. Schema:

Users(user_id INT, country TEXT, signup_date DATE)
Posts(post_id INT, user_id INT, post_type TEXT /* 'friend','page','event' */, created_at DATE)
Likes(like_id INT, user_id INT, post_id INT, created_at DATE)
FriendRequests(sender_id INT, receiver_id INT, created_at DATE, status TEXT /* 'sent','accepted','declined' */)
DailyActiveUsers(date DATE, country TEXT, dau INT)
Events(date DATE, outage_flag INT)
Sample rows:
Users
user_id | country | signup_date
1 | US | 2025-07-15
2 | US | 2025-08-20
3 | IN | 2025-08-01
4 | BR | 2025-07-01
5 | US | 2025-08-30
Posts
post_id | user_id | post_type | created_at
10 | 1 | friend | 2025-08-31
11 | 2 | page | 2025-08-31
12 | 3 | friend | 2025-09-01
13 | 4 | event | 2025-09-01
14 | 2 | friend | 2025-09-01
Likes
like_id | user_id | post_id | created_at
100 | 1 | 12 | 2025-09-01
101 | 2 | 10 | 2025-09-01
102 | 3 | 14 | 2025-09-01
103 | 4 | 10 | 2025-08-25
104 | 5 | 12 | 2025-09-01
FriendRequests
sender_id | receiver_id | created_at | status
1 | 2 | 2025-08-20 | accepted
2 | 3 | 2025-08-28 | sent
3 | 4 | 2025-08-29 | accepted
2 | 5 | 2025-08-31 | accepted
5 | 1 | 2025-09-01 | sent
DailyActiveUsers
date | country | dau
2025-08-25 | US | 3
2025-08-25 | IN | 1
2025-08-25 | BR | 1
2025-09-01 | US | 3
2025-09-01 | IN | 1
2025-09-01 | BR | 1
Events
date | outage_flag
2025-09-01 | 0

Tasks: (a) For each country and post_type on 2025-09-01, compute Likes-per-DAU and its percent change versus the median of the same weekday over the previous 8 weeks, excluding dates where outage_flag=1. Use window functions (e.g., PERCENTILE_CONT) and ensure users are counted once per day for DAU via DailyActiveUsers. (b) Flag the top 3 countries with the largest declines (≤ −10%) and, for each, attribute the decline between new users (signup_date ≥ '2025-08-02') and existing users. Return country, post_type, pct_change, share_of_decline_from_new_users. (c) For those flagged countries, compute the Friend Request acceptance rate in the last 14 days (2025-08-19 to 2025-09-01) and compare with the prior 14 days. Output the absolute and relative change, using window functions rather than correlated subqueries. (d) Provide a high-level pandas approach (groupby, merge, rolling/expanding, quantile) mirroring your SQL. Edge cases to handle explicitly: users with multiple Likes on the same day, countries with sparse DAU, missing DAU rows, and time zones (assume UTC).

Likes-per-DAU anomaly vs same-weekday median baseline

You are investigating a KPI anomaly for the date 2025-06-01 (UTC).

Compute, for each (country, post_type) on 2025-06-01:

Likes-per-DAU = (number of distinct users who liked that post_type on that date in that country) / (DAU for that country and date).
- Important: if a user likes multiple posts of the same post_type on the same day, count that user only once for the numerator.
- Use DailyActiveUsers for DAU (do not recompute DAU from Likes).
The percent change versus the median Likes-per-DAU of the same weekday over the previous 8 weeks.
- The previous 8 same-weekday dates for 2025-06-01 are Sundays from 2025-04-06 through 2025-05-25 (inclusive).
- Exclude any baseline dates where Events.outage_flag = 1.
- Use a window function median (e.g., PERCENTILE_CONT).

Output columns: country, post_type, likes_per_dau, baseline_median_likes_per_dau, pct_change_percent.

Edge cases to handle:

Users with multiple likes on the same day (dedupe by user/day/post_type).
Countries with sparse data.
Missing DAU rows (exclude rows where DAU is missing or 0).
Assume all dates are UTC (no time zone conversion needed).

Table `Users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL
signup_date	DATE	NOT NULL

Sample data:

country	signup_date	user_id
US	2025-03-15	1
US	2025-05-25	2
US	2025-05-31	5
US	2025-05-10	8
US	2025-01-05	9
US	2025-02-20	10
IN	2025-05-20	3
IN	2025-03-20	6
IN	2025-05-03	11
IN	2025-02-10	13
BR	2025-02-01	4
BR	2025-05-10	7
BR	2025-05-03	12

Table `Posts`

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

Sample data:

created_at	post_id	post_type	user_id
2025-03-10	10	friend	1
2025-03-05	11	page	9
2025-04-01	12	event	4
2025-03-25	13	friend	6
2025-05-18	14	page	3
2025-05-10	15	event	7

Table `Likes`

column	type	constraints
like_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
post_id	INT	NOT NULL
created_at	DATE	NOT NULL

Sample data:

created_at	like_id	post_id	user_id
2025-04-06	1001	10	1
2025-04-06	1002	10	9
2025-04-06	1003	10	10
2025-04-13	1004	10	1
2025-04-13	1005	10	9
2025-04-13	1006	10	10
2025-04-20	1007	10	1
2025-04-20	1008	10	9
2025-04-27	1009	10	1
2025-04-27	1010	10	9
2025-04-27	1011	10	10
2025-05-04	1012	10	1
2025-05-04	1013	10	2
2025-05-04	1014	10	8
2025-05-11	1015	10	1
2025-05-11	1016	10	2
2025-05-11	1017	10	8
2025-05-18	1018	10	1
2025-05-18	1019	10	2
2025-05-18	1020	10	8
2025-05-25	1021	10	1
2025-05-25	1022	10	2
2025-05-25	1023	10	8
2025-06-01	1024	10	1
2025-04-06	1025	11	1
2025-04-06	1026	11	9
2025-04-13	1027	11	1
2025-04-13	1028	11	10
2025-04-20	1029	11	1
2025-04-20	1030	11	9
2025-04-27	1031	11	1
2025-05-04	1032	11	2
2025-05-04	1033	11	8
2025-05-11	1034	11	2
2025-05-11	1035	11	8
2025-05-18	1036	11	1
2025-05-18	1037	11	2
2025-05-25	1038	11	2
2025-05-25	1039	11	8
2025-05-25	1040	14	2
2025-06-01	1041	11	2
2025-04-06	1042	12	1
2025-04-13	1043	12	1
2025-04-20	1044	12	1
2025-04-27	1045	12	1
2025-05-04	1046	12	1
2025-05-11	1047	12	1
2025-05-18	1048	12	1
2025-05-25	1049	12	1
2025-06-01	1050	12	5
2025-04-06	1051	13	6
2025-04-06	1052	13	13
2025-04-13	1053	13	6
2025-04-13	1054	13	13
2025-04-20	1055	13	6
2025-04-20	1056	13	13
2025-04-27	1057	13	6
2025-04-27	1058	13	13
2025-05-04	1059	13	6
2025-05-04	1060	13	11
2025-05-11	1061	13	6
2025-05-11	1062	13	11
2025-05-18	1063	13	6
2025-05-18	1064	13	11
2025-05-25	1065	13	6
2025-05-25	1066	13	11
2025-06-01	1067	13	6
2025-04-06	1068	11	6
2025-04-13	1069	11	6
2025-04-20	1070	11	6
2025-04-27	1071	11	6
2025-05-04	1072	11	11
2025-05-11	1073	11	11
2025-05-18	1074	11	11
2025-05-25	1075	11	11
2025-04-06	1076	12	6
2025-04-13	1077	12	6
2025-04-20	1078	12	6
2025-04-27	1079	12	6
2025-05-04	1080	12	6
2025-05-11	1081	12	6
2025-05-18	1082	12	6
2025-05-25	1083	12	6
2025-06-01	1084	12	3
2025-04-06	1085	10	4
2025-04-13	1086	10	4
2025-04-20	1087	10	4
2025-04-27	1088	10	4
2025-05-04	1089	10	12
2025-05-11	1090	10	12
2025-05-18	1091	10	12
2025-05-25	1092	10	12
2025-04-06	1093	11	4
2025-04-13	1094	11	4
2025-04-20	1095	11	4
2025-04-27	1096	11	4
2025-05-04	1097	11	12
2025-05-11	1098	11	12
2025-05-18	1099	11	12
2025-05-25	1100	11	12
2025-06-01	1101	11	4
2025-04-06	1102	12	4
2025-04-13	1103	12	4
2025-04-20	1104	12	4
2025-04-27	1105	12	4
2025-05-04	1106	12	4
2025-05-11	1107	12	4
2025-05-18	1108	12	4
2025-05-25	1109	12	4
2025-06-01	1110	15	7

Table `FriendRequests`

column	type	constraints
sender_id	INT	NOT NULL
receiver_id	INT	NOT NULL
created_at	DATE	NOT NULL
status	VARCHAR(10)	NOT NULL

Sample data:

created_at	receiver_id	sender_id	status
2025-05-06	9	1	accepted
2025-05-07	1	9	accepted
2025-05-10	10	8	accepted
2025-05-12	1	2	declined
2025-05-15	2	1	sent
2025-05-06	11	6	accepted
2025-05-12	6	13	declined
2025-05-08	12	4	accepted
2025-05-14	4	12	accepted
2025-05-20	8	1	accepted
2025-05-23	10	9	declined
2025-05-28	5	2	accepted
2025-05-30	1	8	declined
2025-05-22	13	6	declined
2025-05-27	6	11	declined
2025-05-31	6	3	sent
2025-05-21	7	4	accepted
2025-05-29	7	12	declined

Table `DailyActiveUsers`

column	type	constraints
date	DATE	NOT NULL
country	VARCHAR(2)	NOT NULL
dau	INT	NOT NULL

Sample data:

country	date	dau
US	2025-04-06	10
IN	2025-04-06	5
BR	2025-04-06	4
US	2025-04-13	10
IN	2025-04-13	5
BR	2025-04-13	4
US	2025-04-20	10
IN	2025-04-20	5
BR	2025-04-20	4
US	2025-04-27	10
IN	2025-04-27	5
BR	2025-04-27	4
US	2025-05-04	10
IN	2025-05-04	5
BR	2025-05-04	4
US	2025-05-11	10
IN	2025-05-11	5
BR	2025-05-11	4
US	2025-05-18	10
IN	2025-05-18	5
BR	2025-05-18	4
US	2025-05-25	10
IN	2025-05-25	5
BR	2025-05-25	4
US	2025-06-01	10
IN	2025-06-01	5
BR	2025-06-01	4

Table `Events`

column	type	constraints
date	DATE	PRIMARY KEY, NOT NULL
outage_flag	INT	NOT NULL

Sample data:

date	outage_flag
2025-04-06	0
2025-04-13	0
2025-04-20	0
2025-04-27	0
2025-05-04	0
2025-05-11	0
2025-05-15	1
2025-05-18	0
2025-05-25	0
2025-06-01	0

Expected output

One row per (country, post_type) for 2025-06-01 with the Likes-per-DAU, the median baseline Likes-per-DAU over the previous 8 Sundays, and the percent change (NULL if baseline median is 0/NULL).

baseline_median_likes_per_dau	country	likes_per_dau	pct_change_percent	post_type
0.25	BR	0.25	0	event
0.25	BR	0	-100	friend
0.25	BR	0.25	0	page
0.2	IN	0.2	0	event
0.4	IN	0.2	-50	friend
0.2	IN	0	-100	page
0.1	US	0.1	0	event
0.3	US	0.1	-66.6666666667	friend
0.2	US	0.1	-50	page

Hints

Aggregate likes as COUNT(DISTINCT user_id) per (date, country, post_type) to handle multiple likes by the same user.
Use DailyActiveUsers as the driving table and CROSS JOIN a post_type list to ensure 0-like combinations still appear.
Compute the baseline median with PERCENTILE_CONT(0.5) WITHIN GROUP and then join it back to the target-day rows.

Top declining countries and cohort attribution (new vs existing users)

Using the same KPI definition as in Question 1 for 2025-06-01:

Identify the top 3 countries with the largest KPI declines (pct_change_percent <= -10%), where each country is represented by its single worst post_type (most negative pct_change_percent).
For each flagged (country, post_type), attribute the decline to new users vs existing users.
- New users are those with signup_date >= '2025-05-02'.
- Compute a baseline median Likes-per-DAU for the new-user cohort and for all users (same weekday Sundays from 2025-04-06 to 2025-05-25).
- For 2025-06-01, compute Likes-per-DAU for the new-user cohort and for all users.
- Define:
```
  decline_total = baseline_median_total - today_total
  decline_new   = baseline_median_new   - today_new
  share_of_decline_from_new_users = decline_new / decline_total
```
  Return NULL for share if decline_total <= 0.

Output columns: country, post_type, pct_change_percent, share_of_decline_from_new_users.

Table `Users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL
signup_date	DATE	NOT NULL

Sample data:

country	signup_date	user_id
US	2025-03-15	1
US	2025-05-25	2
US	2025-05-31	5
US	2025-05-10	8
US	2025-01-05	9
US	2025-02-20	10
IN	2025-05-20	3
IN	2025-03-20	6
IN	2025-05-03	11
IN	2025-02-10	13
BR	2025-02-01	4
BR	2025-05-10	7
BR	2025-05-03	12

Table `Posts`

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

Sample data:

created_at	post_id	post_type	user_id
2025-03-10	10	friend	1
2025-03-05	11	page	9
2025-04-01	12	event	4
2025-03-25	13	friend	6
2025-05-18	14	page	3
2025-05-10	15	event	7

Table `Likes`

column	type	constraints
like_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
post_id	INT	NOT NULL
created_at	DATE	NOT NULL

Sample data:

created_at	like_id	post_id	user_id
2025-04-06	1001	10	1
2025-04-06	1002	10	9
2025-04-06	1003	10	10
2025-04-13	1004	10	1
2025-04-13	1005	10	9
2025-04-13	1006	10	10
2025-04-20	1007	10	1
2025-04-20	1008	10	9
2025-04-27	1009	10	1
2025-04-27	1010	10	9
2025-04-27	1011	10	10
2025-05-04	1012	10	1
2025-05-04	1013	10	2
2025-05-04	1014	10	8
2025-05-11	1015	10	1
2025-05-11	1016	10	2
2025-05-11	1017	10	8
2025-05-18	1018	10	1
2025-05-18	1019	10	2
2025-05-18	1020	10	8
2025-05-25	1021	10	1
2025-05-25	1022	10	2
2025-05-25	1023	10	8
2025-06-01	1024	10	1
2025-04-06	1025	11	1
2025-04-06	1026	11	9
2025-04-13	1027	11	1
2025-04-13	1028	11	10
2025-04-20	1029	11	1
2025-04-20	1030	11	9
2025-04-27	1031	11	1
2025-05-04	1032	11	2
2025-05-04	1033	11	8
2025-05-11	1034	11	2
2025-05-11	1035	11	8
2025-05-18	1036	11	1
2025-05-18	1037	11	2
2025-05-25	1038	11	2
2025-05-25	1039	11	8
2025-05-25	1040	14	2
2025-06-01	1041	11	2
2025-04-06	1042	12	1
2025-04-13	1043	12	1
2025-04-20	1044	12	1
2025-04-27	1045	12	1
2025-05-04	1046	12	1
2025-05-11	1047	12	1
2025-05-18	1048	12	1
2025-05-25	1049	12	1
2025-06-01	1050	12	5
2025-04-06	1051	13	6
2025-04-06	1052	13	13
2025-04-13	1053	13	6
2025-04-13	1054	13	13
2025-04-20	1055	13	6
2025-04-20	1056	13	13
2025-04-27	1057	13	6
2025-04-27	1058	13	13
2025-05-04	1059	13	6
2025-05-04	1060	13	11
2025-05-11	1061	13	6
2025-05-11	1062	13	11
2025-05-18	1063	13	6
2025-05-18	1064	13	11
2025-05-25	1065	13	6
2025-05-25	1066	13	11
2025-06-01	1067	13	6
2025-04-06	1068	11	6
2025-04-13	1069	11	6
2025-04-20	1070	11	6
2025-04-27	1071	11	6
2025-05-04	1072	11	11
2025-05-11	1073	11	11
2025-05-18	1074	11	11
2025-05-25	1075	11	11
2025-04-06	1076	12	6
2025-04-13	1077	12	6
2025-04-20	1078	12	6
2025-04-27	1079	12	6
2025-05-04	1080	12	6
2025-05-11	1081	12	6
2025-05-18	1082	12	6
2025-05-25	1083	12	6
2025-06-01	1084	12	3
2025-04-06	1085	10	4
2025-04-13	1086	10	4
2025-04-20	1087	10	4
2025-04-27	1088	10	4
2025-05-04	1089	10	12
2025-05-11	1090	10	12
2025-05-18	1091	10	12
2025-05-25	1092	10	12
2025-04-06	1093	11	4
2025-04-13	1094	11	4
2025-04-20	1095	11	4
2025-04-27	1096	11	4
2025-05-04	1097	11	12
2025-05-11	1098	11	12
2025-05-18	1099	11	12
2025-05-25	1100	11	12
2025-06-01	1101	11	4
2025-04-06	1102	12	4
2025-04-13	1103	12	4
2025-04-20	1104	12	4
2025-04-27	1105	12	4
2025-05-04	1106	12	4
2025-05-11	1107	12	4
2025-05-18	1108	12	4
2025-05-25	1109	12	4
2025-06-01	1110	15	7

Table `FriendRequests`

column	type	constraints
sender_id	INT	NOT NULL
receiver_id	INT	NOT NULL
created_at	DATE	NOT NULL
status	VARCHAR(10)	NOT NULL

Sample data:

created_at	receiver_id	sender_id	status
2025-05-06	9	1	accepted
2025-05-07	1	9	accepted
2025-05-10	10	8	accepted
2025-05-12	1	2	declined
2025-05-15	2	1	sent
2025-05-06	11	6	accepted
2025-05-12	6	13	declined
2025-05-08	12	4	accepted
2025-05-14	4	12	accepted
2025-05-20	8	1	accepted
2025-05-23	10	9	declined
2025-05-28	5	2	accepted
2025-05-30	1	8	declined
2025-05-22	13	6	declined
2025-05-27	6	11	declined
2025-05-31	6	3	sent
2025-05-21	7	4	accepted
2025-05-29	7	12	declined

Table `DailyActiveUsers`

column	type	constraints
date	DATE	NOT NULL
country	VARCHAR(2)	NOT NULL
dau	INT	NOT NULL

Sample data:

country	date	dau
US	2025-04-06	10
IN	2025-04-06	5
BR	2025-04-06	4
US	2025-04-13	10
IN	2025-04-13	5
BR	2025-04-13	4
US	2025-04-20	10
IN	2025-04-20	5
BR	2025-04-20	4
US	2025-04-27	10
IN	2025-04-27	5
BR	2025-04-27	4
US	2025-05-04	10
IN	2025-05-04	5
BR	2025-05-04	4
US	2025-05-11	10
IN	2025-05-11	5
BR	2025-05-11	4
US	2025-05-18	10
IN	2025-05-18	5
BR	2025-05-18	4
US	2025-05-25	10
IN	2025-05-25	5
BR	2025-05-25	4
US	2025-06-01	10
IN	2025-06-01	5
BR	2025-06-01	4

Table `Events`

column	type	constraints
date	DATE	PRIMARY KEY, NOT NULL
outage_flag	INT	NOT NULL

Sample data:

date	outage_flag
2025-04-06	0
2025-04-13	0
2025-04-20	0
2025-04-27	0
2025-05-04	0
2025-05-11	0
2025-05-15	1
2025-05-18	0
2025-05-25	0
2025-06-01	0

Expected output

Top 3 countries (each with its single worst post_type) with pct_change_percent <= -10%. Also show the share of the decline attributed to new users (signup_date >= 2025-05-02).

country	pct_change_percent	post_type	share_of_decline_from_new_users
IN	-100	page	0.5
BR	-100	friend	0.5
US	-66.6666666667	friend	0.5

Hints

To pick one post_type per country, compute pct_change at (country, post_type) granularity and then use ROW_NUMBER() over country ordered by pct_change ascending.
Compute new-user KPIs by filtering Users.signup_date in the likes aggregation (keep DAU unchanged).
Use two medians: one over likes_per_dau (all users) and one over likes_per_dau_new (new users), then compute share = (median_new - today_new)/(median_total - today_total).

Friend request acceptance rate change for flagged countries

For the countries flagged in Question 2 (top 3 countries with the largest declines), compute the Friend Request acceptance rate in:

Last 14 days: 2025-05-19 to 2025-06-01 (inclusive)
Prior 14 days: 2025-05-05 to 2025-05-18 (inclusive)

Definitions:

Attribute a friend request to the sender's country.
Consider only decided requests where status IN ('accepted','declined'). Ignore status='sent'.
acceptance_rate = accepted_count / decided_count.

Output per flagged country: country, acceptance_rate_last_14d, acceptance_rate_prior_14d, abs_change, rel_change where:

abs_change = last - prior
rel_change = (last - prior) / prior (NULL if prior is 0 or NULL)

Constraint: compute the comparison using window functions (e.g., LAG) rather than correlated subqueries.

Table `Users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL
signup_date	DATE	NOT NULL

Sample data:

country	signup_date	user_id
US	2025-03-15	1
US	2025-05-25	2
US	2025-05-31	5
US	2025-05-10	8
US	2025-01-05	9
US	2025-02-20	10
IN	2025-05-20	3
IN	2025-03-20	6
IN	2025-05-03	11
IN	2025-02-10	13
BR	2025-02-01	4
BR	2025-05-10	7
BR	2025-05-03	12

Table `Posts`

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

Sample data:

created_at	post_id	post_type	user_id
2025-03-10	10	friend	1
2025-03-05	11	page	9
2025-04-01	12	event	4
2025-03-25	13	friend	6
2025-05-18	14	page	3
2025-05-10	15	event	7

Table `Likes`

column	type	constraints
like_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
post_id	INT	NOT NULL
created_at	DATE	NOT NULL

Sample data:

created_at	like_id	post_id	user_id
2025-04-06	1001	10	1

Table `FriendRequests`

column	type	constraints
sender_id	INT	NOT NULL
receiver_id	INT	NOT NULL
created_at	DATE	NOT NULL
status	VARCHAR(10)	NOT NULL

Sample data:

created_at	receiver_id	sender_id	status
2025-05-06	9	1	accepted
2025-05-07	1	9	accepted
2025-05-10	10	8	accepted
2025-05-12	1	2	declined
2025-05-15	2	1	sent
2025-05-06	11	6	accepted
2025-05-12	6	13	declined
2025-05-08	12	4	accepted
2025-05-14	4	12	accepted
2025-05-20	8	1	accepted
2025-05-23	10	9	declined
2025-05-28	5	2	accepted
2025-05-30	1	8	declined
2025-05-22	13	6	declined
2025-05-27	6	11	declined
2025-05-31	6	3	sent
2025-05-21	7	4	accepted
2025-05-29	7	12	declined

Table `DailyActiveUsers`

column	type	constraints
date	DATE	NOT NULL
country	VARCHAR(2)	NOT NULL
dau	INT	NOT NULL

Sample data:

country	date	dau
US	2025-06-01	10

Table `Events`

column	type	constraints
date	DATE	PRIMARY KEY, NOT NULL
outage_flag	INT	NOT NULL

Sample data:

date	outage_flag
2025-06-01	0

Expected output

One row per flagged country showing acceptance rate in the last vs prior 14-day windows, plus absolute and relative change.

abs_change	acceptance_rate_last_14d	acceptance_rate_prior_14d	country	rel_change
-0.5	0.5	1	BR	-0.5
-0.5	0	0.5	IN	-1
-0.25	0.5	0.75	US	-0.3333333333

Hints

Label each friend request row into 'prior_14d' vs 'last_14d' using a CASE expression on created_at.
Compute acceptance_rate per (country, period) using conditional sums and a NULLIF denominator.
Use LAG or a pivot-style aggregation to compare last vs prior without correlated subqueries.

Likes-per-DAU anomaly vs same-weekday median baseline

Table Users

Table Posts

Table Likes

Table FriendRequests

Table DailyActiveUsers

Table Events

Expected output

Hints

Top declining countries and cohort attribution (new vs existing users)

Table Users

Table Posts

Table Likes

Table FriendRequests

Table DailyActiveUsers

Table Events

Expected output

Hints

Friend request acceptance rate change for flagged countries

Table Users

Table Posts

Table Likes

Table FriendRequests

Table DailyActiveUsers

Table Events

Expected output

Hints

Table `Users`

Table `Posts`

Table `Likes`

Table `FriendRequests`

Table `DailyActiveUsers`

Table `Events`

Table `Users`

Table `Posts`

Table `Likes`

Table `FriendRequests`

Table `DailyActiveUsers`

Table `Events`

Table `Users`

Table `Posts`

Table `Likes`

Table `FriendRequests`

Table `DailyActiveUsers`

Table `Events`