Applied Scientist
SQL & Data Analysis
SQL is tested at nearly every Applied Scientist role โ especially Amazon. You need to go beyond basic SELECT/JOIN to window functions, CTEs, self-joins, and funnel analysis. The goal: translate business questions ('what is our 7-day retention?') into correct, efficient SQL queries.
Core Theory
- 1Window functions: perform calculations across a set of rows related to the current row, without collapsing the result (unlike GROUP BY). Syntax: func() OVER (PARTITION BY col ORDER BY col ROWS/RANGE BETWEEN ...). Key functions: ROW_NUMBER() (unique rank per partition), RANK() (ties get same rank, gaps after), DENSE_RANK() (ties get same rank, no gaps), LAG/LEAD (access previous/next row), SUM/AVG/COUNT OVER (running or partitioned aggregation).
- 2CTEs (Common Table Expressions): WITH alias AS (SELECT ...) allow you to name and reference intermediate results. Make complex queries readable and testable. Recursive CTEs enable hierarchical queries: find all subordinates of a manager, traverse a graph. Multiple CTEs: WITH cte1 AS (...), cte2 AS (...) SELECT ... FROM cte1 JOIN cte2.
- 3Retention analysis: cohort retention table. Cohort = group of users who performed a key action (signed up, first purchase) in a given period. Retention_rate(cohort_week, delta) = users active in week (cohort_week + delta) / users in cohort. Requires a self-join or cross-join of user events to cohort definitions.
- 4Funnel analysis: measure drop-off at each step of a user journey (e.g., page view โ add to cart โ checkout โ purchase). For each user, find if they completed each step and when (first occurrence). Use CASE WHEN or SIGN() to create binary flags per step. Funnel conversion = fraction reaching each step.
- 5Session identification: group user events into sessions. A new session starts if the gap between events exceeds a threshold (e.g., 30 minutes). Use LAG() to get previous event time, compare gap, use conditional SUM as session counter. SUM(CASE WHEN gap > 30 THEN 1 ELSE 0 END) OVER (PARTITION BY user ORDER BY event_ts) gives a session counter.
- 6Percentile and distribution: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) for median, or use NTILE(n) for approximate quantile bucketing. For exact quantile: PERCENT_RANK() = (rank - 1) / (total - 1). These are used in A/B test analysis and metric reporting.
- 7Self-joins: joining a table to itself. Used for: finding pairs (user A followed user B AND user B followed user A โ mutual follows), consecutive events (find users active on 3 consecutive days), hierarchical data (employee โ manager โ VP). Key: use different aliases for each copy of the table and specify the relationship in the JOIN condition.
- 8Performance considerations: indexes speed up JOIN and WHERE. Avoid SELECT * in production. Push WHERE filters as early as possible (before JOIN when possible). Use EXPLAIN to check query plan. Partitioned tables (by date) allow partition pruning โ huge speedup for date-range queries. Avoid correlated subqueries (executed once per row) โ rewrite as window functions or CTEs.
Key Formulas
| 1 | -- All queries assume BigQuery / PostgreSQL syntax |
| 2 | |
| 3 | -- โโโ Table schemas โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ |
| 4 | -- user_events: user_id, event_date (DATE), event_type |
| 5 | -- users: user_id, signup_date |
| 6 | |
| 7 | -- โโโ 1. 7-Day User Retention โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ |
| 8 | WITH cohort AS ( |
| 9 | -- Cohort: users by signup week |
| 10 | SELECT |
| 11 | user_id, |
| 12 | DATE_TRUNC('week', signup_date) AS cohort_week |
| 13 | FROM users |
| 14 | ), |
| 15 | activity AS ( |
| 16 | -- Get distinct active dates per user |
| 17 | SELECT DISTINCT |
| 18 | user_id, |
| 19 | DATE_TRUNC('week', event_date) AS activity_week |
| 20 | FROM user_events |
| 21 | WHERE event_type = 'active' |
| 22 | ), |
| 23 | cohort_activity AS ( |
| 24 | SELECT |
| 25 | c.cohort_week, |
| 26 | a.activity_week, |
| 27 | COUNT(DISTINCT c.user_id) AS active_users, |
| 28 | DATE_DIFF(a.activity_week, c.cohort_week, WEEK) AS weeks_since_signup |
| 29 | FROM cohort c |
| 30 | JOIN activity a USING (user_id) |
| 31 | GROUP BY 1, 2, 4 |
| 32 | ), |
| 33 | cohort_sizes AS ( |
| 34 | SELECT cohort_week, COUNT(*) AS cohort_size |
| 35 | FROM cohort GROUP BY 1 |
| 36 | ) |
| 37 | SELECT |
| 38 | ca.cohort_week, |
| 39 | ca.weeks_since_signup, |
| 40 | ca.active_users, |
| 41 | cs.cohort_size, |
| 42 | ROUND(100.0 * ca.active_users / cs.cohort_size, 1) AS retention_pct |
| 43 | FROM cohort_activity ca |
| 44 | JOIN cohort_sizes cs USING (cohort_week) |
| 45 | WHERE ca.weeks_since_signup BETWEEN 0 AND 12 |
| 46 | ORDER BY cohort_week, weeks_since_signup; |
| 47 | |
| 48 | |
| 49 | -- โโโ 2. Rolling 7-Day DAU โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ |
| 50 | WITH daily_active AS ( |
| 51 | SELECT |
| 52 | event_date, |
| 53 | COUNT(DISTINCT user_id) AS dau |
| 54 | FROM user_events |
| 55 | WHERE event_type = 'active' |
| 56 | GROUP BY 1 |
| 57 | ) |
| 58 | SELECT |
| 59 | event_date, |
| 60 | dau, |
| 61 | AVG(dau) OVER ( |
| 62 | ORDER BY event_date |
| 63 | ROWS BETWEEN 6 PRECEDING AND CURRENT ROW |
| 64 | ) AS rolling_7d_avg_dau, |
| 65 | SUM(dau) OVER ( |
| 66 | ORDER BY event_date |
| 67 | ROWS BETWEEN 6 PRECEDING AND CURRENT ROW |
| 68 | ) AS rolling_7d_total |
| 69 | FROM daily_active |
| 70 | ORDER BY event_date; |
| 71 | |
| 72 | |
| 73 | -- โโโ 3. Users with 3+ Consecutive Active Days โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ |
| 74 | WITH active_days AS ( |
| 75 | SELECT DISTINCT user_id, event_date |
| 76 | FROM user_events WHERE event_type = 'active' |
| 77 | ), |
| 78 | with_prev AS ( |
| 79 | SELECT |
| 80 | user_id, |
| 81 | event_date, |
| 82 | LAG(event_date, 1) OVER (PARTITION BY user_id ORDER BY event_date) AS prev_day, |
| 83 | LAG(event_date, 2) OVER (PARTITION BY user_id ORDER BY event_date) AS prev2_day |
| 84 | FROM active_days |
| 85 | ) |
| 86 | SELECT DISTINCT user_id |
| 87 | FROM with_prev |
| 88 | WHERE |
| 89 | -- event_date is exactly 1 day after prev_day AND prev_day is 1 day after prev2_day |
| 90 | event_date = prev_day + INTERVAL '1 day' |
| 91 | AND prev_day = prev2_day + INTERVAL '1 day'; |
| 92 | |
| 93 | |
| 94 | -- โโโ 4. Second-Highest Salary per Department โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ |
| 95 | WITH ranked AS ( |
| 96 | SELECT |
| 97 | department_id, |
| 98 | employee_id, |
| 99 | salary, |
| 100 | DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rk |
| 101 | FROM employees |
| 102 | ) |
| 103 | SELECT department_id, employee_id, salary |
| 104 | FROM ranked |
| 105 | WHERE rk = 2; |
| 106 | |
| 107 | |
| 108 | -- โโโ 5. Funnel Analysis โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ |
| 109 | WITH funnel AS ( |
| 110 | SELECT |
| 111 | user_id, |
| 112 | MAX(CASE WHEN event_type = 'page_view' THEN 1 ELSE 0 END) AS viewed, |
| 113 | MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS carted, |
| 114 | MAX(CASE WHEN event_type = 'checkout' THEN 1 ELSE 0 END) AS checked_out, |
| 115 | MAX(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS purchased |
| 116 | FROM user_events |
| 117 | WHERE event_date >= CURRENT_DATE - INTERVAL '7 days' |
| 118 | GROUP BY user_id |
| 119 | ) |
| 120 | SELECT |
| 121 | SUM(viewed) AS step1_view, |
| 122 | SUM(carted) AS step2_cart, |
| 123 | SUM(checked_out) AS step3_checkout, |
| 124 | SUM(purchased) AS step4_purchase, |
| 125 | ROUND(100.0*SUM(carted)/NULLIF(SUM(viewed),0), 1) AS view_to_cart_pct, |
| 126 | ROUND(100.0*SUM(checked_out)/NULLIF(SUM(carted),0), 1) AS cart_to_checkout_pct, |
| 127 | ROUND(100.0*SUM(purchased)/NULLIF(SUM(checked_out),0), 1) AS checkout_to_purchase_pct |
| 128 | FROM funnel; |
Five production SQL patterns for data analysis interviews: (1) cohort retention table with DATE_TRUNC for weekly bucketing and DATE_DIFF for periods since signup; (2) rolling 7-day DAU using ROWS BETWEEN window spec; (3) consecutive active days using LAG() to compare adjacent dates; (4) DENSE_RANK() for second-highest per group; (5) funnel analysis with MAX(CASE WHEN) to create per-user binary flags aggregated into step counts.
Worked Interview Problems
3 problemsProblem
You have a users table (user_id, signup_date) and an events table (user_id, event_date). Write a query to calculate D7, D14, D30 retention for users who signed up in January 2024.
Solution
Define the cohort: users with signup_date in January 2024. SELECT user_id FROM users WHERE signup_date BETWEEN '2024-01-01' AND '2024-01-31'.
For each retention day (7, 14, 30): count users who were active on signup_date + N days. JOIN cohort to events on user_id WHERE event_date = signup_date + INTERVAL 'N days'.
Combine into a single query: SELECT cohort_size, d7_active, d14_active, d30_active, d7_active/cohort_size, etc.
Handle edge cases: a user may be active multiple times on the same day โ use COUNT(DISTINCT user_id) not COUNT(*). A user with no activity on exactly day 7 has NULL โ that's a 0 for retention (use LEFT JOIN from cohort to events).
Full query: CTE for cohort โ LEFT JOIN to events three times (for d7, d14, d30) โ GROUP BY cohort โ report percentages. Or use CASE WHEN DATE_DIFF(event_date, signup_date, DAY) IN (7, 14, 30) THEN 1 END for a more compact approach.
Answer
WITH cohort AS (SELECT user_id, signup_date FROM users WHERE signup_date BETWEEN '2024-01-01' AND '2024-01-31') SELECT COUNT(DISTINCT c.user_id) AS cohort_size, COUNT(DISTINCT CASE WHEN DATE_DIFF(e.event_date, c.signup_date, DAY) = 7 THEN c.user_id END) AS d7, ... FROM cohort c LEFT JOIN events e USING (user_id) GROUP BY ... Divide each by cohort_size for retention rates.
Common Mistakes
Using WHERE instead of HAVING to filter on aggregated values. WHERE filters individual rows before aggregation. HAVING filters groups after aggregation. WHERE COUNT(*) > 5 is a syntax error; HAVING COUNT(*) > 5 is correct.
Forgetting DISTINCT in COUNT when there can be duplicate rows. COUNT(user_id) counts all rows including duplicates. COUNT(DISTINCT user_id) counts unique users. Always know which you need.
Using NOT IN with a subquery that can return NULLs. NOT IN (SELECT x FROM ...) returns no rows if any value in the subquery is NULL (because NULL != anything is UNKNOWN, not TRUE). Use NOT EXISTS or LEFT JOIN + WHERE IS NULL instead.
Applying window functions in WHERE clauses. Window functions are computed after WHERE โ you can't filter on them directly. Wrap in a CTE or subquery: WITH ranked AS (SELECT ..., ROW_NUMBER() OVER (...) AS rn ...) SELECT ... WHERE rn = 1.
Joining tables without specifying all necessary conditions. A cartesian product occurs when a JOIN has insufficient conditions โ often produces millions of unexpected rows. Always verify join output row count makes sense (should be <= max(left table rows, right table rows) for most joins).