← TikTok Interview Insights

TikTok·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
May 2026

Summary

TikTok data scientist interview with a SQL-heavy question around ad conversion metrics. Pretty straightforward setup but the metric definition piece tripped me up more than I expected.

Questions Asked (1)

Q1

Given a user events table with ad clicks and page visits, write SQL to compute the conversion rate of ad clicks that are followed by at least one page visit from the same user on the same calendar day. Define your numerator and denominator explicitly.

Product Analytics & MetricsData Modeling
Author's notes

The SQL itself wasn't the hard part.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, explicitly define the numerator as the number of distinct users who had at least one ad click and at least one page visit on the same calendar day, and the denominator as the number of distinct users who had at least one ad click on that day. Then write a SQL query that aggregates events by user and date, using conditional aggregation or self-joins to identify qualifying users, and finally compute the ratio.

Pro tip: Clarify whether you're computing a daily conversion rate (per user-day) or an overall rate across the entire period, as this changes the aggregation level. Also, mention that using COUNT(DISTINCT user_id) avoids double-counting users with multiple clicks or visits.

1. Define numerator and denominator

Numerator: distinct users with at least one ad click and at least one page visit on the same calendar day. Denominator: distinct users with at least one ad click on that day.

2. Aggregate events by user and date

Group the events table by user_id and event_date, and create flags for whether the user had an ad click and whether they had a page visit on that day.

3. Identify qualifying user-days

Filter to user-days where both an ad click and a page visit occurred, then count distinct users for the numerator. Separately, count distinct users with an ad click for the denominator.

4. Compute the conversion rate

Divide the numerator by the denominator, ensuring you handle division by zero (e.g., using NULLIF or CASE).

5. Consider edge cases and output format

Decide whether to output a single overall rate or a daily rate. Mention handling of multiple events per user per day and timezone considerations.

Key Points to Mention

  • Use COUNT(DISTINCT user_id) to avoid double-counting users with multiple events.
  • Specify the time window (e.g., daily conversion rate) and whether it's per day or overall.
  • Use conditional aggregation (e.g., MAX(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END)) to create flags.
  • Ensure the same calendar day is used for both click and visit, considering timezone if necessary.
  • Handle division by zero using NULLIF or CASE to avoid errors.
  • Consider using a self-join or subqueries to compare click and visit events for the same user and date.

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