Compute daily post success rate for last 7 days

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

Compute daily post success rate for the last 7 days

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'.

Table composer

column	type	constraints
user_id	INT	NOT NULL
event	VARCHAR(10)	NOT NULL, CHECK (event IN ('enter','post','cancel'))
event_date	DATE	NOT NULL

Sample data:

event	event_date	user_id
enter	2025-05-26	1
post	2025-05-26	1
enter	2025-05-26	2
enter	2025-05-27	3
cancel	2025-05-27	3
enter	2025-05-29	4
post	2025-05-29	4
post	2025-05-30	5

Expected output

One row per date from 2025-05-26 through 2025-06-01 (inclusive) with the daily post_success_rate rounded to 2 decimals. Days with zero enters (including days with no events) must show 0.00.

event_date	post_success_rate
2025-05-26	0.5
2025-05-27	0
2025-05-28	0
2025-05-29	1
2025-05-30	0
2025-05-31	0
2025-06-01	0

Hints

1. Build a date spine from 2025-05-26 to 2025-06-01 so days with no events still appear.
2. Aggregate enters/posts per day, then LEFT JOIN to the date spine and use COALESCE(posts / NULLIF(enters,0), 0).