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.
