Interviewers rarely test obscure SQL. They test whether you can answer a business question reliably. These six patterns cover most of what an analyst writes every day.

1. Filter and sort with intent

SELECT only the columns you need, WHERE before you aggregate, and ORDER BY with a LIMIT while you explore. Habit one: never run a query on a huge table without a limit or a date filter.

2. Aggregate with GROUP BY

Revenue per month, orders per customer, average basket by city. Learn COUNT, SUM and AVG together with GROUP BY, and remember that every non-aggregated column in the SELECT must be in the GROUP BY.

3. Join without duplicating rows

Most wrong numbers come from joins that multiply rows. Before you join, check the grain of both tables: one row per what? If you join an orders table to an order-items table and then sum the order total, you will count it once per item.

4. Conditional logic with CASE

Bucket ages, flag late deliveries, build your own segments. CASE inside an aggregate, such as SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END), is one of the most useful tricks you can learn.

5. Window functions

ROW_NUMBER, RANK, LAG and running totals with SUM() OVER. They let you answer questions like "what was each customer's second order?" or "how did this month compare with last month?" without painful self-joins.

6. CTEs for readability

Break a long query into named steps with WITH. A reviewer should be able to read your query top to bottom like a short story. It is also the easiest way to debug your own logic.

Practise each pattern on a dataset you care about, such as your own spending, a sports league or public data. Fluency comes from repetition on questions you genuinely want answered.

The SQL queries every analyst should master first