SQL Interview — Quick Recall Guide
Must knowCommon trapMemory mapOne-liner
🧠 MASTER DECISION FLOWCHART
🧠 Memory Map
QUESTION SAYS...→USE THIS PATTERN
──────────────────────────────────────────────────────────────────
"Top N per group / rank within group"→P1: DENSE_RANK PARTITION BY
"Running total / cumulative / rolling avg"→P2: SUM OVER (ORDER BY)
"Previous row / MoM / next event"→P3: LAG / LEAD
"Consecutive days / streak / period"→P4: GAPS & ISLANDS (date - rn trick)
"Group events into sessions"→P5: SESSIONIZATION (LAG + SUM flag)
"Remove duplicates / keep latest row"→P6: ROW_NUMBER PARTITION BY = 1
"Compare rows in same table / hierarchy"→P7: SELF-JOIN (two aliases)
"All combinations / grid / every pair"→P8: CROSS JOIN
"Items bought in same order together"→P9: MARKET BASKET (self-join order_id)
"Count/sum by category in one row"→P10: CASE WHEN inside aggregate
"First users / retention / cohorts"→P11: COHORT (MIN date as cohort)
"Days between / overdue / past 90 days"→P12: DATE ARITHMETIC
"Hierarchy / tree / all levels"→P13: RECURSIVE CTE
"Median / percentile / quartile"→P14: PERCENTILE_CONT or ROW_NUMBER trick
"Funnel / conversion / drop-off rate"→P15: FUNNEL (COUNT DISTINCT per stage)
SECTION 1: WINDOW FUNCTIONS (P1, P2, P3)
P1: Ranking Template
sql
WITH ranked AS (
SELECT *, DENSE_RANK() OVER (PARTITION BY grp ORDER BY val DESC) AS rnk
FROM tbl
)
SELECT * FROM ranked WHERE rnk <= N;
-- ROW_NUMBER: unique, no ties | RANK: skips | DENSE_RANK: no skip ← use for Top-N⚡ RANK vs DENSE_RANK: Top-2 with RANK → only 2 rows max. Top-2 with DENSE_RANK → tied 2nd places ALL appear (correct!).
P2: Running Total Template
sql
SUM(val) OVER (PARTITION BY grp ORDER BY date_col
R