Recap — Pattern-Matching SQL Interviews

# Pattern-Matching SQL Interviews Most SQL interview questions, no matter how the question is phrased, reduce to one of ~10 patterns. If you can name the pattern in the first 30 seconds of reading the question, you'll write the right query in the next 5 minutes. ## The 10 patterns 1. **Top N per group** — "find the top-3 products by revenue in each category." → window function `ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)` then filter. 2. **Running total** — "cumulative revenue by month." → `SUM(...) OVER (ORDER BY month)`. 3. **Period-over-period** — "MoM growth in active users." → `LAG()` or self-join. 4. **Median / percentile** — `PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ...)`. Many candidates default to AVG and lose the question. 5. **Gaps & islands** — "find consecutive login streaks." → row_number trick: subtract `ROW_NUMBER()` from date to find groups. 6. **Funnel / conversion** — "what % of users who did A also did B within 7 days?" → self-join with date interval filter. 7. **Cohort retention** — "of users acquired in Jan, how many were active in Feb, Mar…?" → date_trunc on acquisition + activity, group by both. 8. **Outliers** — "find products priced more than 2 std above category mean." → window aggregation + WHERE filter. 9. **De-duplication** — "for each user, keep the most recent record." → window + `ROW_NUMBER()` + filter. 10. **Pivot** — "show monthly revenue as columns instead of rows." → `FILTER (WHERE ...)` or `CASE WHEN ... END` in SELECT. ## What to do in the interview When you read a question: 1. **Scan for the pattern keyword** — "consecutive", "top N", "running", "percentage that also did X", "compare to previous". 2. **Name the pattern out loud**: "this is a top-N-per-group problem." Interviewers love this — shows you have a vocabulary. 3. **Write the skeleton first**, then fill in column names. Don't start typing the WHERE clause on line 1. ## Worked example — top-N-per-group > "For each department, find the 3 employees with the highest salaries." Pattern: top N per group. Skeleton: ```sql WITH ranked AS ( SELECT department_id, employee_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn FROM employees ) SELECT department_id, employee_id, salary FROM ranked WHERE rn <= 3; ``` Naming the pattern got you to that skeleton in 30 seconds. Without it, you'd be tempted to write a self-join and run out of time. ## The interview red flag If you find yourself writing nested subqueries with EXISTS / NOT EXISTS for a simple ranking question, stop. You've missed the pattern. Window functions exist for exactly this. Using them signals you know modern SQL. ## Takeaway Memorize the 10 patterns. Know which window function or aggregate maps to each. In the interview, narrate the pattern before you start writing. This alone moves you from "candidate who can write SQL" to "candidate who thinks in SQL".