SQL Window Functions And Analytical Querying
Asked of: Data Scientist
Last updated

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); keeprn = 1before counting users. -
First-event extraction using
MIN(event_ts)orROW_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, andROW_NUMBERto 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
D7means 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.
Featured in interview prep guides
Practice questions
- Find high-value crypto users and top-CTR productTikTok · Data Scientist · Technical Screen · easy
- Write monthly customer and sales SQL queriesTikTok · Data Scientist · Technical Screen · easy
- Find top-paid employee per departmentTikTok · Data Scientist · Technical Screen · easy
- Count buggy vs non-buggy by employerTikTok · Data Scientist · Take-home Project · Medium
- Select max-discount product per categoryTikTok · Data Scientist · Take-home Project · Medium
- Write SQL for 7-day geo-localized revenue dashboardTikTok · Data Scientist · HR Screen · Medium
- Compute and rank top bad advertisersTikTok · Data Scientist · Technical Screen · Medium
- Analyze shopping funnel with joins and windowsTikTok · Data Scientist · Technical Screen · Medium
- Write SQL for geo posting-frequency dropsTikTok · Data Scientist · Onsite · Medium
- Compute CTR drop with exclusionsTikTok · Data Scientist · Onsite · Medium
- Compare SQL counts, windows, and NULL semanticsTikTok · Data Scientist · Technical Screen · Medium
- Compute 7-day rolling complaint/order ratio in SQLTikTok · Data Scientist · Technical Screen · Medium
Related concepts
- SQL Window Functions And AnalyticsData Manipulation (SQL/Python)
- SQL And Python Data ManipulationData Manipulation (SQL/Python)
- SQL Analytical QueryingData Manipulation (SQL/Python)
- SQL Product AnalyticsData Manipulation (SQL/Python)
- SQL Analytics Joins, Aggregations, And Windows
- SQL Window Functions And Temporal JoinsData Manipulation (SQL/Python)