Calculate User Registration Date and 7-Day Retention Rate
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates the ability to perform time-based data manipulation and cohort retention analysis using SQL/Python, including deriving registration dates, aggregating posts within time windows, and computing retention metrics.
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.
Tables
user_posts(user_id INTEGER, posting_date DATE, num_posts INTEGER)
Hints
- Derive each user's registration date with `MIN(posting_date) OVER (PARTITION BY user_id)`, then group by user.
- In PostgreSQL, `date_a - date_b` returns an integer number of days — use it directly instead of any DATEDIFF/DATE_DIFF function.