SQL for data analysis, the 10 query patterns that matter

You do not need to memorize hundreds of SQL functions to be a strong analyst. Real analytical work is a small set of patterns that come up again and again. Learn these ten and you can answer most questions a business will throw at you.

1. Filtering and aggregation

The foundation: WHERE to pick rows, GROUP BY with COUNT, SUM, AVG to summarize. Most reports are some version of "this metric, by this dimension."

SELECT region, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders GROUP BY region;

2. Joins

Combining tables is the heart of SQL. Know the difference between an inner join (only matches) and a left join (keep all rows on the left, fill nulls where there is no match). Most "where did my rows go" bugs are an inner join that should have been a left join.

3. Conditional aggregation

Counting subsets in one pass with CASE inside an aggregate. This replaces running several queries.

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'paid') AS paid
FROM orders;

4. Window functions

The analyst's superpower: compute across a set of rows without collapsing them. Running totals, rankings, and "compared to the row before."

SELECT day, revenue,
  SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily;

5. Ranking and top-N-per-group

ROW_NUMBER() or RANK() partitioned by a group, to get "the top 3 products in each category."

6. CTEs for readable, layered queries

WITH lets you name a step and build on it, so a complex query reads top to bottom instead of as nested subqueries. Use them to keep analysis legible.

7. Cohorts

Group users by when they joined, then track each cohort over time. The pattern: derive a cohort key (signup month), then aggregate activity by cohort and period.

8. Funnels

Count how many users reached each step of a flow (viewed, added to cart, purchased), usually with conditional aggregation or joins between step events.

9. Retention

What fraction of a cohort comes back in week 1, 2, 3. A join of a cohort to its later activity, then a ratio. This is one of the most-asked analytics questions.

10. Null handling

Nulls break sums, comparisons, and joins in surprising ways. COALESCE to supply defaults, and remember that NULL = NULL is not true. Most subtle SQL bugs trace back to nulls.

Build the patterns, not flashcards

These click when you run them on real tables and see the rows change. The SQL track builds each of these patterns on real data, graded in your browser as you write the query. The first project is free.