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.
Quick Answer: 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.