Most people learning SQL start with SELECT * and never go deeper. Here are the 5 query patterns that actually come up in interviews and on the job:

1. Window functions (ROW_NUMBER, RANK, LAG)

Every take-home assessment I've seen includes at least one. If you can't write a running total or rank rows within a partition, you're not ready.

2. Self-joins for comparison queries

"Compare each employee's salary to their department average." This pattern shows up constantly. Practice it until it's second nature.

3. CTEs for readable, layered logic

Common Table Expressions separate your logic into steps. Interviewers want to see clean, readable SQL — not a 40-line nested subquery.

4. CASE WHEN for conditional aggregation

"Show me revenue by category, split by new vs returning customers." This is how you pivot data without a pivot table.

5. DATE_TRUNC and time-series grouping

"Show me weekly active users for the past 90 days." Time-based aggregation is the bread and butter of analytics.

If you want hands-on practice with all 5 patterns — real datasets, real interview questions, and feedback from other analysts — that's exactly what we built DataEdge for.