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.
Overview: 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.
Daily 24-hour commented-post rate
Using the PostgreSQL posts and comments tables defined below, compute daily post engagement.
For each calendar day on which at least one post was created, return:
1. post_date: DATE of posts.created_at
2. total_posts: number of posts created on that date
3. commented_posts_24h: number of posts that received at least one non-deleted comment within 24 hours after the post's created_at timestamp
4. commented_rate_24h: commented_posts_24h / total_posts as a decimal
Rules:
- comments.is_deleted is an integer flag where 0 means the comment counts and 1 means it is deleted.
- Treat timestamps as UTC.
- Count each qualifying post once even if it has multiple qualifying comments.
Return rows ordered by post_date.
Tables
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 SMALLINT)
Hints
- Use a DISTINCT post_id CTE to avoid double-counting posts with multiple qualifying comments.
- Use a half-open interval: comment created_at >= post created_at and < post created_at + INTERVAL '24 hours'.
Top creators by 24-hour commented-post rate over a fixed 30-day window
Using the same PostgreSQL posts and comments tables, consider only posts created from 2025-05-03 through 2025-06-01, inclusive.
For each creator (posts.user_id), compute:
1. total_posts in this date window
2. commented_posts_24h: posts with at least one non-deleted comment within 24 hours after creation
3. commented_rate_24h: commented_posts_24h / total_posts as a decimal
Return the top 3 creators by commented_rate_24h, but only include creators with at least 5 posts in the window. Break ties by higher total_posts, then lower user_id.
Rules:
- comments.is_deleted is an integer flag where 0 means the comment counts and 1 means it is deleted.
- Count each qualifying post once even if it has multiple qualifying comments.
Tables
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 SMALLINT)
Hints
- Reuse a DISTINCT post_id marker CTE for qualifying non-deleted comments within 24 hours.
- Filter posts by DATE(created_at) between the two inclusive DATE literals before aggregating per user.
Daily unique open rate and click-through rate for an email campaign
Using the email_sends and email_events tables, compute daily engagement metrics for campaign 'reengage_v1'. For each calendar day (based on email_sends.send_ts) and this campaign, compute: delivered_sends = number of send_ids that have a 'delivered' event (exclude bounced sends), unique_opens = number of delivered send_ids that have at least one 'open' event, unique_clicks = number of delivered send_ids that have at least one 'click' event, open_rate = unique_opens / delivered_sends, and ctr = unique_clicks / delivered_sends. Count at most one open and one click per send_id in the numerators. Return campaign, date, delivered_sends, unique_opens, unique_clicks, open_rate, ctr.
Tables
email_sends(send_id INT, user_id INT, campaign VARCHAR(50), send_ts TIMESTAMP)
email_events(event_id INT, send_id INT, event_type VARCHAR(20), event_ts TIMESTAMP)
Hints
- Start by identifying delivered sends for the target campaign using a CTE that joins email_sends and email_events on event_type = 'delivered'.
- From that delivered set, derive distinct send_ids with opens and clicks, then aggregate per (campaign, send_date) to compute counts and rates.