Write SQL/pandas for KPI anomaly
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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).
Overview: 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.
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:
1) 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).
2) 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).
Tables
Users(user_id INT, country VARCHAR(2), signup_date DATE)
Posts(post_id INT, user_id INT, post_type VARCHAR(10), 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 VARCHAR(10))
DailyActiveUsers(date DATE, country VARCHAR(2), dau INT)
Events(date DATE, outage_flag INT)
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.
Top declining countries and cohort attribution (new vs existing users)
Using the same KPI definition as in Question 1 for 2025-06-01:
1) 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).
2) 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.
Tables
Users(user_id INT, country VARCHAR(2), signup_date DATE)
Posts(post_id INT, user_id INT, post_type VARCHAR(10), 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 VARCHAR(10))
DailyActiveUsers(date DATE, country VARCHAR(2), dau INT)
Events(date DATE, outage_flag INT)
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).
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.
Tables
Users(user_id INT, country VARCHAR(2), signup_date DATE)
Posts(post_id INT, user_id INT, post_type VARCHAR(10), 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 VARCHAR(10))
DailyActiveUsers(date DATE, country VARCHAR(2), dau INT)
Events(date DATE, outage_flag INT)
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.