SQL for Data Analysts: The Queries You Actually Write

Job adverts say “SQL required” and leave it there. In practice a data analyst uses a small, sharp subset of SQL over and over: reduce a table to the rows that count, group them, join in the names, and shape the result into something a human can read. This page is that subset, in the order you meet it, with every query runnable against the sample database.

The shape of almost every analyst query

Nine reporting queries in ten are the same skeleton. Learn it once and most requests become fill-in-the-blanks:

SELECT   <the thing you group by>, <the numbers you want>
FROM     <the fact table>
JOIN     <the lookup tables that carry names>
WHERE    <which rows count>
GROUP BY <the thing you group by>
HAVING   <which groups count>
ORDER BY <the number that matters> DESC;

Here it is filled in. “Revenue by country, shipped orders only, biggest first”:

SELECT c.country,
       COUNT(*) AS orders,
       ROUND(SUM(o.order_total), 0) AS revenue
FROM Orders o
JOIN Customers c ON c.customer_id = o.customer_id
WHERE o.status = 'Shipped'
GROUP BY c.country
ORDER BY revenue DESC;

That single pattern — filter, join for names, group, sort — answers a surprising share of the questions an analyst gets asked.

Filter before you aggregate, not after

The most common analyst bug is filtering in the wrong place. WHERE runs before rows are grouped; HAVING runs after. Getting this backwards either fails outright or, worse, silently returns the wrong number.

-- Departments with more than one person, and what they average.
SELECT d.dept_name,
       COUNT(*) AS headcount,
       ROUND(AVG(e.salary), 0) AS avg_salary
FROM Employees e
JOIN Departments d ON d.dept_id = e.dept_id
WHERE e.active = 1
GROUP BY d.dept_name
HAVING COUNT(*) > 1
ORDER BY avg_salary DESC;

Read it as two filters at two moments: WHERE e.active = 1 decides which people count, HAVING COUNT(*) > 1 decides which departments count. Analysts who can say that sentence out loud stop writing this bug.

Conditional aggregation: one row, many numbers

Dashboards want a single row with several measures. The pattern is SUM(CASE WHEN ... THEN 1 ELSE 0 END), and it is worth memorising because it replaces three separate queries with one pass over the data.

SELECT COUNT(*) AS total_orders,
       SUM(CASE WHEN status = 'Shipped'   THEN 1 ELSE 0 END) AS shipped,
       SUM(CASE WHEN status = 'Pending'   THEN 1 ELSE 0 END) AS pending,
       SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled,
       ROUND(SUM(CASE WHEN status = 'Shipped' THEN order_total ELSE 0 END), 0) AS shipped_revenue
FROM Orders;

The same trick pivots rows into columns. Swap the condition for a category and you have a cross-tab without any special syntax:

SELECT category,
       COUNT(*) AS products,
       SUM(CASE WHEN in_stock > 100 THEN 1 ELSE 0 END) AS well_stocked,
       SUM(CASE WHEN in_stock <= 100 THEN 1 ELSE 0 END) AS low_stock
FROM Products
GROUP BY category
ORDER BY products DESC, category;

LEFT JOIN and the zero that must appear

An analyst is usually asked for “every X and its Y”, where some X have no Y. An inner join silently drops those, and the report is wrong in a way nobody notices until someone asks why a department is missing.

SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM Departments d
LEFT JOIN Employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_name
ORDER BY headcount DESC;

Time series: revenue by month

Almost every analyst report has a time axis. Group by the parts of the date rather than the date itself:

SELECT YEAR(order_date) AS yr,
       MONTH(order_date) AS mth,
       COUNT(*) AS orders,
       ROUND(SUM(order_total), 0) AS revenue
FROM Orders
WHERE status = 'Shipped'
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY yr, mth;

Keep the year in the GROUP BY even though every order here falls in 2024. Grouped by the month alone, January 2025 would be added to January 2024 the moment the data crossed a year, and nothing would look wrong.

Two more habits save you here. First, filter the date range with a half-open interval — >= '2024-01-01' AND order_date < '2024-04-01' — because BETWEEN on a timestamp column quietly loses the last day. Second, expect the date functions to change from one database to the next. YEAR() and MONTH() are MySQL and SQL Server functions; PostgreSQL writes EXTRACT(YEAR FROM order_date), and SQLite has none of these — its own spelling is strftime('%Y', order_date), which returns text. The playground runs SQLite and adds YEAR(), MONTH() and DAY() for convenience, so this query runs as written. The dialect guide has the translation table.

Multi-step questions: name the steps

Real questions rarely fit one clause. “Which categories earn the most from shipped orders, and what is the average order in each?” is two ideas. A CTE lets you write them in the order you think them:

WITH shipped AS (
  SELECT o.order_id, o.order_total, p.category
  FROM Orders o
  JOIN Products p ON p.product_id = o.product_id
  WHERE o.status = 'Shipped'
)
SELECT category,
       COUNT(*) AS orders,
       ROUND(SUM(order_total), 0) AS revenue,
       ROUND(AVG(order_total), 0) AS avg_order
FROM shipped
GROUP BY category
ORDER BY revenue DESC;

This is the single biggest readability upgrade available to an analyst. When the definition of “shipped” changes, you edit one CTE instead of hunting through a nest of subqueries.

Rankings and running totals

“Top 3 per category”, “rank customers by spend”, “running total by month” are window-function questions, and they separate an intermediate analyst from a senior one.

SELECT name, category, unit_price,
       ROW_NUMBER() OVER (ORDER BY unit_price DESC) AS price_rank
FROM Products
ORDER BY price_rank;

ROW_NUMBER is safe here only because no two products cost the same. Where values can tie — two customers with equal spend, two products with equal stock — the order among the tied rows is not defined, so who makes “the top 3” is left to chance. Use RANK or DENSE_RANK to treat ties as ties, or add a second column inside OVER as a tie-breaker.

Period-over-period is the other everyday window job. Build the monthly totals in a CTE, then let LAG fetch the previous month and SUM() OVER keep a running total:

WITH monthly AS (
  SELECT YEAR(order_date) AS yr,
         MONTH(order_date) AS mth,
         ROUND(SUM(order_total), 0) AS revenue
  FROM Orders
  WHERE status = 'Shipped'
  GROUP BY YEAR(order_date), MONTH(order_date)
)
SELECT yr, mth, revenue,
       revenue - LAG(revenue) OVER (ORDER BY yr, mth) AS vs_prev_month,
       SUM(revenue) OVER (ORDER BY yr, mth) AS running_total
FROM monthly
ORDER BY yr, mth;

January has no earlier month, so its change is NULL rather than an invented zero. February shipped 4760, which is 13525 less than January. The running total ends at 66059, the same shipped revenue the conditional-aggregation query above reports — a cheap way to check a report against itself.

The full family — RANK, DENSE_RANK, PARTITION BY, moving averages, LEAD and NTILE — is covered in the window functions guide, where every example runs in the playground and shows the rows it returns.

Seven mistakes that corrupt reports quietly

1. Inner join where a left join belonged. Rows with no match vanish and the total is too low.
2. Joining without checking the grain. If one order matches three rows on the other side, every SUM triples. Count rows before and after a join when the number looks suspicious.
3. NOT IN against a column containing NULL. If the subquery returns even one NULL, NOT IN returns no rows at all. The playground shows it: WHERE emp_id NOT IN (SELECT manager_id FROM Employees) finds nobody, because manager_id is NULL for the employees who have no manager. Use NOT EXISTS.
4. BETWEEN on a timestamp. The last day is truncated to midnight, so a day of data disappears. Use a half-open range.
5. AVG over a column with NULLs. AVG skips them, so the denominator is not what you assumed. Decide whether NULL means zero, and say so with COALESCE.
6. Filtering an aggregate in WHERE. Either an error or, with a subquery, the wrong rows.
7. Integer division. In SQLite, PostgreSQL and SQL Server an integer divided by an integer is an integer: the fraction is thrown away, so a ratio of two counts usually comes out as 0. MySQL returns a decimal instead, which is how the bug slips through when a query moves between databases. Multiply by 100.0 or 1.0, or CAST one side to a decimal, before dividing.

The last one is easy to watch, because the playground runs SQLite. Two of the fifteen orders are cancelled; here is that share worked out three ways:

SELECT SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) / COUNT(*) AS int_share,
       SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) * 100 / COUNT(*) AS int_pct,
       ROUND(SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS pct
FROM Orders;

2 / 15 is 0 in integer arithmetic, so the first column reports no cancellations at all. Multiplying by 100 first gives 13, still missing the .3. Only the third, where 100.0 makes the arithmetic decimal, gives the true 13.3. The sample database stores money as REAL, so dividing salaries or order totals keeps the fraction; the trap is counts, quantities and any column declared INTEGER.

What to learn next, in order

If you want a checklist for becoming employable rather than a reading list:

1. Be fluent in the skeleton at the top of this page. Write it without thinking.
2. Know when each join type is correct, and be able to explain it in one sentence.
3. Master conditional aggregation. It answers most dashboard requests.
4. Use CTEs by default for anything longer than a screen.
5. Learn window functions well enough for top-N-per-group and running totals.
6. Practise until you stop making the seven mistakes above.

The 42 exercises are ordered to build exactly this, and every answer is checked by running it. For interviews, work the ten analyst scenarios with the solutions hidden.

Frequently asked questions

Related reading

• SQL Aggregate Functions: COUNT, SUM, AVG, MIN and MAX
• SQL ORDER BY and LIMIT: Sorting, Top-N and Pagination
• SQL Scenario Questions for Data Analyst Interviews
• SQL Exercises with Solutions: 15 Practice Problems
• SQL Window Functions: ROW_NUMBER, RANK & PARTITION BY
• PostgreSQL vs MySQL vs SQL Server vs SQLite