TikTok·Data Scientist·Technical Phone Screen·Senior

SeniorPrefer not to say

Jun 2026Remote

Summary

TikTok data scientist interview that was basically one giant SQL question with a lot of moving parts. The schema looked simple but the actual requirements kept stacking up and I spent more time than I'd like to admit untangling the timezone logic.

Questions Asked (1)

Write a single PostgreSQL query to produce a 7-day regional dashboard (US vs Asia) with columns for local date, DAU, buyers, revenue, ARPPU, view-to-purchase conversion rate, new vs returning DAU, and a flag for days where revenue drops more than 20% below the 7-day median for that region. Use per-user timezone offsets to define 'local date', treat 2025-09-01 as today, and ensure the query is idempotent against duplicate events sharing the same user, timestamp, event type, and session.

Product Analytics & MetricsData ModelingTechnical Trade-offs

Author's notes

This took me way longer to structure than I expected.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by outlining a CTE-based pipeline: deduplicate raw events, compute local dates using per-user timezone offsets, aggregate daily metrics per region, then apply window functions for 7-day median and revenue drop flag. Emphasize correctness (idempotency, timezone handling) and clarity, and mention performance considerations like indexing and partitioning.

Pro tip: Mention that you'd validate the query against a small sample and check edge cases like users with missing timezone offsets or events crossing midnight boundaries, showing production readiness.

1. Deduplicate and prepare base events

Use a CTE with DISTINCT or ROW_NUMBER() to remove duplicate events sharing user, timestamp, event type, and session. Join with a user timezone mapping to compute local date as (timestamp + offset)::date.

2. Aggregate daily metrics per region

Group by local_date and region (US vs Asia) to compute DAU (distinct users), buyers (distinct users with purchase), revenue (sum of purchase amounts), and view-to-purchase conversion rate (purchases / views).

3. Compute new vs returning DAU

For each user and local_date, determine if they were active on any prior date. Use a window function like MIN(local_date) OVER (PARTITION BY user) or a self-join to flag new vs returning, then aggregate counts per region and date.

4. Calculate ARPPU and 7-day median revenue

ARPPU = revenue / buyers. Use a window function with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW to compute the 7-day median revenue per region (using PERCENTILE_CONT or a subquery).

5. Flag revenue drops and finalize output

Compare daily revenue to the 7-day median; flag days where revenue < 0.8 * median. Select all required columns, order by region and local_date, and ensure the query is idempotent and uses '2025-09-01' as today for any date filtering.

Key Points to Mention

Idempotency: use DISTINCT or ROW_NUMBER() to deduplicate events with identical user, timestamp, event type, and session.
Timezone handling: join a user timezone offset table and compute local_date as (timestamp + offset)::date; handle NULL offsets gracefully.
Region mapping: define US and Asia based on user country or timezone; clarify how to classify ambiguous cases.
Metric definitions: DAU = distinct active users; buyers = distinct users with purchase; ARPPU = revenue / buyers; conversion = purchases / views.
New vs returning: use first-seen date per user (MIN(local_date) OVER (PARTITION BY user)) to classify.
7-day median and flag: use window function with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW and PERCENTILE_CONT(0.5); flag if revenue < 0.8 * median.

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