Daily Post Success Rate Over the Last Week
Problem statement
A social app logs what happens in its post composer:
post_events(user_id INT, created_at TEXT, event_name TEXT): created_at is 'YYYY-MM-DD HH:MM:SS'; event_name is 'enter' (the user opened the composer), 'post' (the user published) or 'cancel' (the user abandoned the draft).
The post success rate for a day is the number of 'post' events that day divided by the number of 'enter' events that day. Write one query (SQLite-compatible) that returns the success rate for each day of the past week, where the past week means the 7 calendar days ending on the most recent date that appears in the table (inclusive). Skip days with no 'enter' events. Return day ('YYYY-MM-DD') and success_rate (a decimal), ordered by day.
Example rows:
| user_id | created_at | event_name |
|---|---|---|
| 101 | 2026-03-01 09:12:00 | enter |
| 101 | 2026-03-01 09:14:30 | post |
| 102 | 2026-03-01 18:40:00 | enter |
| 102 | 2026-03-01 18:41:10 | cancel |
| 103 | 2026-02-22 10:00:00 | enter |
| 103 | 2026-02-22 10:02:00 | post |
| 104 | 2026-03-04 07:05:00 | enter |
| 104 | 2026-03-04 07:06:00 | post |
| 105 | 2026-03-04 21:30:00 | enter |
| 105 | 2026-03-04 21:31:00 | post |
| 106 | 2026-03-04 22:00:00 | enter |
| 106 | 2026-03-04 22:03:00 | cancel |
| 107 | 2026-03-05 11:00:00 | enter |
| 107 | 2026-03-05 11:01:00 | cancel |
Expected output (the latest date is 2026-03-05, so the week is 2026-02-27 to 2026-03-05 and the 2026-02-22 events fall outside it):
| day | success_rate |
|---|---|
| 2026-03-01 | 0.5 |
| 2026-03-04 | 0.6667 |
| 2026-03-05 | 0.0 |
Show hint
How can you count two different event types in the same grouped pass over the table?
Relevant roles: Data Scientist and Forward Deployed Engineer.