SQL for data analysts: from question to verified result

SQL becomes useful when I can take a business question all the way to a result I can explain. Running a query is only part of that process. I also need to know whether it counts the right things, preserves missing information and answers the question the reader actually asked. A database can execute a misleading query perfectly successfully.

We will work through a small order analysis: define the scope, inspect the tables, join the data and reconcile the output. The example deliberately includes a duplicated order amount after a join, an unmatched customer and a missing amount that must remain distinguishable from zero. Every record is synthetic. You can reproduce the complete exercise without a company database or access to customer information.

Turn the request into a precise question

Suppose someone asks for October orders by customer country. Before writing SQL, I make the definition explicit. We want orders with status paid and an order date in October 2026. We count orders, sum their known amounts and report how many amounts are missing. Orders without a recognised customer remain in an unknown group. Country comes from the current customer table in this exercise.

That last detail matters. We are not reconstructing the customer’s country at the time of purchase. The result also says nothing about recognised accounting revenue, margin or cash flow. We have no returns, tax details or status history. Any of those could change a real reporting definition. Here, I keep the scope within the information the sample actually contains.

Write the definition beside the query. If someone later changes paid to shipped, the change in scope becomes visible. Otherwise, two queries can run successfully and produce different numbers while their authors believe they are answering the same question. When that happens, I compare definitions before discussing formatting or execution speed. Agreement about the population comes first.

Identify the grain and the keys

The grain describes what one row represents. In orders it is one order. In customers it is one customer. In order_items it is one line item. An order can contain several items, which means joining those two tables can repeat the order amount. Whether that repetition is correct depends on what you calculate next.

Keys tell us how records connect. order_id identifies an order and customer_id identifies a customer. The pair order_id and sku identifies an item in this particular exercise. Another system might allow the same product on several separate lines and require a line identifier instead. Similar column names are useful clues, but they do not establish the meaning or uniqueness of a relationship.

Run the fixture below in an empty local SQLite database created for practice. Amounts are integer minor units of one imaginary currency. There is no currency conversion or rounding during addition. Customer 99 is deliberately absent from customers. That controlled inconsistency will help us check whether the report silently loses an order when a customer record is missing.

-- example: fixture
CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  country TEXT NOT NULL
);
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  status TEXT NOT NULL,
  order_date TEXT NOT NULL,
  amount_cents INTEGER
);
CREATE TABLE order_items (
  order_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  quantity INTEGER NOT NULL,
  PRIMARY KEY (order_id, sku)
);
INSERT INTO customers VALUES
  (1,'PL'),(2,'DE'),(3,'PL'),(4,'FR');
INSERT INTO orders VALUES
  (101,1,'paid','2026-10-01',10000),
  (102,1,'paid','2026-10-03',NULL),
  (103,2,'paid','2026-10-02',0),
  (104,2,'cancelled','2026-10-04',5000),
  (105,3,'paid','2026-09-30',9000),
  (106,99,'paid','2026-10-05',2500),
  (107,NULL,'paid','2026-10-05',1500),
  (108,3,'paid','2026-11-01',7000),
  (109,1,'paid','2026-10-06',10000),
  (110,4,'pending','2026-10-07',8000);
INSERT INTO order_items VALUES
  (101,'A',1),(101,'B',2),(102,'A',1),
  (103,'C',1),(106,'D',1),(107,'E',1),
  (109,'A',1);

For a work project, I would also read the data dictionary and ask the source owner about ambiguous fields. Does paid mean confirmed payment? Does the status overwrite earlier states? Is the date a local calendar day or a timestamp stored in UTC? SQL syntax cannot supply those definitions. They have to come from the system and the people responsible for the analysis.

Filter the population and inspect actual records

The first query returns just the order identifier and amount. That small output is easy to compare with the fixture before introducing any aggregation. We expect six identifiers: 101, 102, 103, 106, 107 and 109. September and November orders, cancelled orders and pending orders should stay outside the population. This expectation gives the first query a concrete acceptance check.

-- example: filtered
SELECT order_id, amount_cents
FROM orders
WHERE status = 'paid'
  AND order_date >= '2026-10-01'
  AND order_date < '2026-11-01'
ORDER BY order_id;

The range includes the first of October and excludes the first of November. In this exercise, dates are consistently formatted ISO text, so their ordering matches calendar days. A real timestamp column needs an agreed type and time zone as well. Do not copy text comparisons into a source containing several incompatible date formats and assume the same result.

I prefer an explicit column list while building the analysis. It makes the required inputs visible and keeps the intermediate result manageable. Selecting every column can help when exploring a tiny sample, but a wide export quickly becomes harder to inspect. A row limit is useful for exploration too; it does not demonstrate that the full dataset is complete or that every record follows the pattern you saw.

Check how a join changes the grain

Now we will make a deliberate mistake. We join orders to their items and sum the order amounts. Order 101 has two items, so its amount appears twice. Every eligible order happens to have at least one item in this fixture. That lets us isolate multiplication of rows without also introducing lost orders from the inner join.

-- example: wrong_join
SELECT COUNT(*) AS joined_rows,
       SUM(o.amount_cents) AS repeated_amount,
       SUM(DISTINCT o.amount_cents) AS distinct_amount
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'paid'
  AND o.order_date >= '2026-10-01'
  AND o.order_date < '2026-11-01';

The joined result contains seven rows and a sum of 34000. The correct sum of known amounts across the six eligible orders is 24000. The extra 10000 comes from counting order 101 again. The database has followed the instructions exactly: we asked it to add a value defined at order level after moving the data to item level.

Using DISTINCT inside the sum produces 14000, which is another incorrect answer. Orders 101 and 109 have equal amounts, but they are separate purchases. Deduplicating amount values removes a legitimate order as well. If item data is necessary, I first bring it to the required order grain or check whether a matching item exists. For this country report, the item table is unnecessary.

Before every join, I write down the expected relationship: one to one, many to one or many to many. Afterwards, I compare row counts and distinct keys. A change in row count is not automatically a defect, but I need to explain it. The important question is whether the next calculation still operates at the level its measure requires.

Build the final report in readable stages

Our final report needs only orders and customers. The eligible CTE names the filtered population. The next stage adds country, and the final query groups the result. Naming the stages makes it easier to inspect the population and the relationship separately. It does not promise a faster execution plan. SQLite describes the syntax in its WITH documentation.

-- example: summary
WITH eligible AS (
  SELECT order_id, customer_id, amount_cents
  FROM orders
  WHERE status = 'paid'
    AND order_date >= '2026-10-01'
    AND order_date < '2026-11-01'
), labelled AS (
  SELECT e.order_id, e.amount_cents,
         COALESCE(c.country, 'unknown') AS country
  FROM eligible AS e
  LEFT JOIN customers AS c
    ON c.customer_id = e.customer_id
)
SELECT country,
       COUNT(*) AS order_count,
       SUM(amount_cents) AS known_amount,
       COUNT(*) - COUNT(amount_cents) AS missing_amounts
FROM labelled
GROUP BY country
ORDER BY country;

LEFT JOIN retains orders without a matching customer, and COALESCE labels the missing country unknown. A later WHERE condition requiring country PL would remove those unmatched records. Conditions on the right table therefore deserve careful attention: their location changes the population. The SELECT documentation explains join and filter behaviour.

The query’s missing_amounts column is 1 for PL and 0 for DE and unknown.

The French customer exists, but their order is pending. France consequently has no row in a report built from eligible orders. If the reader wants every country, including countries with no activity, we need a country list and a different output contract. Adding a zero only at the chart layer would conceal that decision instead of documenting it in the analysis.

Keep unknown amounts separate from zero

Order 103 has an amount of zero. Order 102 has NULL. We know the first value and do not know the second. That distinction is why the report contains both a known amount total and a missing amount count. A reader can see whether the total covers every order before using it to compare countries or assess a change.

COUNT(*) counts rows, COUNT(amount_cents) counts known amounts, and SUM ignores NULL values. SUM returns NULL when there are no known values to add. These rules are documented under SQLite aggregate functions. The German zero stays zero. The Polish total of 20000 comes from two known amounts and is accompanied by one explicitly missing amount.

I do not replace missing amounts with zero without a business reason. That replacement could hide an incomplete import or delayed payment information. The unknown customer group also combines two different situations: a missing identifier and an identifier that has no match. They can share a reporting label while still requiring separate investigation. A convenient display category should not erase the reason a record needs attention.

Reconcile before sharing the result

Start with a simple reconciliation. The country groups contain six orders, matching the filtered population. Their known amounts add to 24000 and their missing amount counts add to one. There are two orders in the unknown group. If those figures disagree, go back to the last stage where they matched and inspect the identifiers introduced or removed by the next operation.

The tests for this exercise execute the same SQL blocks from both language versions in SQLite. They check the main result, an empty population, groups containing only missing amounts, a real zero, an unmatched key and rejection of a duplicate primary key. The deliberately wrong join has an expected wrong result too. If the fixture changes, the explanation must still describe what the query actually does.

Matching totals alone cannot approve a report. Two errors can cancel each other out. I also inspect boundary cases: the start and end of the period, a cancelled order, a missing amount and a missing customer. The selection should follow the definition of the question. Checking one convenient, ordinary record tells you much less about whether the difficult cases behave correctly.

For a larger source, keep a small control sample with an expected result alongside counters for the full population. The sample explains the logic; the counters show whether the scale of a problem changes. My article on data cleaning develops the distinction between fixing an issue and merely making it disappear from view. Both habits are useful long before a dashboard is built.

Read unfamiliar SQL from the inputs forwards

When I receive a long query, I first find the source tables, filters and join keys. For each CTE, I write one sentence describing its row grain. Only then do I examine the final measures. This order helps reveal that an amount was multiplied several stages before reaching an innocent-looking SUM at the bottom of the query.

Window functions become useful when a calculation needs to retain individual rows, such as ordering purchases within a customer group. Partitioning and ordering still require deliberate choices, including a tie breaker when dates match. Unrecognised customers need explicit treatment. SQLite explains the mechanism in its window function documentation.

Our question asks for country-level output, so ordinary aggregation is enough. If a new requirement asks for each customer’s latest eligible order, I revisit the definition and the missing identifiers before selecting a function. Adding a sophisticated construct cannot repair an unclear question. It often just makes the unclear assumption harder for the next analyst to notice during review.

Separate orders, items and units

The item table also contains quantity. Order 101 has two lines but three product units. A request for products sold therefore needs clarification: units, line items and distinct product codes are different measures. Each can answer a legitimate question, but the output needs a name that identifies the chosen unit. Counting rows alone does not settle what a reader intends to compare.

We also cannot allocate the full order amount equally across its items without an additional definition. We do not know item prices, discounts or whether delivery is included. This fixture supports an order report by country; it cannot establish reliable product-level sales values. Recognising the boundary helps prevent a neat-looking query from making a claim the source cannot support.

Make the query reviewable

Before handing over the result, I ask another reader to follow an ordinary order and an exception from the input through the filters into the final country group. If we disagree about an order without a customer, we return to the definition. Formatting cannot resolve a disagreement about which population belongs in the report.

A useful handover also considers source changes. A new status may require a scope decision. A new country should pass through grouping without a manually added column. Duplicate customer records should trigger a uniqueness check before they multiply orders. Under the table, state the selected status, date basis, value unit and extraction time. Readers should not have to reconstruct these details from a filename or guess whether a blank means zero.

Optimise a verified answer

For a slow query, I inspect the execution plan, source sizes and row counts after joins. SQLite provides EXPLAIN QUERY PLAN, described in the official guide. An index may help, depending on the conditions and data distribution. Ten synthetic orders cannot establish how a query will perform against a large company warehouse.

Compare both results and execution cost under similar conditions. A faster query that drops unmatched customers answers a different question. Partitioning, date syntax and diagnostic tools also differ between database systems. These examples were executed in SQLite. Before moving them to another engine, check its documentation and rerun the boundary cases instead of assuming identical behaviour or performance.

Optimisation discussions are clearer when the team can point to a reproducible baseline. Keep the original result and the assumptions that produced it, then change one relevant part at a time. If a plan changes after a source grows, investigate the new conditions. Avoid turning a useful observation from one small dataset into a universal rule about every join or subquery.

Leave a result someone else can reproduce

Alongside the report, I keep the population definition, query version, data extraction time and a short note about missing values. If customer country can change, I state whether the query uses current values or history. That tells the next reader why rerunning the same period a month later might produce a different country split without any change to the SQL.

For a follow-up exercise, move the range to November and predict the answer before running the query. There should be one eligible order, 108, in PL, with amount 7000. Then select a month with no orders and decide whether the reader expects an empty table or explicit zeros for a fixed country list. Those are different report contracts and should be explained in ordinary language.

Finally, imagine the customer dimension acquiring two rows for the same identifier. Our primary key prevents that in the fixture, but a raw export might not. Before joining that export, you would need a rule for choosing or consolidating records. Selecting an arbitrary row could hide a disagreement about country. Treat the uniqueness check as part of the analysis, not as an assumption inherited from a column name.

SQL skills develop alongside the habit of checking what data means. You can continue with a histogram built using CTE and CASE WHEN. Keep the same sequence: define the observation, predict a small expected result, test difficult cases and then scale up. A query becomes useful when another person can follow both its calculation and the limits of its answer.