Cheat sheet

SQL for analyst interviews

Logical execution order

FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

Frank Was Going Home So Order Lunch. Consequences: no SELECT alias or aggregate in WHERE. Aggregates go in HAVING. ORDER BY can use aliases. [MS Learn]

Template

SELECT   region, SUM(revenue) AS total, COUNT(*) AS n
FROM     sales s
LEFT JOIN targets t ON t.region = s.region
WHERE    s.product = 'Laptop'          -- filters rows
GROUP BY region                       -- every non-aggregated SELECT column
HAVING   SUM(revenue) > 100000         -- filters groups
ORDER BY total DESC                   -- ASC is default
LIMIT    5;

Joins

Join	Keeps	Unmatched side
INNER	Only matching pairs	dropped
LEFT	All left rows (≥ 1 time each)	right columns = NULL
RIGHT	All right rows	left columns = NULL
FULL OUTER	Everything	NULLs on both sides

LEFT JOIN vs VLOOKUP

VLOOKUP: first match only, #N/A, one column per formula. LEFT JOIN: all matches (rows multiply), NULL, many columns, multi-key ON.

Patterns

Anti-join: LEFT JOIN … WHERE right.key IS NULL. A filter on the right table in WHERE turns a LEFT JOIN into an INNER JOIN, so put it in ON.

Handy extras

`COUNT(*)` vs `COUNT(col)`	all rows vs non-NULL values
`COUNT(DISTINCT col)`	unique values
`CASE WHEN x > 0 THEN 'pos' ELSE 'neg' END`	SQL's IF
`COALESCE(col, 0)`	replace NULL (like IFERROR for NULLs)
`WHERE col IS NULL`	never `= NULL`
`PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x)`	median (PostgreSQL / SQL Server)

Lessons: 4 · 5 · Source: PostgreSQL: Joins