TikTok·Software Engineer·Technical Phone Screen·Senior

SeniorPrefer not to say

May 2026Remote

Summary

TikTok SQL round for a software engineering role. One question, but it was basically five questions crammed into one monster query. Left feeling like I answered maybe 60% of it properly.

Questions Asked (1)

Given tables for users, orders, and events, write a single SQL query that returns per month and country: new user counts, 7-day post-signup conversion rate, 7-day rolling retention, GMV excluding refunded or canceled orders, and the top campaign by last-touch attribution. Also handle late-arriving events, deduplication on (uid, ts, event_type), timezone consistency, and explain your window function choices.

Product Analytics & MetricsData ModelingTechnical Trade-offs

Author's notes

I started with the GMV piece because that felt safest, then tried to bolt on the rest.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying requirements and assumptions, then outline a modular query structure using CTEs for deduplication, timezone normalization, and metric calculations. Explain how you handle late-arriving events with a watermark and how window functions address each metric, emphasizing correctness and performance trade-offs.

Pro tip: Mention that you would validate the query against a small manually computed dataset and discuss how you'd monitor data quality (e.g., late event rates) in production. This shows you think beyond just writing SQL.

1. Clarify requirements and assumptions

Ask about definitions: what counts as a new user, how conversion is attributed, what '7-day rolling retention' means (e.g., active on day 7 after signup), and how to handle multiple campaigns. Confirm timezone (e.g., UTC) and late event tolerance.

2. Deduplicate and normalize events

Use a CTE with ROW_NUMBER() OVER (PARTITION BY uid, ts, event_type ORDER BY ingestion_time DESC) to deduplicate, keeping the latest record. Convert all timestamps to a consistent timezone (e.g., UTC) using AT TIME ZONE.

3. Handle late-arriving events

Define a watermark (e.g., allow events up to 7 days late) and filter or aggregate accordingly. Use event_time for windowing and ingestion_time for deduplication. Mention that late events may require reprocessing or incremental updates.

4. Compute metrics with window functions

For each metric, choose appropriate window functions: e.g., COUNT(DISTINCT uid) for new users, SUM(CASE WHEN ...) for conversions, and window frames for rolling retention. Use PARTITION BY month, country and ORDER BY event_time for rolling calculations.

5. Assemble final query and explain trade-offs

Combine CTEs into a single query, ensuring correct grouping and ordering. Explain why you chose specific window functions (e.g., ROWS BETWEEN for rolling retention) and discuss performance considerations like indexing and partition pruning.

Key Points to Mention

Deduplication strategy using ROW_NUMBER() with ingestion_time to handle duplicates on (uid, ts, event_type).
Timezone consistency: convert all timestamps to UTC using AT TIME ZONE and use event_time for time-based windows.
Late-arriving events: use a watermark (e.g., 7 days) and consider reprocessing or incremental updates.
Window function choices: ROW_NUMBER for dedup, SUM/COUNT with OVER for rolling metrics, and RANGE vs ROWS for time-based windows.
Metric definitions: new users (first event), 7-day conversion (signup to purchase within 7 days), 7-day rolling retention (active on day 7 after signup), GMV excluding refunded/canceled orders.
Top campaign by last-touch attribution: use ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC) to get last touch, then aggregate by month and country.

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