TikTok Interview Questions 2027: 7-Day Retention SQL

For this TikTok interview question — 7-day retention from an event log — use cohort analysis: define the day-0 cohort, self-join on user_id where the return date is cohort date + 7, and divide retained by cohort size. Define 'active' and 'day 0' before writing a line.

TikTok Interview Questions: What the 7-Day Retention Problem Tests

7-day retention is commonly reported by candidates in TikTok's analytics interviews because it is one of the core health metrics of any consumer app — TikTok's own growth team lives on retention curves. Interviewers are testing whether you can operationalize a fuzzy product concept ("retention") into exact SQL: defining the cohort, defining the return window, and computing the ratio without double-counting. Candidates who jump to code without defining terms usually get the logic subtly wrong.

TikTok Interview Questions: How to Solve Step by Step

• Define the terms. "I'll define the cohort as users with at least one event on day 0, and retained as having at least one event on day 0 + 7. Should 'active' mean any event, or a specific event_type?" Asking shows product sense.
• Build the cohort. SELECT DISTINCT user_id, DATE(timestamp) AS cohort_date FROM events — one row per user per day.
• Self-join for returners. Join the daily-active set to itself on user_id where the return date equals cohort date + 7 days.
• Compute the ratio. COUNT(DISTINCT returners) / COUNT(DISTINCT cohort) per cohort date — GROUP BY cohort_date to get a retention curve over time.
• Guard the edges. Use DISTINCT everywhere (multiple events per day must not inflate counts); note that the most recent 7 days of cohorts cannot have complete retention yet.
• Narrate the logic. "For each cohort day, I'm asking: of the users active that day, what fraction came back exactly a week later?"

Example line: "Retention is a cohort question, not an event question — so the query shape is always: define cohort, self-join on user and date offset, divide."

Common Mistakes

• No DISTINCT on users. Counting events instead of users inflates both numerator and denominator.
• Fuzzy return window. "Around 7 days" vs. "exactly day + 7" — pick one and defend it.
• Ignoring incomplete cohorts. The last 7 days of data cannot show 7-day retention; flagging this shows maturity.

Keep Reading

• jp morgan tell me about yourself hirevue
• Is the TikTok OA Hard? Difficulty Breakdown 2027
• TikTok Backend Interview: LeetCode Questions & Follow-Ups

FAQ

Day 0 + 7 exactly, or a window like days 5–9? Exactly +7 is the standard literal reading; mention the window variant as a follow-up if the interviewer wants robustness.

What about users active on day 0 but not day 7, then active day 8? Not retained under the strict definition — say so explicitly; definitions are the test.

Which date function for the +7? DATE_ADD in MySQL/BigQuery, + INTERVAL '7 days' in Postgres — name your dialect.

How would I extend this to a retention curve? Group by cohort_date and compute day-1 through day-7 retention in one query — mention it as a natural follow-up.

Preparing for TikTok's interview? Our 2027 TikTok Online Hackerrank Coding Assessment Tutorials has practice questions and answers — $79 one-time, instant download.