I've interviewed at 3 FAANG companies and reviewed hundreds of take-home assignments. The same SQL patterns come up over and over.

Here are the 5 you need to have memorized:

1. Window functions for running totals

SELECT SUM(revenue) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) — this shows up in literally every analytics interview.

2. Self-joins for comparing rows

Comparing a user's current month vs previous month? Self-join on user_id with a date offset. Simple but people fumble it under pressure.

3. CTEs for readability

Stop nesting 4 subqueries deep. WITH clauses make your code reviewable and debuggable. Interviewers notice this.

4. CASE WHEN for pivot-style aggregation

SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) — the poor man's pivot table. Works everywhere.

5. COALESCE + LEFT JOIN for handling missing data

Real datasets have nulls. If your query doesn't handle them, your dashboard is lying to someone.

I built BrainForge AI to help working professionals drill these patterns (and hundreds more) with an AI tutor that adapts to your level. No fluff, no 40-hour courses — just practice with instant feedback.