Write SQL for engagement and attribution KPIs
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
Using the schema and sample data below, answer the SQL tasks. Assume timestamps are UTC and comments with is_deleted=1 do not count. Schema:
users(user_id INT, created_at TIMESTAMP, locale STRING)
posts(post_id INT, user_id INT, created_at TIMESTAMP)
comments(comment_id INT, post_id INT, user_id INT, created_at TIMESTAMP, is_deleted TINYINT)
email_sends(send_id INT, user_id INT, campaign STRING, send_ts TIMESTAMP)
email_events(event_id INT, send_id INT, event_type STRING /* delivered|bounce|open|click */, event_ts TIMESTAMP)
Sample tables (tiny subset):
users
user_id | created_at | locale
1 | 2025-07-01 00:00:00 | en_US
2 | 2025-07-10 00:00:00 | en_US
3 | 2025-08-02 00:00:00 | fr_FR
4 | 2025-08-15 00:00:00 | en_US
posts
post_id | user_id | created_at
101 | 1 | 2025-08-31 10:00:00
102 | 1 | 2025-09-01 12:00:00
103 | 2 | 2025-09-01 13:30:00
104 | 3 | 2025-09-02 09:00:00
comments
comment_id | post_id | user_id | created_at | is_deleted
1001 | 101 | 2 | 2025-09-01 11:00:00 | 0
1002 | 101 | 3 | 2025-09-03 08:00:00 | 0
1003 | 104 | 4 | 2025-09-02 10:00:00 | 1
1004 | 104 | 2 | 2025-09-03 09:00:00 | 0
email_sends
send_id | user_id | campaign | send_ts
5001 | 2 | reengage_v1 | 2025-09-01 09:00:00
5002 | 3 | reengage_v1 | 2025-09-01 09:05:00
email_events
event_id | send_id | event_type | event_ts
6001 | 5001 | delivered | 2025-09-01 09:00:30
6002 | 5001 | open | 2025-09-01 09:10:00
6003 | 5001 | click | 2025-09-01 09:12:00
6004 | 5002 | bounce | 2025-09-01 09:05:30
Tasks:
1) For each calendar day, compute total posts created and the percentage that received ≥1 non‑deleted comment within 24 hours of post creation.
2) For the last 30 days, list the top 3 creators (user_id) by that 24‑hour commented‑post rate, requiring at least 5 posts per creator; break ties by higher post count, then lower user_id.
3) For campaign reengage_v1, compute daily unique open rate and click‑through rate on delivered emails: denominator = delivered sends (exclude bounces), numerator = at most one open or one click per send_id; return campaign, date, delivered_sends, unique_opens, unique_clicks, open_rate, ctr.
Quick Answer: This question evaluates SQL data-manipulation skills for engagement and attribution KPIs, including joins, time-window (24-hour) analysis, deduplication of event data, conditional aggregation, and correct denominator construction for rate metrics.