Calculate User Registration Date and 7-Day Retention Rate

user_posts

+---------+--------------+-----------+
| user_id | posting_date | num_posts |
+---------+--------------+-----------+
| 1       | 2023-01-01   | 3         |
| 1       | 2023-01-02   | 2         |
| 2       | 2023-02-10   | 1         |
| 2       | 2023-02-15   | 4         |
| 3       | 2023-03-05   | 1         |
+---------+--------------+-----------+

Scenario

Given a posting log table, calculate each user’s registration date, posts in their first 7 days, and the 7-day retention rate.

Question

Write SQL to derive each user’s first posting date (registration). Compute total posts each user made within 7 days of registration. Compute overall 7-day retention rate (share of users with any post on day 7 or later).

Hints

Use window functions, DATE_DIFF (or equivalent), CTEs for registration, and conditional aggregation.

User Registration Date, First 7-Day Posts, and 7-Day Retention

You have a posting-activity log in the table user_posts, where each row records how many posts a user made on a given calendar date.

column	type	description
user_id	INTEGER	the user who posted
posting_date	DATE	the calendar date of the activity
num_posts	INTEGER	how many posts that user made on that date

A user may have multiple rows (one per active date). For each user, define their registration date as their earliest posting_date. "Day 0" is the registration date itself, "day 6" is six days after it, and so on.

Write a single PostgreSQL query that returns one row per user with the following columns:

• user_id
• registration_date — the user's earliest posting_date.
• posts_first_7_days — the total number of posts (sum of num_posts) the user made during their first 7 days, i.e. on days 0 through 6 inclusive (posting_date between registration_date and registration_date + 6 days).
• retained_7d — 1 if the user has at least one post on day 7 or later (posting_date >= registration_date + 7 days), otherwise 0.
• overall_7d_retention_rate — the share of all users that are 7-day retained (count of users with retained_7d = 1 divided by the total number of users), rounded to 4 decimal places and repeated on every output row.

Order the result by user_id ascending.

Table user_posts

column	type	constraints
user_id	INTEGER	NOT NULL
posting_date	DATE	NOT NULL
num_posts	INTEGER	NOT NULL

Sample data:

num_posts	posting_date	user_id
3	2023-01-01	1
2	2023-01-02	1
4	2023-01-10	1
1	2023-02-10	2
4	2023-02-15	2
2	2023-02-17	2
1	2023-03-05	3

Expected output

One row per user with registration_date, total posts made in the first 7 days (days 0-6), a 1/0 retained_7d flag (post on day 7+), and the overall 7-day retention rate (2 of 3 users retained = 0.6667) repeated on every row, ordered by user_id.

overall_7d_retention_rate	posts_first_7_days	registration_date	retained_7d	user_id
0.6667	5	2023-01-01	1	1
0.6667	5	2023-02-10	1	2
0.6667	1	2023-03-05	0	3

Hints

1. Derive each user's registration date with MIN(posting_date) OVER (PARTITION BY user_id), then group by user.
2. In PostgreSQL, date_a - date_b returns an integer number of days — use it directly instead of any DATEDIFF/DATE_DIFF function.
3. MAX(CASE WHEN day_offset >= 7 THEN 1 ELSE 0 END) behaves like a logical OR for the retention flag; cast to ::numeric before dividing and wrap the denominator in NULLIF(..., 0) for safe division.