SQL for Data Analysis: The Skills Analysts Use
· 7 min read
SQL for data analysis is a small part of SQL used very often. Analysts mostly read data, not change it: they filter rows, join tables, aggregate into totals and averages, and check that the numbers are right. If you can write and sanity-check a query like this one, you have the core of the job:
SELECT i.category,
SUM(i.quantity * i.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY i.category
ORDER BY revenue DESC;It joins two tables, keeps the right orders, adds up revenue per category and sorts the answer. Below is the list of skills that matter for analysts, then a small analysis worked from first look to checked result, then a sensible order to learn things in.
The SQL skills a data analyst actually needs
| Skill | What it answers | Keywords |
|---|---|---|
| Select and filter | "Show me the March orders from the North" | SELECT, WHERE, IN, BETWEEN |
| Sort and limit | "Top 10 customers" | ORDER BY, LIMIT |
| Aggregate | "How many, how much, on average?" | COUNT, SUM, AVG, MIN, MAX |
| Group | "...per region, per month" | GROUP BY, HAVING |
| Join | "Add the customer's segment to each order" | JOIN, LEFT JOIN |
| Handle missing data | "Treat no discount as 0" | IS NULL, COALESCE |
| Categorise | "Label orders small, medium, large" | CASE WHEN |
| Dates | "Revenue by month" | EXTRACT, DATE_TRUNC |
| Multi-step logic | "Average per order, then per region" | subqueries, CTEs |
| Compare rows | "Change from last month, rank within category" | window functions |
The first six cover a large share of everyday requests. The last four are what separates a junior analyst from someone who can answer harder questions without exporting to a spreadsheet.
What you mostly do not need early on: creating tables, writing INSERT or UPDATE statements, indexes, stored procedures and database administration. Those belong to engineers. An analyst's SQL is almost all SELECT.
A worked analysis
Brightline Supply Co. sells office, kitchen and cleaning supplies. Your manager asks: "Which product category brought in the most revenue in May?" Here are the two tables.
| order_id | customer_name | order_date | status |
|---|---|---|---|
| 1 | Acme Dental | 2025-05-02 | completed |
| 2 | Birch Cafe | 2025-05-06 | completed |
| 3 | Acme Dental | 2025-05-19 | refunded |
| 4 | Cobalt Labs | 2025-05-21 | completed |
| 5 | Dune Studio | 2025-05-28 | cancelled |
| order_id | category | quantity | unit_price |
|---|---|---|---|
| 1 | Office | 20 | 4.50 |
| 1 | Kitchen | 2 | 18.00 |
| 2 | Kitchen | 10 | 2.40 |
| 2 | Cleaning | 6 | 3.00 |
| 3 | Office | 40 | 4.50 |
| 4 | Cleaning | 12 | 3.00 |
| 4 | Office | 5 | 6.80 |
| 5 | Kitchen | 30 | 2.40 |
Step 1: look at the data before you query it
Run SELECT * FROM order_items LIMIT 3 and read the rows. You learn that one order has several item rows, so the table has one row per order line.
Step 2: check the grain and the statuses
Two quick counts tell you what you are dealing with:
SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC, status;| status | orders |
|---|---|
| completed | 3 |
| cancelled | 1 |
| refunded | 1 |
And SELECT COUNT(*), COUNT(DISTINCT order_id) FROM order_items returns 8 rows for 5 orders. Now you know two things that will shape the query: not every order counts as revenue, and any "per order" number needs a step that combines lines first.
Step 3: filter, join and aggregate
The query at the top of this page does the job:
SELECT i.category,
SUM(i.quantity * i.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY i.category
ORDER BY revenue DESC;| category | revenue |
|---|---|
| Office | 124.00 |
| Kitchen | 60.00 |
| Cleaning | 54.00 |
Office wins. Each piece of syntax here has its own guide: joining two tables and GROUP BY.
Step 4: check the answer
Never send a number you have not checked. Two habits:
- Totals reconcile. The categories add up to 238.00. Run the same query without
GROUP BYand confirm it also gives 238.00. If the parts do not add up to the whole, a join or filter is off. - One row by hand. Cleaning is order 2's 6 × 3.00 = 18 plus order 4's 12 × 3.00 = 36, so 54. It matches.
Step 5: answer the next question
The follow-up is almost always "and what's our average order?" This is where grain matters. Combine lines into orders first, then average:
WITH order_totals AS (
SELECT o.order_id, SUM(i.quantity * i.unit_price) AS order_total
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY o.order_id
)
SELECT COUNT(*) AS orders, ROUND(AVG(order_total), 2) AS avg_order
FROM order_totals;| orders | avg_order |
|---|---|
| 3 | 79.33 |
Averaging the item rows directly gives 39.67, half the real figure, because it averages lines, not orders. Same tables, same filter, very different answer. The WITH part is a CTE; see SQL CTE explained.
How to learn SQL for data analysis
A learning order that builds each step on the last:
SELECT,WHERE,ORDER BY,LIMIT. Read and filter one table.- Aggregates and
GROUP BY, thenHAVING. Turn rows into summaries. - Joins. Inner joins first, then left joins and what their
NULLs mean. CASE WHEN,NULLhandling and dates. The everyday cleanup.- CTEs and subqueries. Answer questions that need two steps.
- Window functions. Rankings, running totals, month-over-month.
At every step, practise on data that looks like a real business: several related tables, statuses that matter, missing values. Toy tables with one perfect column teach syntax but not judgment. Practisheet is built around this idea: one fictional company's data, and tasks where you also have to catch the mistakes in an AI assistant's query. Wherever you practise, check your own results the way step 4 does.
When you are ready to test yourself, SQL interview questions for data analysts covers what tends to come up.
Common mistakes
Missing the business filter (the AI mistake to watch for)
Ask an AI assistant "which category had the most revenue in May?" without mentioning statuses, and it will usually write a clean join and group, with no status filter:
SELECT i.category,
SUM(i.quantity * i.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
GROUP BY i.category
ORDER BY revenue DESC;| category | revenue |
|---|---|
| Office | 304.00 |
| Kitchen | 132.00 |
| Cleaning | 54.00 |
Office still wins, so the headline looks the same, but revenue is overstated by more than double: a refunded order and a cancelled order are counted as sales. If your manager uses 304 in a forecast, that is a real problem.
How to spot it: before reading any AI-written query, write down the business rules the answer depends on (which statuses count, which dates, which customers). Then check that each rule appears in the WHERE. An assistant does not know your company's definition of revenue unless you tell it.
Trusting a query because it ran
No error message does not mean the answer is right. Wrong join keys, missing filters and the wrong grain all run without complaint. Reconcile totals and check one row by hand.
Learning syntax without questions
Memorising every clause is less useful than practising the translation from a business question to a query. Start from the question, every time.
FAQ
Is SQL enough for a data analyst?
SQL is the core skill for getting and shaping data, but most analyst roles also expect a spreadsheet tool, a BI or charting tool, and the ability to explain results. SQL is usually the first one to learn.
How long does it take to learn SQL for data analysis?
It depends on how often you practise. The basics in steps 1 to 3 of the learning order are enough to start answering real questions. Fluency comes from practising on realistic problems, not from reading.
Which SQL dialect should a data analyst learn?
Any mainstream one: Postgres, MySQL, SQL Server, DuckDB or BigQuery. The core (SELECT, JOIN, GROUP BY, window functions) is the same. Differences like LIMIT vs TOP or date functions take a day to adjust to.
What SQL questions are asked in data analyst interviews?
Mostly the skills in the table above: joins, GROUP BY with HAVING, CASE WHEN, window functions for ranking, and spotting why a query gives the wrong answer.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- How many completed orders?
- Revenue by sales channel
- Average order value by segment
- Revenue that includes refundsAudit