Compute daily post success rate for last 7 days
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You have a table composer(user_id INT, event STRING CHECK(event IN ('enter','post','cancel')), event_date DATE). Compute the post success rate for each calendar date in the last 7 days, taking "today" as 2025-09-01 (so the window is 2025-08-26 through 2025-09-01, inclusive). Define daily post success rate = count(event='post') / NULLIF(count(event='enter'), 0) computed per event_date. Requirements: 1) Output one row per date in the window even if there is no activity that day (use 0.00 when enters=0). 2) Return columns: event_date, post_success_rate (rounded to 2 decimals). 3) Avoid integer division. 4) Treat duplicate events from the same user independently (do not de-duplicate users). 5) Do not assume referential integrity across days. Example sample data:
composer
user_id | event | event_date
1 | enter | 2025-08-26
1 | post | 2025-08-26
2 | enter | 2025-08-26
3 | enter | 2025-08-27
3 | cancel | 2025-08-27
4 | enter | 2025-08-29
4 | post | 2025-08-29
5 | post | 2025-08-30
Overview: This question evaluates data manipulation and analytical competencies in Data Manipulation (SQL/Python), focusing on time-windowed aggregations, rate calculations, handling nulls/zero denominators, numeric type and rounding considerations, and producing per-calendar-date outputs.
Read the full Meta Data Scientist interview experience this question came from
You have a table `composer(user_id INT, event STRING, event_date DATE)` that records user actions. Each row is an event and duplicate events from the same user should be counted independently (do not de-duplicate users).
Using the fixed 'current date' of 2025-06-01, compute the daily post success rate for each calendar date in the 7-day window FROM 2025-05-26 TO 2025-06-01 (inclusive).
Definition:
- daily post success rate (per `event_date`) = count(event = 'post') / NULLIF(count(event = 'enter'), 0)
Requirements:
1) Output exactly one row per calendar date in the window even if there is no activity that day.
2) If `count(enter) = 0`, output `0.00` for that day.
3) Return columns: `event_date`, `post_success_rate` (rounded to 2 decimals).
4) Avoid integer division.
5) Do not assume referential integrity across days; compute per day only.
Events are limited to: 'enter', 'post', 'cancel'.
Tables
composer(user_id INT, event VARCHAR(10), event_date DATE)
Hints
- Build a date spine from 2025-05-26 to 2025-06-01 so days with no events still appear.
- Aggregate enters/posts per day, then LEFT JOIN to the date spine and use COALESCE(posts / NULLIF(enters,0), 0).
Community answers
Answer by a.vaghefi
/*
post_success_rate for each event_date
one row per date. use 0 when enter.
we should handle days without any post event
*/
WITH params AS (
SELECT
DATE('2025-08-26') AS start_date,
DATE('2025-09-01') AS end_date
),
-- 7-day date spine (inclusive)
dates AS (
SELECT start_date AS event_date
FROM params
UNION ALL
SELECT event_date + INTERVAL '1' DAY
FROM dates d
JOIN params p ON 1=1
WHERE d.event_date < p.end_date
),
base AS (
SELECT
d.event_date,
COALESCE(SUM(CASE WHEN c.event = 'post' THEN 1 ELSE 0 END), 0) AS post_on_date,
COALESCE(SUM(CASE WHEN c.event = 'enter' THEN 1 ELSE 0 END), 0) AS enters_on_date,
ROUND(
100 * COALESCE(
(1.0 * SUM(CASE WHEN c.event = 'post' THEN 1 ELSE 0 END)) /
NULLIF(SUM(CASE WHEN c.event = 'enter' THEN 1 ELSE 0 END), 0),
0
),
2
) AS post_success_rate
FROM dates d
LEFT JOIN composer c
ON c.event_date = d.event_date
GROUP BY 1
)
SELECT event_date, post_success_rate
FROM base;