← TikTok Interview Insights

TikTok·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Jun 2026

Summary

SQL round for a Data Scientist role at TikTok, two questions back to back. Nothing conversational, just schema and tasks. Felt more like a take-home but it was live.

Questions Asked (2)

Q1

Given a users table and a transactions table, write a query to find all users whose total completed crypto transaction volume exceeds £100 within the first 7 days of their account creation.

Product Analytics & MetricsData Modeling
Author's notes

The window logic is what gets you.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and definitions (e.g., 'completed' status, transaction volume in GBP, account creation date). Then write a SQL query that joins users to transactions, filters for completed transactions within the first 7 days of account creation, aggregates the total volume per user, and selects users whose sum exceeds £100.

Pro tip: Mention the importance of handling time zones and currency conversion upfront, as these can significantly impact the results in a global product like TikTok. Also, consider discussing how you would validate the query with sample data or edge cases.

1. Clarify requirements and schema

Ask about the table structures, the definition of 'completed' transactions, how transaction volume is calculated (e.g., in GBP or requires conversion), and whether account creation date is in UTC.

2. Filter transactions within 7 days

Use a JOIN between users and transactions, and filter transactions where the transaction date is between the user's account creation date and 7 days after.

3. Aggregate volume per user

Group by user and sum the transaction volume (converted to GBP if necessary) for the filtered transactions.

4. Apply threshold and select users

Use a HAVING clause to filter users whose total volume exceeds £100, and return the user IDs or relevant details.

5. Validate and discuss edge cases

Mention potential edge cases like users with no transactions, transactions exactly on the 7th day, or multiple currencies, and how to handle them.

Key Points to Mention

  • Definition of 'completed' transaction status and how to filter for it.
  • Handling of currency conversion if transactions are not in GBP.
  • Time zone considerations for account creation and transaction timestamps.
  • Use of date functions (e.g., DATE_ADD, INTERVAL) to calculate the 7-day window.
  • Aggregation with SUM and filtering with HAVING.
  • Performance considerations for large datasets (e.g., indexing on user_id and transaction_date).

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

Q2

Using an activity table with view and click events, compute the click-through rate per product and return the product with the highest CTR. How do you handle products that have views but no clicks, or no views at all?

Product Analytics & MetricsA/B Testing & Experimentation
Author's notes

Pretty standard funnel metric question but the edge case about zero views is the part they actually care about.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and definitions: what constitutes a view and a click, and how to compute CTR (clicks/views). Then outline a SQL query that aggregates events per product, computes CTR, and handles edge cases like zero views or zero clicks. Finally, discuss how to select the product with the highest CTR, considering ties and minimum view thresholds.

Pro tip: Mention that products with zero views should be excluded from CTR calculation to avoid division by zero, and consider setting a minimum view threshold to ensure statistical significance, especially in A/B testing contexts.

1. Clarify definitions and assumptions

Confirm what constitutes a view and a click, and whether CTR is defined as clicks/views. Ask about time windows and if there are any filters (e.g., unique users).

2. Aggregate events per product

Write a SQL query to count views and clicks per product, likely using conditional aggregation (SUM(CASE WHEN event='view' THEN 1 ELSE 0 END)).

3. Compute CTR and handle edge cases

Calculate CTR as clicks/views. For products with views but no clicks, CTR is 0. For products with no views, exclude them or set CTR to NULL to avoid division by zero.

4. Select product with highest CTR

Order by CTR descending and pick the top product. Consider ties and whether to apply a minimum view threshold for statistical significance.

5. Discuss business implications and validation

Mention that high CTR on low views may be noise; suggest validating with confidence intervals or A/B testing if applicable.

Key Points to Mention

  • Use conditional aggregation to count views and clicks per product.
  • Handle division by zero: exclude products with zero views or use NULLIF.
  • Products with views but no clicks have CTR = 0.
  • Consider minimum view threshold to avoid misleading CTR from low-volume products.
  • Tie-breaking strategy: e.g., highest clicks or most recent activity.
  • Statistical significance: CTR alone may not be reliable; consider confidence intervals.

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