Compute daily post success rate for last 7 days
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.
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