Coding questions · Meta
Write SQL for engagement and attribution KPIs
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.
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:
- For each calendar day, compute total posts created and the percentage that received ≥1 non‑deleted comment within 24 hours of post creation.
- 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.
- 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.
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:
- post_date: DATE of posts.created_at
- total_posts: number of posts created on that date
- commented_posts_24h: number of posts that received at least one non-deleted comment within 24 hours after the post's created_at timestamp
- 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.
Table posts
| column | type | constraints |
|---|---|---|
| post_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
Sample data:
| created_at | post_id | user_id |
|---|---|---|
| 2025-04-30 09:00:00 | 120 | 3 |
| 2025-05-20 10:00:00 | 101 | 1 |
| 2025-05-21 12:00:00 | 102 | 1 |
| 2025-05-21 13:30:00 | 103 | 2 |
| 2025-05-22 09:00:00 | 104 | 3 |
| 2025-05-25 15:00:00 | 105 | 3 |
| 2025-06-01 08:00:00 | 106 | 4 |
| 2025-05-23 11:00:00 | 107 | 1 |
| 2025-05-24 11:00:00 | 108 | 1 |
| 2025-05-28 11:00:00 | 109 | 1 |
| 2025-05-30 11:00:00 | 110 | 1 |
| 2025-05-26 10:00:00 | 111 | 3 |
| 2025-05-27 10:00:00 | 112 | 3 |
| 2025-05-28 10:00:00 | 113 | 3 |
| 2025-05-30 10:00:00 | 114 | 3 |
| 2025-05-29 14:00:00 | 115 | 2 |
| 2025-05-31 14:00:00 | 116 | 2 |
| 2025-05-18 14:00:00 | 117 | 2 |
Table comments
| column | type | constraints |
|---|---|---|
| comment_id | INT | PRIMARY KEY, NOT NULL |
| post_id | INT | NOT NULL |
| user_id | INT | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
| is_deleted | SMALLINT | NOT NULL |
Sample data:
| comment_id | created_at | is_deleted | post_id | user_id |
|---|---|---|---|---|
| 1001 | 2025-05-20 15:00:00 | 0 | 101 | 2 |
| 1002 | 2025-05-22 11:00:00 | 0 | 101 | 3 |
| 1003 | 2025-05-22 10:00:00 | 1 | 104 | 4 |
| 1004 | 2025-05-23 09:30:00 | 0 | 104 | 2 |
| 1005 | 2025-05-25 16:00:00 | 0 | 105 | 1 |
| 1006 | 2025-06-02 07:00:00 | 0 | 106 | 2 |
| 1007 | 2025-05-01 08:00:00 | 0 | 120 | 2 |
| 1008 | 2025-05-24 10:00:00 | 0 | 107 | 3 |
| 1009 | 2025-05-26 12:00:00 | 0 | 108 | 2 |
| 1010 | 2025-05-28 12:00:00 | 0 | 109 | 4 |
| 1011 | 2025-05-31 12:00:00 | 0 | 110 | 3 |
| 1012 | 2025-05-26 11:00:00 | 0 | 111 | 1 |
| 1013 | 2025-05-27 18:00:00 | 0 | 112 | 2 |
| 1014 | 2025-05-29 10:30:00 | 0 | 113 | 4 |
| 1015 | 2025-05-30 11:30:00 | 1 | 114 | 1 |
| 1016 | 2025-05-30 13:00:00 | 0 | 115 | 1 |
| 1017 | 2025-06-02 15:00:00 | 0 | 116 | 3 |
| 1018 | 2025-05-19 12:00:00 | 0 | 117 | 4 |
Expected output
One row per calendar day with posts, showing total posts created, how many received at least one non-deleted comment within 24 hours, and the corresponding rate.
| commented_posts_24h | commented_rate_24h | post_date | total_posts |
|---|---|---|---|
| 1 | 1 | 2025-04-30 | 1 |
| 1 | 1 | 2025-05-18 | 1 |
| 1 | 1 | 2025-05-20 | 1 |
| 0 | 0 | 2025-05-21 | 2 |
| 0 | 0 | 2025-05-22 | 1 |
| 1 | 1 | 2025-05-23 | 1 |
| 0 | 0 | 2025-05-24 | 1 |
| 1 | 1 | 2025-05-25 | 1 |
| 1 | 1 | 2025-05-26 | 1 |
| 1 | 1 | 2025-05-27 | 1 |
| 1 | 0.5 | 2025-05-28 | 2 |
| 1 | 1 | 2025-05-29 | 1 |
| 0 | 0 | 2025-05-30 | 2 |
| 0 | 0 | 2025-05-31 | 1 |
| 1 | 1 | 2025-06-01 | 1 |
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'.
- Aggregate from posts with a left join so posts without qualifying comments remain in the denominator.
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:
- total_posts in this date window
- commented_posts_24h: posts with at least one non-deleted comment within 24 hours after creation
- 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.
Table posts
| column | type | constraints |
|---|---|---|
| post_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
Sample data:
| created_at | post_id | user_id |
|---|---|---|
| 2025-04-30 09:00:00 | 120 | 3 |
| 2025-05-20 10:00:00 | 101 | 1 |
| 2025-05-21 12:00:00 | 102 | 1 |
| 2025-05-21 13:30:00 | 103 | 2 |
| 2025-05-22 09:00:00 | 104 | 3 |
| 2025-05-25 15:00:00 | 105 | 3 |
| 2025-06-01 08:00:00 | 106 | 4 |
| 2025-05-23 11:00:00 | 107 | 1 |
| 2025-05-24 11:00:00 | 108 | 1 |
| 2025-05-28 11:00:00 | 109 | 1 |
| 2025-05-30 11:00:00 | 110 | 1 |
| 2025-05-26 10:00:00 | 111 | 3 |
| 2025-05-27 10:00:00 | 112 | 3 |
| 2025-05-28 10:00:00 | 113 | 3 |
| 2025-05-30 10:00:00 | 114 | 3 |
| 2025-05-29 14:00:00 | 115 | 2 |
| 2025-05-31 14:00:00 | 116 | 2 |
| 2025-05-18 14:00:00 | 117 | 2 |
Table comments
| column | type | constraints |
|---|---|---|
| comment_id | INT | PRIMARY KEY, NOT NULL |
| post_id | INT | NOT NULL |
| user_id | INT | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
| is_deleted | SMALLINT | NOT NULL |
Sample data:
| comment_id | created_at | is_deleted | post_id | user_id |
|---|---|---|---|---|
| 1001 | 2025-05-20 15:00:00 | 0 | 101 | 2 |
| 1002 | 2025-05-22 11:00:00 | 0 | 101 | 3 |
| 1003 | 2025-05-22 10:00:00 | 1 | 104 | 4 |
| 1004 | 2025-05-23 09:30:00 | 0 | 104 | 2 |
| 1005 | 2025-05-25 16:00:00 | 0 | 105 | 1 |
| 1006 | 2025-06-02 07:00:00 | 0 | 106 | 2 |
| 1007 | 2025-05-01 08:00:00 | 0 | 120 | 2 |
| 1008 | 2025-05-24 10:00:00 | 0 | 107 | 3 |
| 1009 | 2025-05-26 12:00:00 | 0 | 108 | 2 |
| 1010 | 2025-05-28 12:00:00 | 0 | 109 | 4 |
| 1011 | 2025-05-31 12:00:00 | 0 | 110 | 3 |
| 1012 | 2025-05-26 11:00:00 | 0 | 111 | 1 |
| 1013 | 2025-05-27 18:00:00 | 0 | 112 | 2 |
| 1014 | 2025-05-29 10:30:00 | 0 | 113 | 4 |
| 1015 | 2025-05-30 11:30:00 | 1 | 114 | 1 |
| 1016 | 2025-05-30 13:00:00 | 0 | 115 | 1 |
| 1017 | 2025-06-02 15:00:00 | 0 | 116 | 3 |
| 1018 | 2025-05-19 12:00:00 | 0 | 117 | 4 |
Expected output
Top creators (by user_id) over the fixed window 2025-05-03 to 2025-06-01, ordered by 24-hour commented-post rate, then total_posts, then user_id. Only creators with at least 5 posts are included. With the sample data, two creators qualify.
| commented_posts_24h | commented_rate_24h | total_posts | user_id |
|---|---|---|---|
| 3 | 0.5 | 6 | 1 |
| 3 | 0.5 | 6 | 3 |
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.
- Apply the minimum-post threshold before ranking, then rank by rate descending, total_posts descending, and user_id ascending.
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.
Table email_sends
| column | type | constraints |
|---|---|---|
| send_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| campaign | VARCHAR(50) | NOT NULL |
| send_ts | TIMESTAMP | NOT NULL |
Sample data:
| campaign | send_id | send_ts | user_id |
|---|---|---|---|
| reengage_v1 | 5001 | 2025-05-20 09:00:00 | 2 |
| reengage_v1 | 5002 | 2025-05-20 09:05:00 | 3 |
| reengage_v1 | 5003 | 2025-05-21 10:00:00 | 1 |
| welcome | 5004 | 2025-05-21 11:00:00 | 4 |
Table email_events
| column | type | constraints |
|---|---|---|
| event_id | INT | PRIMARY KEY, NOT NULL |
| send_id | INT | NOT NULL |
| event_type | VARCHAR(20) | NOT NULL |
| event_ts | TIMESTAMP | NOT NULL |
Sample data:
| event_id | event_ts | event_type | send_id |
|---|---|---|---|
| 6001 | 2025-05-20 09:00:30 | delivered | 5001 |
| 6002 | 2025-05-20 09:10:00 | open | 5001 |
| 6003 | 2025-05-20 09:12:00 | click | 5001 |
| 6004 | 2025-05-20 09:05:30 | bounce | 5002 |
| 6005 | 2025-05-21 10:00:20 | delivered | 5003 |
| 6006 | 2025-05-21 10:05:00 | open | 5003 |
| 6007 | 2025-05-21 10:15:00 | open | 5003 |
| 6008 | 2025-05-21 11:00:10 | delivered | 5004 |
| 6009 | 2025-05-21 11:05:00 | open | 5004 |
| 6010 | 2025-05-21 11:06:00 | click | 5004 |
| 6011 | 2025-05-20 09:20:00 | open | 5001 |
| 6012 | 2025-05-20 09:25:00 | click | 5001 |
Expected output
Per day metrics for campaign 'reengage_v1'. For each send date, counts of delivered sends (with a delivered event), unique opens, unique clicks, and the corresponding open rate and click-through rate.
| campaign | ctr | date | delivered_sends | open_rate | unique_clicks | unique_opens |
|---|---|---|---|---|---|---|
| reengage_v1 | 1 | 2025-05-20 | 1 | 1 | 1 | 1 |
| reengage_v1 | 0 | 2025-05-21 | 1 | 1 | 0 | 1 |
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.