Daily Post Success Rate Over the Last Week

EasySQL

Reported in interviews at Meta

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_idcreated_atevent_name
1012026-03-01 09:12:00enter
1012026-03-01 09:14:30post
1022026-03-01 18:40:00enter
1022026-03-01 18:41:10cancel
1032026-02-22 10:00:00enter
1032026-02-22 10:02:00post
1042026-03-04 07:05:00enter
1042026-03-04 07:06:00post
1052026-03-04 21:30:00enter
1052026-03-04 21:31:00post
1062026-03-04 22:00:00enter
1062026-03-04 22:03:00cancel
1072026-03-05 11:00:00enter
1072026-03-05 11:01:00cancel

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):

daysuccess_rate
2026-03-010.5
2026-03-040.6667
2026-03-050.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.

Related free problems