Analyze Posting Behavior by Cohort and Date
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
creator_post
+------------+---------+------+
| post_date | user_id | post |
+------------+---------+------+
| 2023-01-01 | 111 | 1 |
| 2023-01-01 | 222 | 2 |
| 2023-01-02 | 111 | 4 |
| 2022-01-03 | 333 | 10 |
+------------+---------+------+
##### Scenario
Product analytics team wants to understand posting behavior of content creators by acquisition cohort.
##### Question
Write SQL to assign each user to a cohort defined by their first post_date (first day they ever posted). For each cohort and each posting_date, return total posts and distinct active users. Order results by cohort_start_date and posting_date.
##### Hints
Use window functions or sub-query to get users’ MIN(post_date); join back to original table for aggregation.
Overview: This question evaluates a candidate's ability to perform cohort-based user attribution and time-series aggregation, specifically assigning users to acquisition cohorts by their first activity and computing total posts and distinct active users by date.
You are given a table of creator posts. Assign each user to a cohort defined by their first post_date (the first day they ever posted). For each cohort and each posting_date, return the total number of posts and the number of distinct active users from that cohort on that date. Order the results by cohort_start_date and posting_date.
Tables
creator_post(post_date DATE, user_id INTEGER, post INTEGER)
Hints
- Compute each user’s cohort as MIN(post_date) using a window function or a grouped subquery/CTE.
- Join the cohort mapping back to creator_post, then aggregate by cohort_start_date and post_date with SUM(post) and COUNT(DISTINCT user_id).