Katkat
Back

Calculate Day-7 Retention Rate from User Post Data

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

Last updated

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.

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.