← TikTok Interview Insights

TikTok·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
May 2026

Summary

TikTok data scientist interview with a SQL-heavy technical screen. The problem revolved around a user posting log and asked for registration dates, early engagement metrics, and retention math. Pretty classic analytics setup but the retention rate piece tripped me up a bit.

Questions Asked (1)

Q1

Given a user posting log table, write SQL to find each user's first posting date, compute how many posts they made within their first 7 days, and calculate the overall 7-day retention rate (the share of users who posted on day 7 or later).

Product Analytics & MetricsData Modeling
Author's notes

I started with a CTE to pull the min date per user, which felt right.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify the table schema and definitions (e.g., what constitutes a post, how to handle multiple posts per day, and whether 'day 7' means exactly the 7th day or day 7 and beyond). Then, use a two-step approach: first compute each user's first posting date and their post count within the first 7 days, then calculate the retention rate as the proportion of users who have at least one post on or after day 7 relative to their first posting date.

Pro tip: Explicitly state your assumptions about edge cases (e.g., users with only one post, timezone handling) and mention that you would validate the retention definition with stakeholders, as '7-day retention' can vary across companies.

1. Clarify requirements and schema

Ask about the table structure (columns like user_id, post_date, post_id) and confirm the definition of 'first 7 days' and 'day 7 or later' (e.g., relative to first post date, inclusive of day 7).

2. Compute first posting date per user

Use a subquery or window function (e.g., MIN(post_date) OVER (PARTITION BY user_id)) to find each user's earliest post date.

3. Count posts within first 7 days

Join the first posting date back to the original table and count posts where the post date is between the first date and first date + 6 days (inclusive).

4. Calculate 7-day retention rate

Identify users who have at least one post on or after (first_date + 7 days), then divide that count by the total number of users.

5. Combine results and present

Output a table with user_id, first_post_date, posts_in_first_7_days, and the overall retention rate as a separate metric or in a summary row.

Key Points to Mention

  • Use of window functions (e.g., MIN() OVER) or self-joins to compute first posting date.
  • Handling of multiple posts per user per day (e.g., COUNT(DISTINCT post_date) vs COUNT(*)).
  • Definition of 'day 7 or later' as relative to each user's first post date, not calendar day.
  • Edge cases: users with no posts after day 7, users with only one post, and timezone considerations.
  • Efficiency: filtering early to reduce data size before joins or aggregations.
  • Clear separation of per-user metrics and overall retention rate in the final output.

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