Write SQL to localize anomaly and funnel
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Given the schema and toy data below, write SQL to (a) validate instrumentation vs behavior change, (b) localize the 2025-09-01 Likes drop by app_version and platform, and (c) build a daily funnel. Schema: users(user_id INT, region STRING); posts(post_id INT, author_id INT, created_at DATE); feed_impressions(user_id INT, post_id INT, created_at DATE, platform STRING, app_version STRING); likes(user_id INT, post_id INT, created_at DATE, platform STRING, app_version STRING); app_sessions(user_id INT, session_id STRING, session_date DATE, platform STRING, app_version STRING); outages(date DATE, region STRING, outage_minutes INT). Sample tables (ASCII):
Users
user_id | region
1 | US
2 | US
3 | IN
4 | BR
Posts
post_id | author_id | created_at
10 | 1 | 2025-08-30
11 | 2 | 2025-08-31
12 | 3 | 2025-09-01
Feed_Impressions
user_id | post_id | created_at | platform | app_version
1 | 10 | 2025-08-31 | iOS | 10.5.0
2 | 11 | 2025-08-31 | Android | 9.9.1
1 | 11 | 2025-09-01 | iOS | 10.6.0
3 | 12 | 2025-09-01 | Android | 9.9.2
4 | 12 | 2025-09-01 | Web | web
Likes
user_id | post_id | created_at | platform | app_version
1 | 10 | 2025-08-31 | iOS | 10.5.0
2 | 11 | 2025-08-31 | Android | 9.9.1
1 | 11 | 2025-09-01 | iOS | 10.6.0
App_Sessions
user_id | session_id | session_date | platform | app_version
1 | s1 | 2025-08-31 | iOS | 10.5.0
2 | s2 | 2025-08-31 | Android | 9.9.1
1 | s3 | 2025-09-01 | iOS | 10.6.0
3 | s4 | 2025-09-01 | Android | 9.9.2
4 | s5 | 2025-09-01 | Web | web
Outages
date | region | outage_minutes
2025-09-01 | IN | 45
Tasks: 1) Write a query that, for 2025-08-18..2025-09-01, computes daily like-through-rate (distinct likers / distinct viewers) by platform and app_version, and flags segments with a ≥5 percentage-point drop on 2025-09-01 vs their prior 14-day mean; 2) Write a funnel query for 2025-08-31 and 2025-09-01: feed viewers → like impressions (join on impressions) → likes, with stage-to-stage conversion; 3) Write a data-quality check that compares likes per session across platforms and versions to detect logging regressions (e.g., sudden zeros on iOS 10.6.0). Explain how you’d join outages to avoid false alarms.
Overview: This question evaluates SQL-based product analytics skills, including anomaly detection, funnel construction, joins and aggregations, and data-quality/instrumentation checks across platform and app_version.
Daily Like-Through-Rate Anomaly Detection by Platform and App Version
Using the tables below, write a SQL query that, for the date range 2025-08-18 to 2025-09-01 (inclusive), computes the daily like-through-rate (LTR) by platform and app_version and flags possible anomalies on 2025-09-01.
Definitions:
- A feed viewer for a given day/platform/app_version is any distinct user_id that has at least one row in feed_impressions on that day with that platform and app_version.
- A liker for a given day/platform/app_version is any distinct user_id that has at least one row in likes on that same day with the same platform and app_version.
- Daily like-through-rate (LTR) = distinct_likers / distinct_viewers for that day/platform/app_version.
Requirements:
1. For all feed_impressions between 2025-08-18 and 2025-09-01, compute a daily table with:
- metric_date,
- platform,
- app_version,
- distinct_viewers,
- distinct_likers,
- like_through_rate.
2. For each (platform, app_version), compute its prior 14-day baseline LTR as the average of daily LTR over 2025-08-18 to 2025-08-31 (inclusive).
3. Join the baseline back to the daily metrics and, for 2025-09-01 only, compute the drop in percentage points vs the baseline: (baseline_ltr - like_through_rate) * 100.
4. Add a column is_flagged_drop that is 1 when, on 2025-09-01, the drop in LTR is at least 5 percentage points and the segment has a non-null baseline; otherwise 0.
Return one row per metric_date, platform, and app_version for all dates that have impressions in the range.
Tables
users(user_id INT, region VARCHAR(10))
posts(post_id INT, author_id INT, created_at DATE)
feed_impressions(user_id INT, post_id INT, created_at DATE, platform VARCHAR(20), app_version VARCHAR(20))
likes(user_id INT, post_id INT, created_at DATE, platform VARCHAR(20), app_version VARCHAR(20))
app_sessions(user_id INT, session_id VARCHAR(50), session_date DATE, platform VARCHAR(20), app_version VARCHAR(20))
outages(date DATE, region VARCHAR(10), outage_minutes INT)
Hints
- First aggregate feed_impressions and likes by date, platform, and app_version to get distinct viewers and likers.
- Compute the 14-day baseline in a separate CTE and join it back to the daily metrics; only calculate the drop and flag for the 2025-09-01 rows.
Daily Feed-to-Like Funnel for Key Dates
Using the same schema, write a SQL query that builds a simple daily funnel for 2025-08-31 and 2025-09-01 with three stages:
1. Feed viewers: distinct users who saw at least one feed impression on that date (from feed_impressions).
2. Like impressions: distinct users who saw an impression for a post that they liked on that same date (join feed_impressions to likes on user_id, post_id, created_at).
3. Likers: distinct users who created at least one like on that date (from likes).
For each of the two dates (2025-08-31 and 2025-09-01), return:
- event_date,
- feed_viewers,
- like_impression_viewers,
- likers,
- viewer_to_like_impression_rate = like_impression_viewers / feed_viewers,
- like_impression_to_like_rate = likers / like_impression_viewers (null if like_impression_viewers is 0).
Assume the provided toy data.
Tables
users(user_id INT, region VARCHAR(10))
posts(post_id INT, author_id INT, created_at DATE)
feed_impressions(user_id INT, post_id INT, created_at DATE, platform VARCHAR(20), app_version VARCHAR(20))
likes(user_id INT, post_id INT, created_at DATE, platform VARCHAR(20), app_version VARCHAR(20))
app_sessions(user_id INT, session_id VARCHAR(50), session_date DATE, platform VARCHAR(20), app_version VARCHAR(20))
outages(date DATE, region VARCHAR(10), outage_minutes INT)
Hints
- Compute each funnel stage (feed viewers, like-impression viewers, likers) in separate CTEs, grouped by date.
- Join the three stage aggregates on date and then compute the conversion ratios with CASE expressions to avoid division by zero.
Likes-Per-Session Data Quality Check with Outage Adjustment
Using the same schema, write a SQL query to compute a data-quality metric that compares likes per session across platforms and app versions, and uses outages to reduce false alarms about logging regressions.
Requirements:
1. For each session_date, platform, and app_version, compute:
- sessions: count of distinct session_id from app_sessions.
- likes: count of likes on that same date for that platform and app_version, joined by user_id and date (join app_sessions to likes on user_id = user_id, platform, app_version, and session_date = created_at).
- likes_per_session = likes / sessions.
2. Join in outages via users and regions so that you can tell which platform/app_version segments are affected by regional outages:
- Join app_sessions to users on user_id.
- Join to outages on outages.date = app_sessions.session_date and outages.region = users.region.
- Aggregate outages at the segment level as outage_minutes = MAX(outage_minutes) for that date/platform/app_version.
3. For each date, also compute overall_likes_per_session = average likes_per_session across all platform/app_version segments on that date.
4. Add a flag is_potential_logging_regression that is 1 when, for a segment:
- likes_per_session < 0.5 * overall_likes_per_session, and
- outage_minutes < 30 (i.e., the segment is not strongly impacted by an outage),
otherwise 0.
Return one row per session_date, platform, and app_version. Use the toy data as-is (even if it does not include an actual iOS logging regression); your query should still correctly identify segments whose likes_per_session are much lower than the same-day average but not explained by outages.
Tables
users(user_id INT, region VARCHAR(10))
posts(post_id INT, author_id INT, created_at DATE)
feed_impressions(user_id INT, post_id INT, created_at DATE, platform VARCHAR(20), app_version VARCHAR(20))
likes(user_id INT, post_id INT, created_at DATE, platform VARCHAR(20), app_version VARCHAR(20))
app_sessions(user_id INT, session_id VARCHAR(50), session_date DATE, platform VARCHAR(20), app_version VARCHAR(20))
outages(date DATE, region VARCHAR(10), outage_minutes INT)
Hints
- First aggregate app_sessions and likes by session_date, platform, and app_version to get sessions, likes, and likes_per_session for each segment.
- To incorporate outages, join app_sessions to users and then to outages on matching session_date and region, aggregate outage_minutes per segment (e.g., with MAX), and then compute same-day overall likes_per_session using a window function so you can flag low segments that are not explained by outages.