Calculate Day-7 Retention Rate from User Post Data
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates a candidate's competency in SQL and Python data wrangling, emphasizing date arithmetic, window functions, grouping, and conditional aggregation for cohort and retention calculations.
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.
Tables
post_activity(user_id INTEGER, post_date DATE, posts INTEGER)
Hints
- Use a per-user CTE before computing the aggregate retention row.
- Use NULLs for columns that do not apply to the aggregate row.