Write SQL to localize anomaly and funnel

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Meta.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Meta during technical interviews.

Question

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.

PracHub · Accepted Answer

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.

Quick Overview

Quick Overview