← TikTok Interview Insights

TikTok·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Jun 2026

Summary

TikTok Data Scientist interview with a SQL-heavy analytics question focused on user retention. The problem was more layered than it looked on the surface, and I definitely underestimated the date arithmetic piece going in.

Questions Asked (1)

Q1

Given a table of daily post counts per user, find each user's registration date (their earliest post date), calculate total posts within their first 7 days, and compute the Day-7 retention rate as the share of users who posted on day 7 out of those who posted on day 1.

Product Analytics & MetricsData Modeling
Author's notes

The registration date part was fine, just MIN(post_date) partitioned by user.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the table schema and defining key terms like 'day 7' and 'posted on day 1'. Then outline a SQL-based solution using window functions to compute registration date, first 7-day posts, and Day-7 retention, and finally discuss how to interpret and validate the metric.

Pro tip: Always clarify whether 'day 7' means exactly the 7th day after registration or within the first 7 days, and whether retention is based on any activity or a specific action. This shows attention to detail and prevents misinterpretation.

1. Clarify requirements and assumptions

Confirm the table structure (e.g., user_id, post_date, post_count) and define ambiguous terms like 'day 7' (exact day vs. within 7 days) and 'posted on day 1' (any post vs. specific count).

2. Compute registration date and first 7-day posts

Use a window function (e.g., MIN(post_date) OVER (PARTITION BY user_id)) to get each user's registration date, then sum post counts for dates within 7 days of that date.

3. Identify day 1 and day 7 users

Flag users who posted on day 1 (registration date) and on day 7 (registration date + 6 days if day 1 is day 0, or +7 days if day 1 is day 1).

4. Calculate Day-7 retention rate

Divide the number of users who posted on day 7 by the number of users who posted on day 1, ensuring the denominator is correct.

5. Validate and interpret results

Check for edge cases (e.g., users with no day 1 activity), validate with sample data, and discuss how this metric informs product decisions.

Key Points to Mention

  • Use of window functions (e.g., MIN, SUM) to compute per-user metrics efficiently.
  • Definition of 'day 7' as the 7th day after registration (date_add(reg_date, 6) if day 1 is day 0) and consistency in date arithmetic.
  • Handling of users who did not post on day 1 (excluded from denominator) and potential bias.
  • Importance of partitioning by user_id and ordering by date for cumulative sums.
  • Consideration of time zones and date truncation if timestamps are involved.
  • Interpretation of Day-7 retention as an early engagement metric and its limitations.

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