SQL for business analysts: how to get numbers you can prove (2026)
The short answer
To answer business questions with SQL and get numbers you can prove, agree the decision, metric, grain and date range first; know what one row of each table means; count rows before and after every join; handle NULLs and dates deliberately; run data-quality checks; reconcile to a source of truth; and have a colleague review the query.
Want the tools, not just the method? The The SQL for Business Analysts Kit kit turns this guide into ready-to-use templates, spreadsheets and printables, with an instant download.
- Most wrong numbers come from definitions, grain, joins, NULLs and dates, not from syntax.
- Count rows before and after every join: a rise on a many-to-one join is fan-out.
- Use half-open date ranges (>= start AND < next period), never BETWEEN on timestamps.
- Reconcile to a source of truth and explain every difference in named pieces that add up.
- Use AI to draft SQL, then test it: benchmarks show fluent SQL is often wrong.
What does a business analyst need SQL for?
SQL lets you ask a database a question directly instead of waiting for an export. For an analyst that means answering 'how much, how many, which, compared with what' without a queue, and being able to show exactly how a number was produced. The language is old and standard: Edgar Codd's 1970 relational model set the idea of asking for data by what it means rather than where it is stored, and SQL is an international standard, ISO/IEC 9075, with every database adding its own dialect.
The hard part is not syntax. It is producing a number that survives the question 'how do you know?'. The rest of this guide is a method for that, illustrated with one realistic case.
What are the steps to answer a business question with SQL?
Seven steps, in order. Each one prevents a specific kind of wrong number.
- 1. Agree the decision, the metric, the grain of the answer and a half-open date range with the requester.
- 2. Write or reuse a metric definition: plain words, the exact rule, includes, excludes, owner.
- 3. Name the grain of every table you will use, and check it with COUNT(*) against COUNT(DISTINCT key).
- 4. Write the query in named steps (CTEs), counting rows before and after every join.
- 5. Run data-quality checks on the tables you used.
- 6. Reconcile the total to a source of truth within an agreed tolerance, and explain any difference.
- 7. Have a colleague review the query with a checklist, then save it with a version.
Why do joins make totals too big?
When you join a row to a table that has several matching rows, the row is repeated once per match. If you then add up a column from the first table, the total grows. This is called fan-out. Nothing errors and the result looks plausible, which is why it is the costliest mistake in everyday SQL.
A worked example from a practice database of a subscription business: the operations dashboard joined order lines to subscriptions so it could show plan names. Customers who had upgraded had two subscription records, so their lines appeared twice. In August, 820 order lines became 851 rows and revenue rose by $1,130.50.
The fixes, in order of preference: aggregate the many side first and join the summary; join exactly one row per key, chosen on purpose (for example the latest subscription, picked with ROW_NUMBER); or use EXISTS when you only need to know whether a match exists. Whatever you choose, count rows before and after the join.
How should you handle NULLs, dates and the other common traps?
NULL means unknown. SQL compares with three-valued logic, so a condition involving NULL is neither true nor false, and WHERE drops the row. Research on SQL semantics shows that with nulls present, SQL's answers can differ from the answers that are certainly true. In practice: test with IS NULL, avoid NOT IN against lists that may contain NULL (use NOT EXISTS), remember that AVG ignores NULLs, and only use COALESCE where a missing value truly means zero or 'Unknown'.
Dates cause the other quiet errors. If a column holds a date and a time, BETWEEN '2026-08-01' AND '2026-08-31' stops at midnight at the start of the 31st and drops that day's orders. In the practice data that was 15 orders out of 431. Write ranges as start inclusive and end exclusive: order_ts >= '2026-08-01' AND order_ts < '2026-09-01'. And beware integer division: in SQLite, PostgreSQL and SQL Server an integer divided by an integer returns an integer, so a share can come out as zero. Multiply by 1.0 first.
The eight trap pairs in the kit's practice pack each produce a plausible but wrong number; they are listed below.
- Joining to a table with several rows per key, then summing (fan-out).
- BETWEEN on timestamps, dropping the last day.
- NOT IN against a list that contains NULL.
- Integer division in rates.
- Running totals with the default window frame on tied rows.
- COUNT(*) after a LEFT JOIN, counting unmatched rows as one.
- A WHERE condition on the right-hand table of a LEFT JOIN, which turns it into an inner join.
- Turning missing scores into zeros before averaging.
Which window functions do analysts use most?
Window functions calculate across related rows without collapsing them, which is how SQL does running totals, rankings and period comparisons. The common ones are ROW_NUMBER, RANK and DENSE_RANK for rankings; SUM() OVER for running totals and shares; LAG and LEAD for previous and next periods; AVG() OVER with a ROWS frame for moving averages; and NTILE for quartiles.
Two habits keep them right. Filter on a window result in an outer query or CTE, because window functions run after WHERE (BigQuery and Snowflake add QUALIFY for this). And write the frame explicitly: with ORDER BY and no frame, SQLite, SQL Server and others default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes tied rows, so a running total over lines with the same date shows the day's closing total on every line.
How do you check that a SQL number is right?
Run data-quality checks first. The UK Government Data Quality Framework's six dimensions, accuracy, completeness, uniqueness, consistency, timeliness and validity, are a good checklist: duplicate customers, test accounts, orphan rows, impossible dates, values outside their range. Each check is a query returning the number of rows that fail.
Then reconcile. Compare your total with the agreed source of truth, usually finance's closed report, and decompose any difference into named causes that add up. Here is the worked example, from the same practice database, for one month:
| Piece | Amount | How it was found |
|---|---|---|
| Old dashboard query | $31,453.65 | All statuses, test accounts, joined to subscriptions |
| Join fan-out | -$1,130.50 | Row count 820 became 851 after the join |
| Refunded and cancelled orders | -$1,839.15 | Filter on status = 'completed' |
| Internal test accounts | -$498.00 | Filter on the test email domain |
| Finance closed report | $27,986.00 | Corrected query matches to the cent |
Reading helps; measuring tells you what to work on. These AI-graded assessments on AssessAll pair with this topic:
- SQL Query Writing Assessment with Executed Queries (AssessAll)
- Data Cleaning Skills Assessment with Executed Scripts (AssessAll)
- Spreadsheet & Data Literacy (AssessAll)
What should a metric definition contain?
Most arguments about numbers are arguments about definitions nobody wrote down. One dashboard counts every order; finance counts completed orders, leaves out internal test accounts and excludes shipping. Both call it revenue, and both are right for their own purpose, which is exactly why the purpose has to be written next to the number.
A usable definition has seven parts: the metric's name, a plain-words definition a manager would accept, the exact rule in SQL terms, the grain, what it includes, what it excludes and the owner who approves it, with the date they did. Review it when the business changes, or every quarter. In the worked example, the churn definition excludes upgrades, because an upgrade ends one subscription and starts a bigger one: the customer stayed. Counting upgrades would have overstated churn by almost a third.
Keep the definitions where people can find them, next to the queries that use them, and put the definition's version in each query's header comment. When a definition changes, restate history and say so in one sentence to everyone who uses the number.
How do you review someone else's SQL in twenty minutes?
Code review research at Microsoft found that finding defects is the main reason people review code, that reviews find fewer defects than expected, and that understanding the change is the hardest part. For SQL, give the reviewer the context first: the request, the metric definition and the row counts at each step.
Then spend the time on what changes the answer before anything else: the grain, each join (row counts before and after), the filters against the definition, the date range, NULL handling, divisions, window frames and the final total against a trusted figure. Run the query yourself. Layout and naming come last; they matter for the next reader, but they rarely change a number. Finish with a short list of requested changes, and record the review next to the query so everyone can see it was checked.
Can AI write SQL for you?
It can draft SQL quickly, and the vendors say clearly that the output must be checked. The research is sharper. On the BIRD benchmark of large, realistic databases, ChatGPT's queries returned the correct result 40.08% of the time against 92.96% for people. On Spider 2.0, built from enterprise workflows, the best agent framework in the paper solved 21.3% of tasks. Models improve and benchmarks are not your database, but fluent SQL that runs is often wrong.
A safe routine: use the assistant your organisation approves; share the question, the metric definition and the schema, not data; insert the draft into your editor rather than running it from chat; and test it on a question with a known answer, with row counts and a reconciliation. If the assistant can run queries, give it a read-only login. Students should follow their institution's academic-integrity rules on AI first.
How can you practise SQL on realistic business questions?
Practise the way learning research suggests: test yourself and check the answer. In a classic experiment, students who practised by taking tests remembered substantially more after two days and a week than students who restudied. For SQL that means realistic questions, written the way requests arrive, with answers you can check automatically.
The SQL for Business Analysts Kit from Bodhih Training includes a practice database built by a script, 60 graded questions in six levels with tested answers and a checker, eight trap pairs and twelve data-quality checks, along with the workbook and templates for definitions, reconciliation and review. If you want an independent measure of your skills, AssessAll's SQL query writing assessment runs your queries for real, and Jobulary's guide to the fresh-start effect is a good companion for restarting a practice habit.

Practise on realistic data, and prove every number
The SQL for Business Analysts Kit gives you the practice database, 60 graded questions with tested answers, and the checks and templates to make numbers trustworthy at work. Not affiliated with or endorsed by Microsoft, Oracle, Google, Snowflake, GitHub, the SQLite project or the PostgreSQL Global Development Group; product names are used descriptively.
Sources
- GOV.UK (Government Data Quality Hub): The Government Data Quality Framework
- International Organization for Standardization: ISO/IEC 9075-1:2023 Information technology: Database languages SQL, Part 1: Framework
- SQLite (official documentation): Window Functions
- PostgreSQL Global Development Group (official documentation, version 19): Tutorial: Window Functions
- Snowflake (official documentation): QUALIFY
- Google Cloud (official documentation): Write queries with Gemini assistance
- Communications of the ACM 13(6) (copy hosted by the University of Michigan): E. F. Codd: A Relational Model of Data for Large Shared Data Banks
- ICDT 2015, LIPIcs 31 (Schloss Dagstuhl): Leonid Libkin: SQL's Three-Valued Logic and Certain Answers
- arXiv (Li et al., BIRD benchmark): Can LLM Already Serve as A Database Interface? A BIg Bench for Large-Scale Database Grounded Text-to-SQLs
- ICLR 2025 (Lei et al., arXiv): Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows
- ICSE 2013 (TU Delft repository): Bacchelli and Bird: Expectations, outcomes, and challenges of modern code review
- Psychological Science 17(3) (PubMed record): Roediger and Karpicke: Test-enhanced learning: taking memory tests improves long-term retention
More from the Bodhih family
Questions people ask next
Is SQL hard to learn for a business analyst?
The core, SELECT, WHERE, GROUP BY and joins, takes a few weeks of regular practice for someone comfortable with spreadsheets. The harder skill is checking: grain, joins, NULLs and dates. That comes from practice with answers you can check.
Which SQL dialect should I learn first?
Learn the standard core on any database; SQLite needs no server and is a fine start. Then learn the short list of differences for your organisation's database: row limits, date functions, calendars, QUALIFY and integer division.
What is the grain of a table?
What one row represents: one order, one order line, one customer per month. Check it with COUNT(*) against COUNT(DISTINCT key). Most join and total errors start with getting the grain wrong.
Why does my SQL total not match the finance report?
Usually definitions (statuses, test accounts, shipping), fan-out from a join, date boundaries or time zones, duplicates, or a manual adjustment in the report. Decompose the difference into named pieces that add up, and agree the explanation with the report's owner.
Should I use BETWEEN for dates in SQL?
Not for timestamps. Use start inclusive and end exclusive: >= the first moment you want and < the first moment you do not. It works for days, months and years and never drops the last day.
Is it safe to paste data into an AI tool to get SQL help?
Only if your organisation has approved that tool for that data. Usually the schema and the question are enough for a good draft; keep personal data, extracts and passwords out.
What is the difference between RANK and ROW_NUMBER?
ROW_NUMBER gives every row a different number; RANK gives tied rows the same rank and skips the next number. Use RANK when ties should be visible, ROW_NUMBER with a tie-breaker when you need exactly one row per group.