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;

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;

Two 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, remember that the function names differ by database; 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;

The full family — RANK, DENSE_RANK, PARTITION BY, running totals with SUM() OVER — is covered in the window functions guide, which marks honestly which examples run on this site’s in-browser engine and which need PostgreSQL or MySQL 8.

Six 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. In a real database it returns nothing at all (the in-browser engine here does not reproduce this, so the playground will not show it). 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.

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 six 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