Katkat
Back

Calculate User Registration Date and 7-Day Retention Rate

TikTokData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.

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.