SQL Window Functions And Analytical Querying

What's being tested

These prompts test analytical SQL for product metrics: deduplicating event streams, ordering user actions, joining behavioral tables, and computing cohort/funnel metrics. For a TikTok Data Scientist, the core skill is turning raw user events into trustworthy metrics like conversion_rate, D7_retention, AOV, and funnel step-through rates.

Patterns & templates

• Event deduplication with ROW_NUMBER() OVER (PARTITION BY user_id, event_type, date ORDER BY event_ts); keep rn = 1 before counting users.

• First-event extraction using MIN(event_ts) or ROW_NUMBER() to define registration date, first click, first visit, or cohort anchor.

• Temporal joins for funnels: join later events with conditions like visit_ts BETWEEN click_ts AND click_ts + INTERVAL '1 day'.

• Conditional aggregation with COUNT(DISTINCT CASE WHEN condition THEN user_id END) for conversion, retention, and step-level rates.

• Cohort grouping via DATE_TRUNC('month', event_ts) or registration date; compute rates as retained users divided by eligible cohort users.

• Window ordering with LAG, LEAD, and ROW_NUMBER to identify next action, prior action, session sequence, or valid event progression.

• Metric formulas should be explicit: conversion_rate = converted_users / exposed_or_clicked_users; AOV = total_revenue / order_count, not users.

Common pitfalls

Pitfall: Counting events instead of distinct users will inflate funnel conversion and retention when heavy users generate repeated actions.

Pitfall: Joining without time constraints can attribute a page visit, purchase, or post to the wrong prior event.

Pitfall: Using calendar day differences incorrectly; confirm whether D7 means exactly day 7, within 7 days, or days 1–7 after registration.

Practice these

The practice cards below cover the canonical variants — solve all of them and time yourself.