SQL for analysts: beyond syntax to a correct result

I would assess SQL knowledge by asking about a result. What does one row represent? Which records disappear? Why did the amount increase after a join? You can recognise many advanced constructions and still miss duplicated sales. I want to move from a business question to a query whose result I can explain, including when the data no longer resembles the neat example in a course.

I do not have evidence establishing what proportion of analysts know SQL only superficially. Instead of judging everyone, I propose a practical assessment of your own understanding. We will follow a small customer and order report. The syntax refers to PostgreSQL 18, and the records are synthetic. The explanations use documented rules and small datasets that you can reason through manually, rather than claims of benchmarked performance.

Define the data’s level before writing SELECT

Suppose you need the order value for every customer. There are three customers, A, B and C. Customer A has two orders, for 100 and 50. Customer B has one order for 80. Customer C has no order. The intended report contains one row per customer, giving three rows. You can write down the expected amounts before writing any SQL. This establishes what the query is supposed to preserve.

A’s value is 150 and B’s is 80. For C, agree how to display the absence of orders. Should the report show zero or a missing value? Both displays may be technically possible, but they communicate different things. Zero makes sense as an order count. For an amount, first distinguish a customer with no transactions from a customer whose transaction values were never supplied. That difference should survive into the explanation of the report.

I also identify the key of each source. The customer table needs a defined uniqueness rule for its identifier, while orders need their own identifier. A customer’s name may change or occur more than once, so it is not automatically a suitable key. Before joining sources, read their definitions and establish whether the proposed matching columns really are unique at the level you expect. A familiar column name provides no such guarantee.

Understand why a join changes row counts

PostgreSQL’s table-expression documentation explains joins and the transformations that follow them. Joining customers to orders with a left join produces two rows for A, one for B and one for C with no matching order fields. Four rows are consistent with the relationship. An increased row count is therefore something to explain, rather than automatic proof that the join was implemented incorrectly.

Now suppose you add order lines while continuing to sum each entire order’s amount. A’s order for 100 has two lines, so the amount may appear twice. Adding 100, 100 and 50 gives 250 even though A ordered only 150. Before every additional join, I ask how many matches a single row can find and at which level the new measure is stored. Those two questions expose many errors before the report becomes complicated.

Adding DISTINCT does not resolve the meaning of that problem. Two separate orders for 100 should still contribute 200. Removing repeated amounts could delete a valid transaction from the calculation. The appropriate repair depends on the report: aggregate lines to orders first, use line-level amounts or prepare separate summaries. Establish the intended result and then choose the construction. A shorter query is useful only when it still expresses the right relationship.

Distinguish matching rules from result filters

Imagine that you want every customer but only their open orders. A status condition in the join’s ON clause restricts matching orders. A customer without an open order can remain with empty order fields. Putting the same condition in WHERE after the join removes rows that do not satisfy it, including the usual unmatched customer row. The placement changes which customers the report preserves, even though the status text itself stays the same.

All customers with their open order value and customers who have open orders are separate questions. I would name that difference before starting. Otherwise, a syntactically valid query can silently answer a different question from the one the reader intended. The editor’s successful execution message cannot identify that substitution. It confirms something about the statement’s execution, while the business meaning still depends on how you translated the request.

For practice, include a customer with no orders and another with only closed orders. Follow both through the two versions of the condition. You do not need a huge database to see the consequence. You need cases that reveal the rule. Once you can explain each one, more complicated filters become easier to discuss. You have a model to reason with instead of a slogan remembered from an unrelated exercise.

Count the thing you actually mean

After a left join, COUNT(*) counts result rows, while COUNT(o.id) counts non-null order identifiers. For C, the former sees the preserved customer row and the latter finds no order identifier. That follows PostgreSQL’s aggregate rules. It is worth explaining the distinction in ordinary language before relying on a particular expression. Your reader probably wants a count of orders rather than an accidental count of rows created by the join.

A possible report shape is shown below. Assume customers and orders contain the records described above, and that orders.id is a non-null key:

SELECT
    c.id,
    COUNT(o.id) AS order_count,
    SUM(o.amount) AS order_value
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.id
GROUP BY c.id
ORDER BY c.id;

The order counts are two for A, one for B and zero for C. C’s sum remains NULL because there is no non-null amount to sum. I would not replace it without agreeing its interpretation. COALESCE can explicitly display zero for the absence of orders, but a blanket replacement may also conceal existing orders without prices. A helpful report distinguishes those situations instead of giving every missing amount the same reassuring display.

Explain what NULL means in the source

A missing shipment date might mean an unshipped order or a failed integration. SQL stores NULL, but that value alone does not document the entire process. I would read the field definition and ask when it is populated. Without that context, a filter might select both genuine backlog and damaged imported records. The resulting count could look ordinary while combining two very different reasons for missing information.

Distinguish comparing a value from checking for absence. IS NULL and IS NOT NULL are explicit checks for missing values. Empty text, zero and NULL should not be treated as interchangeable by default. An older source may use several representations of the same underlying problem. Standardising them requires a stated rule. Keep that rule understandable enough to discuss with the person responsible for the source, particularly when it changes which records reach a report.

I would include the number of orders with missing amounts alongside the sum of known amounts. The limitation is then visible. A reader may decide that this is sufficient for preliminary planning or may wait for the missing values before acting. Your job is to deliver the result with a clear boundary around what it supports. An aggregate returning a number does not establish that the business data is complete.

Use CTEs to name stages of reasoning

For a larger report, I would separately name the selection of relevant orders, their aggregation and their combination with customers. WITH queries, or CTEs, provide named auxiliary queries. A name such as open_orders_by_customer communicates a stage’s purpose. The next reader can follow the intended transformation before unpacking several levels of brackets, abbreviated aliases and conditions that otherwise compete for attention in one statement.

A CTE does not guarantee correctness or faster execution. A wrong summary can be beautifully organised. For each stage, I would state the expected row level and key: one order here, one customer there. That makes it easier to spot where a new table changes the relationship. It also gives a reviewer a useful question to ask, whereas a comment merely repeating the name of a SQL clause adds little information.

Do not assume every CTE becomes a physical table built in a fixed sequence. Execution depends on the query and optimiser. I treat the stage names as a way to understand the logic, then use the plan to discuss physical execution. Keeping those perspectives separate prevents the conclusion that moving code into WITH has automatically solved a performance problem. Readability is valuable without attaching an unsupported speed claim to it.

Preserve detail with a window function

Suppose you need each order and its customer’s total alongside it. Ordinary grouping by customer reduces the result to customer-level rows. A window function can calculate across related rows while retaining the individual orders. A’s two orders can therefore remain separate, with the same customer total displayed next to both. You have added context without changing the intended detail of each row.

That total is repeated information. Summing the customer-total column afterwards can overstate the overall value again. Window functions give you more ways to describe calculation context; they still require attention to granularity. I would identify which columns can be added across the current view and which are present only for comparison or display. This matters when someone exports the result and builds another summary from it without seeing the original query.

Numbering rows or calculating a running total also requires ordering and tie rules. Two events on the same date may need another key to establish the intended sequence. The window frame matters too. Before adding OVER, explain which records should participate in the calculation for the current row and what should happen at a tie. The advanced-looking syntax is useful when it expresses those decisions clearly enough for another person to inspect.

Separate correctness from performance

A query can return the wrong records very quickly. I would establish correctness on a small dataset first, then investigate cost under representative conditions. PostgreSQL’s EXPLAIN guide explains how to read a plan. Estimated cost is not a promise of a particular number of milliseconds. Interpret the plan in the context of data, statistics and environment, rather than turning one displayed figure into a universal performance claim.

I would not add indexes blindly to every field used in a filter. Distribution, selectivity, table size and reporting patterns all matter. Work with the team responsible for the database to agree a diagnostic approach. An analyst should be able to describe the problem and provide the relevant query. Who may change infrastructure depends on the team’s responsibilities and process. Understanding a plan helps that conversation without requiring you to take ownership of every database setting.

Remember that EXPLAIN ANALYZE actually executes the statement being examined. That distinction matters particularly for statements that modify data. Here the goal is to understand a plan, without suggesting experiments against a live production system. Ask where a comparison can run and which dataset would be representative. A useful investigation should reveal the problem while respecting the environment in which other people are already working.

Agree the period before comparing reports

For a monthly report, identify the date defining the month. Creation, payment and shipment dates can assign the same order to different periods. Each may be appropriate, but changing the field should change the measure’s description. An order created in late November and shipped in early December makes the distinction easy to discuss.

Agree boundaries and time zone as well. Include synthetic orders just before the period, exactly at its start and exactly at the next period’s start. Explain which belong before choosing syntax for the column type. A condition copied from another monthly report may encode a different calendar or business event.

When two totals disagree, compare definitions first: status, date, amount level and source snapshot. Once those match, a discrepancy becomes a concrete technical question. Otherwise, you can spend hours modifying two queries that correctly answer separate questions.

Check orders without matching customers

Add a synthetic order for customer D, whose identifier is absent from the customer table. A report starting from customers may omit it. That does not establish that the order does not exist. It shows that the chosen starting set and relationship do not cover every order. Missing matches need their own check.

This matters when grouping by a lookup’s descriptions. An unknown region or product may disappear or fall into an unassigned category. I would show the count and value of such records. The reader can then decide whether to repair the lookup or accept a temporary category, instead of receiving an unexplained reduction in coverage.

Start by listing identifiers from both sources on paper. State what should remain and which fields should be empty. This helps explain join coverage before choosing a construction for exception reporting. It also gives you a clear reason why the report’s total might differ from the sum of the entire orders table.

Prepare the query for another reader

Leave a short explanation of inputs, measures and parameters. Stage names should reveal whether they contain every order, open orders or one period only. Comments are useful for explaining an unusual exclusion’s reason. Repeating the condition adds less than explaining why it expresses the reader’s business question.

When presenting a sample or ranking, establish output order and tie rules. Check that a temporary row limit used during development has not accidentally remained in a report requiring the complete list. These small choices change how the output can be used. I treat explaining them as part of completing the assignment, alongside writing the SQL itself.

Practise changing the assumptions

Return to the customer report and add another customer without orders. Next add an order without an amount, then two separate orders with the same value. Finally, introduce order lines so that one order has two lines. At each step, state the expected row count and total. The purpose is to understand the model you built. These are ordinary data situations, and the report should have an explicit interpretation for each of them.

Then ask someone to change the request: show all customers, show only active customers, or show customers without open orders. Explain which conditions change before writing more SQL. This also develops analytical communication. A reader will often describe the process in everyday terms. Translating that request into precise selection rules is part of the work, alongside choosing the right syntax and producing a table of results.

If a step is unclear, revisit LEFT JOIN, WHERE and GROUP BY or SQL’s logical query order. I would judge progress by how well you can defend the result and describe its limitations. Your vocabulary of SQL keywords will grow along the way. Make sure each new keyword corresponds to a transformation you can explain on the actual records in front of you.