Data analysts don't just query data — they turn raw rows into decisions. This path takes you from confident querying to the specific SQL patterns analysts lean on every week: ranking, running totals, cohorts, funnels and A/B tests.
Most SQL courses teach syntax in isolation. This path is sequenced the way a working Data Analyst actually uses SQL: start by getting comfortable pulling and filtering data, then build up to the functions that answer the questions your stakeholders actually ask — "is retention improving?", "which variant won?", "what's driving the trend?". Each module below has a short explanation, a worked example on realistic analyst data, and links to go deeper on the homepage lesson player, the blog, or interview questions for this exact topic.
Every analyst question starts as a filter and a sort. Before touching anything advanced, get fluent
in SELECT, WHERE, and ORDER BY — but read them the way an analyst
does: WHERE is how you scope a question ("just this quarter, just this region"), and
ORDER BY is how you find the outliers that actually matter in a report.
SELECT name, department_id, salary
FROM employees
WHERE salary > 60000
ORDER BY salary DESC;WHERE clause is the difference between a report stakeholders trust and one they have to double-check.GROUP BY is the single most-used tool in an analyst's kit — it's how "10,000 rows of
orders" becomes "revenue by region." Pair it with HAVING to filter on the aggregate itself
(e.g. "only regions doing over $50k"), not the raw rows — a distinction that trips up almost everyone
the first time.
SELECT department_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 70000;WHERE filters rows before grouping; HAVING filters groups after. Mixing these up is one of the most common SQL interview mistakes.Analysts constantly need to turn continuous values into labeled groups — salary bands, customer
tiers, engagement buckets. CASE is how you do that inline, without a separate lookup
table, so it can slot directly into a SELECT or a GROUP BY.
SELECT name,
CASE
WHEN salary >= 85000 THEN 'Senior band'
WHEN salary >= 65000 THEN 'Mid band'
ELSE 'Entry band'
END AS pay_band
FROM employees;GROUP BY a CASE expression directly — great for one-query segment breakdowns without a separate lookup table.This is the module that separates "knows SQL" from "does analysis in SQL." Window functions let you
rank within groups, compute running totals, and compare a row to the one before it — all without
collapsing your result set the way GROUP BY does. RANK() for leaderboards,
LAG()/LEAD() for period comparisons, SUM() OVER for running
totals — these three cover most real analyst requests.
SELECT department_id, name, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;PARTITION BY is "restart the calculation per group" — it's what makes window functions replace a whole class of self-joins and subqueries.Real analysis queries are rarely one step — you aggregate, then rank the aggregate, then filter the
rank. WITH (a Common Table Expression) lets you name each step instead of nesting
subqueries five levels deep, which matters as much for your own sanity six months later as it does for
anyone reviewing your work.
WITH dept_totals AS (
SELECT department_id, SUM(salary) AS total_payroll
FROM employees
GROUP BY department_id
)
SELECT department_id, total_payroll
FROM dept_totals
WHERE total_payroll > 150000;"Is retention getting better or worse?" is one of the most common questions a Data Analyst is asked, and it can't be answered with a single aggregate — you need to group users into cohorts by signup period, then track what fraction of each cohort is still active N periods later. This combines everything from modules 2, 4 and 5: grouping, window functions, and CTEs, applied to a real business question.
GROUP BY, window functions and CTEs stop being separate topics and start being one workflow.Product and growth teams live and die by funnels ("of everyone who viewed, how many bought?") and A/B test readouts ("did variant B actually win, or is that noise?"). Both are conditional aggregation problems at heart — counting users who cleared each step, and comparing conversion rates between groups.
The fastest way to lose a stakeholder's trust is to hand them a number that's wrong because of a NULL you didn't account for, or a duplicate row inflating a count. Before any analysis ships, an analyst checks: are NULLs being silently dropped by a join or an aggregate? Are there duplicate rows with no unique key? This module is what keeps modules 2–7 honest.
COUNT(column) silently ignores NULLs while COUNT(*) doesn't — a one-character difference that changes your denominator.Reading about these patterns only gets you so far — build something you can point to. These projects use the exact techniques from this path on realistic datasets.
A curated set of project ideas built around the exact skills employers screen for.
Revenue trends, top products, and cohort-style repeat-purchase analysis.
A regulated, real-world domain — good practice for handling messy, sensitive data carefully.
Data Analyst interviews lean hard on exactly what you just practiced: window functions, grouping logic, and scenario-based "write me a query for X" prompts.
The interview.php question bank, filtered to the functions category.
Real questions asked in analyst interviews, with worked answers.
Deeper practice on the single most-tested analyst topic.
25 questions covering everything above. Score 80% or higher to earn your Data Analyst badge on your dashboard.
25 questions · pass at 80%+ to earn your Data Analyst badge on your dashboard. One-time payment, lifetime access.