Coding questions · Meta
Write SQL to localize anomaly and funnel
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.
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.
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:
- 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.
- 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).
- 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.
- 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.
Table users
| column | type | constraints |
|---|
| user_id | INT | PRIMARY KEY |
| region | VARCHAR(10) | NOT NULL |
Sample data:
| region | user_id |
|---|
| US | 1 |
| US | 2 |
| IN | 3 |
| BR | 4 |
Table posts
| column | type | constraints |
|---|
| post_id | INT | PRIMARY KEY |
| author_id | INT | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| author_id | created_at | post_id |
|---|
| 1 | 2025-08-30 | 10 |
| 2 | 2025-08-31 | 11 |
| 3 | 2025-09-01 | 12 |
Table feed_impressions
| column | type | constraints |
|---|
| user_id | INT | NOT NULL |
| post_id | INT | NOT NULL |
| created_at | DATE | NOT NULL |
| platform | VARCHAR(20) | NOT NULL |
| app_version | VARCHAR(20) | NOT NULL |
Sample data:
| app_version | created_at | platform | post_id | user_id |
|---|
| 10.5.0 | 2025-08-31 | iOS | 10 | 1 |
| 9.9.1 | 2025-08-31 | Android | 11 | 2 |
| 10.6.0 | 2025-09-01 | iOS | 11 | 1 |
| 9.9.2 | 2025-09-01 | Android | 12 | 3 |
| web | 2025-09-01 | Web | 12 | 4 |
Table likes
| column | type | constraints |
|---|
| user_id | INT | NOT NULL |
| post_id | INT | NOT NULL |
| created_at | DATE | NOT NULL |
| platform | VARCHAR(20) | NOT NULL |
| app_version | VARCHAR(20) | NOT NULL |
Sample data:
| app_version | created_at | platform | post_id | user_id |
|---|
| 10.5.0 | 2025-08-31 | iOS | 10 | 1 |
| 9.9.1 | 2025-08-31 | Android | 11 | 2 |
| 10.6.0 | 2025-09-01 | iOS | 11 | 1 |
Table app_sessions
| column | type | constraints |
|---|
| user_id | INT | NOT NULL |
| session_id | VARCHAR(50) | NOT NULL |
| session_date | DATE | NOT NULL |
| platform | VARCHAR(20) | NOT NULL |
| app_version | VARCHAR(20) | NOT NULL |
Sample data:
| app_version | platform | session_date | session_id | user_id |
|---|
| 10.5.0 | iOS | 2025-08-31 | s1 | 1 |
| 9.9.1 | Android | 2025-08-31 | s2 | 2 |
| 10.6.0 | iOS | 2025-09-01 | s3 | 1 |
| 9.9.2 | Android | 2025-09-01 | s4 | 3 |
| web | Web | 2025-09-01 | s5 | 4 |
Table outages
| column | type | constraints |
|---|
| date | DATE | NOT NULL |
| region | VARCHAR(10) | NOT NULL |
| outage_minutes | INT | NOT NULL |
Sample data:
| date | outage_minutes | region |
|---|
| 2025-09-01 | 45 | IN |
Expected output
One row per day, platform, and app_version where there were feed impressions between 2025-08-18 and 2025-09-01. For each row, the query returns distinct_viewers, distinct_likers, daily like_through_rate, the 14-day baseline LTR over 2025-08-18..2025-08-31 for that platform/app_version, the drop in LTR (in percentage points) on 2025-09-01 vs baseline (null for other days or when no baseline), and a flag is_flagged_drop (1 if the 2025-09-01 drop is at least 5 percentage points and a baseline exists, else 0). On the toy data, no segment meets the drop threshold, so all is_flagged_drop values are 0.
| app_version | baseline_ltr_14d | distinct_likers | distinct_viewers | drop_pct_points | is_flagged_drop | like_through_rate | metric_date | platform |
|---|
| 9.9.1 | 1 | 1 | 1 | NULL | 0 | 1 | 2025-08-31 | Android |
| 10.5.0 | 1 | 1 | 1 | NULL | 0 | 1 | 2025-08-31 | iOS |
| 9.9.2 | NULL | 0 | 1 | NULL | 0 | 0 | 2025-09-01 | Android |
| web | NULL | 0 | 1 | NULL | 0 | 0 | 2025-09-01 | Web |
| 10.6.0 | NULL | 1 | 1 | NULL | 0 | 1 | 2025-09-01 | iOS |
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:
- Feed viewers: distinct users who saw at least one feed impression on that date (from feed_impressions).
- 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).
- 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.
Table users
| column | type | constraints |
|---|
| user_id | INT | PRIMARY KEY |
| region | VARCHAR(10) | NOT NULL |
Sample data:
| region | user_id |
|---|
| US | 1 |
| US | 2 |
| IN | 3 |
| BR | 4 |
Table posts
| column | type | constraints |
|---|
| post_id | INT | PRIMARY KEY |
| author_id | INT | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| author_id | created_at | post_id |
|---|
| 1 | 2025-08-30 | 10 |
| 2 | 2025-08-31 | 11 |
| 3 | 2025-09-01 | 12 |
Table feed_impressions
| column | type | constraints |
|---|
| user_id | INT | NOT NULL |
| post_id | INT | NOT NULL |
| created_at | DATE | NOT NULL |
| platform | VARCHAR(20) | NOT NULL |
| app_version | VARCHAR(20) | NOT NULL |
Sample data:
| app_version | created_at | platform | post_id | user_id |
|---|
| 10.5.0 | 2025-08-31 | iOS | 10 | 1 |
| 9.9.1 | 2025-08-31 | Android | 11 | 2 |
| 10.6.0 | 2025-09-01 | iOS | 11 | 1 |
| 9.9.2 | 2025-09-01 | Android | 12 | 3 |
| web | 2025-09-01 | Web | 12 | 4 |
Table likes
| column | type | constraints |
|---|
| user_id | INT | NOT NULL |
| post_id | INT | NOT NULL |
| created_at | DATE | NOT NULL |
| platform | VARCHAR(20) | NOT NULL |
| app_version | VARCHAR(20) | NOT NULL |
Sample data:
| app_version | created_at | platform | post_id | user_id |
|---|
| 10.5.0 | 2025-08-31 | iOS | 10 | 1 |
| 9.9.1 | 2025-08-31 | Android | 11 | 2 |
| 10.6.0 | 2025-09-01 | iOS | 11 | 1 |
Table app_sessions
| column | type | constraints |
|---|
| user_id | INT | NOT NULL |
| session_id | VARCHAR(50) | NOT NULL |
| session_date | DATE | NOT NULL |
| platform | VARCHAR(20) | NOT NULL |
| app_version | VARCHAR(20) | NOT NULL |
Sample data:
| app_version | platform | session_date | session_id | user_id |
|---|
| 10.5.0 | iOS | 2025-08-31 | s1 | 1 |
| 9.9.1 | Android | 2025-08-31 | s2 | 2 |
| 10.6.0 | iOS | 2025-09-01 | s3 | 1 |
| 9.9.2 | Android | 2025-09-01 | s4 | 3 |
| web | Web | 2025-09-01 | s5 | 4 |
Table outages
| column | type | constraints |
|---|
| date | DATE | NOT NULL |
| region | VARCHAR(10) | NOT NULL |
| outage_minutes | INT | NOT NULL |
Sample data:
| date | outage_minutes | region |
|---|
| 2025-09-01 | 45 | IN |
Expected output
Two rows, one for 2025-08-31 and one for 2025-09-01. For each date, the query returns the count of distinct feed viewers (from feed_impressions), distinct users who had at least one 'like impression' (an impression for a post they liked that day), distinct likers, and the conversion rates viewer_to_like_impression_rate and like_impression_to_like_rate. On the toy data, 2025-08-31 has perfect funnel conversion, while 2025-09-01 has a drop from viewers to like impressions.
| event_date | feed_viewers | like_impression_to_like_rate | like_impression_viewers | likers | viewer_to_like_impression_rate |
|---|
| 2025-08-31 | 2 | 1 | 2 | 2 | 1 |
| 2025-09-01 | 3 | 1 | 1 | 1 | 0.3333 |
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:
- 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.
- 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.
- For each date, also compute overall_likes_per_session = average likes_per_session across all platform/app_version segments on that date.
- 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.
Table users
| column | type | constraints |
|---|
| user_id | INT | PRIMARY KEY |
| region | VARCHAR(10) | NOT NULL |
Sample data:
| region | user_id |
|---|
| US | 1 |
| US | 2 |
| IN | 3 |
| BR | 4 |
Table posts
| column | type | constraints |
|---|
| post_id | INT | PRIMARY KEY |
| author_id | INT | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| author_id | created_at | post_id |
|---|
| 1 | 2025-08-30 | 10 |
| 2 | 2025-08-31 | 11 |
| 3 | 2025-09-01 | 12 |
Table feed_impressions
| column | type | constraints |
|---|
| user_id | INT | NOT NULL |
| post_id | INT | NOT NULL |
| created_at | DATE | NOT NULL |
| platform | VARCHAR(20) | NOT NULL |
| app_version | VARCHAR(20) | NOT NULL |
Sample data:
| app_version | created_at | platform | post_id | user_id |
|---|
| 10.5.0 | 2025-08-31 | iOS | 10 | 1 |
| 9.9.1 | 2025-08-31 | Android | 11 | 2 |
| 10.6.0 | 2025-09-01 | iOS | 11 | 1 |
| 9.9.2 | 2025-09-01 | Android | 12 | 3 |
| web | 2025-09-01 | Web | 12 | 4 |
Table likes
| column | type | constraints |
|---|
| user_id | INT | NOT NULL |
| post_id | INT | NOT NULL |
| created_at | DATE | NOT NULL |
| platform | VARCHAR(20) | NOT NULL |
| app_version | VARCHAR(20) | NOT NULL |
Sample data:
| app_version | created_at | platform | post_id | user_id |
|---|
| 10.5.0 | 2025-08-31 | iOS | 10 | 1 |
| 9.9.1 | 2025-08-31 | Android | 11 | 2 |
| 10.6.0 | 2025-09-01 | iOS | 11 | 1 |
Table app_sessions
| column | type | constraints |
|---|
| user_id | INT | NOT NULL |
| session_id | VARCHAR(50) | NOT NULL |
| session_date | DATE | NOT NULL |
| platform | VARCHAR(20) | NOT NULL |
| app_version | VARCHAR(20) | NOT NULL |
Sample data:
| app_version | platform | session_date | session_id | user_id |
|---|
| 10.5.0 | iOS | 2025-08-31 | s1 | 1 |
| 9.9.1 | Android | 2025-08-31 | s2 | 2 |
| 10.6.0 | iOS | 2025-09-01 | s3 | 1 |
| 9.9.2 | Android | 2025-09-01 | s4 | 3 |
| web | Web | 2025-09-01 | s5 | 4 |
Table outages
| column | type | constraints |
|---|
| date | DATE | NOT NULL |
| region | VARCHAR(10) | NOT NULL |
| outage_minutes | INT | NOT NULL |
Sample data:
| date | outage_minutes | region |
|---|
| 2025-09-01 | 45 | IN |
Expected output
One row per session_date, platform, and app_version. Each row includes sessions, likes, likes_per_session, outage_minutes (maximum outage minutes affecting any user in that segment on that date), overall_likes_per_session (the average likes_per_session across segments for that date), and is_potential_logging_regression, which is 1 when likes_per_session is less than half the same-day overall average and the segment is not outage-affected (outage_minutes < 30). On the toy data, Android 9.9.2 on 2025-09-01 has zero likes per session but is covered by a 45-minute outage in region IN, so it is not flagged. Web/web on 2025-09-01 has zero likes per session without outage and is flagged as a potential logging regression.
| app_version | is_potential_logging_regression | likes | likes_per_session | outage_minutes | overall_likes_per_session | platform | session_date | sessions |
|---|
| 9.9.1 | 0 | 1 | 1 | 0 | 1 | Android | 2025-08-31 | 1 |
| 10.5.0 | 0 | 1 | 1 | 0 | 1 | iOS | 2025-08-31 | 1 |
| 9.9.2 | 0 | 0 | 0 | 45 | 0.3333 | Android | 2025-09-01 | 1 |
| web | 1 | 0 | 0 | 0 | 0.3333 | Web | 2025-09-01 | 1 |
| 10.6.0 | 0 | 1 | 1 | 0 | 0.3333 | iOS | 2025-09-01 | 1 |
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.