Calculate Day-7 Retention Rate from User Post Data

post_activity

+---------+------------+-------+
| user_id | post_date  | posts |
+---------+------------+-------+
| 1       | 2023-01-01 | 3     |
| 1       | 2023-01-02 | 2     |
| 2       | 2023-04-10 | 1     |
| 2       | 2023-04-11 | 4     |
| 3       | 2023-05-05 | 5     |
+---------+------------+-------+

Scenario

SQL data wrangling for user posting behavior analytics.

Question

Given a table of daily post counts, derive each user's registration date (their first post_date). For every user, calculate the total number of posts made within their first 7 days on the platform. Compute the Day-7 retention rate: the proportion of users who posted on their 7th day relative to those who posted on Day-1.

Hints

Use window functions, date arithmetic, group by, and conditional aggregation.

Day-7 Retention from Daily Post Activity

You are given a table of daily post counts per user. A user's registration date is defined as their first post_date. For every user, compute the total number of posts made within their first 7 days on the platform (registration date counted as Day-1, so the window is registration_date through registration_date + 6 days). Also compute the Day-7 retention rate, defined as the number of users who posted on their 7th day divided by the total number of users who posted on Day-1. Return both a per-user summary and the overall retention metrics in a single query.

Return one combined result set with columns result_set, user_id, registration_date, total_posts_first_7_days, posted_on_day7, day7_users, day1_users, and day7_retention_rate. Use result_set = 'user_summary' for per-user rows and result_set = 'retention_rate' for the aggregate row; fields that do not apply to a row should be NULL.

Table post_activity

column	type	constraints
user_id	INTEGER	NOT NULL
post_date	DATE	NOT NULL
posts	INTEGER	NOT NULL

Sample data:

post_date	posts	user_id
2023-01-01	3	1
2023-01-02	2	1
2023-04-10	1	2
2023-04-11	4	2
2023-05-05	5	3

Expected output

Combined per-user day-7 summary rows plus one aggregate retention-rate row.

day1_users	day7_retention_rate	day7_users	posted_on_day7	registration_date	result_set	total_posts_first_7_days	user_id
3	0.0000	0	NULL	NULL	retention_rate	NULL	NULL
NULL	NULL	NULL	false	2023-01-01	user_summary	5	1
NULL	NULL	NULL	false	2023-04-10	user_summary	5	2
NULL	NULL	NULL	false	2023-05-05	user_summary	5	3

Hints

1. Use a per-user CTE before computing the aggregate retention row.
2. Use NULLs for columns that do not apply to the aggregate row.