← TikTok Interview Insights

TikTok·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Jan 2023Remote

Summary

TikTok data scientist interview with a SQL cohort analysis question. Pretty standard product analytics setup but the specifics tripped me up a bit.

Questions Asked (1)

Q1

Given a table of creator posts with user IDs and post dates, write SQL to assign each user to a cohort based on their earliest post date. For every cohort and posting date combination, return the total number of posts and the count of distinct active users, ordered by cohort start date and posting date.

Product Analytics & MetricsData Modeling
Author's notes

My first instinct was to just group by post_date and call it a day, which would've completely missed the cohort part.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, compute each user's earliest post date using a window function or subquery to assign them to a cohort. Then, aggregate posts by cohort and posting date, counting total posts and distinct active users, and order the results by cohort start date and posting date.

Pro tip: Clarify whether 'active user' means a user who posted on that date or any user in the cohort who was active (e.g., logged in) that day; in most cases, it refers to distinct users who posted on that date. Also, consider using a CTE for readability and to avoid repeating the cohort calculation.

1. Identify the cohort for each user

Use a window function like MIN(post_date) OVER (PARTITION BY user_id) or a subquery to find each user's earliest post date, which defines their cohort.

2. Join cohort information back to the posts table

Join the cohort assignment back to the original posts table so each post row has the user's cohort start date.

3. Aggregate by cohort and posting date

Group by cohort start date and post date, then compute COUNT(*) for total posts and COUNT(DISTINCT user_id) for active users.

4. Order the results

Sort the final output by cohort start date and then by posting date to meet the requirement.

Key Points to Mention

  • Use of window functions (e.g., MIN() OVER) or subqueries to determine cohort assignment.
  • Handling of ties or multiple posts on the same date for a user's earliest post date.
  • Definition of 'active user' as distinct users who posted on that date.
  • Efficiency considerations: indexing, avoiding repeated scans, using CTEs for clarity.
  • Potential edge cases: users with no posts (if included), null dates, timezone considerations.
  • Ordering by cohort start date and posting date as specified.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.