SQL Case File: 003 | Correct Code Framework

Case Brief

Aggregation Investigation

Finance sees thousands of source orders but only a few result rows. The query did not lose rows; it changed the output grain.

This is not a syntax tour. The case closes only when you can predict, reason, prove, write, debug, explain, and defend the SQL.

Learning Contract

What Mastery Means Here

Predict
Given data plus SQL, determine the output before execution.

Construct
Given a requirement and schema, build the SQL progressively.

Defend
Explain correctness, traps, edge cases, and likely SQL Server work.

Evidence

Evidence Locker

Orders include repeated customers, cancelled rows, and totals that must be grouped by customer.

Business requirement	The result contains one row per group, and HAVING filters groups after aggregates exist.
Relevant constructs	COUNT, SUM, AVG, MIN, MAX, GROUP BY, WHERE, HAVING
Reasoning engine focus	Use the engine to define what one output row means, filter source rows first, then measure the aggregate work left for SQL Server.
Distractor to watch	Filtering groups in WHERE when the condition belongs in HAVING.

SQL Reasoning Engine

Questions To Ask Before Writing SQL

The infographic is the reusable reasoning engine for this case: prove the answer, then ask how much work SQL Server must do to retrieve it.

1. What data do I actually need?	Return only required columns; avoid SELECT * when extra data causes reads, memory, or lookups.
2. Which tables contain the data?	Identify the correct tables first because they define FROM and JOIN.
3. How are the tables related?	Use primary and foreign keys as evidence; join on relationships, not guesses.
4. What rows do I need?	Use selective WHERE logic so fewer rows flow through the plan.
5. Are the filter columns indexed?	Check WHERE and JOIN columns for useful access paths.
6. Is the predicate sargable?	Prefer predicates SQL Server can seek, such as date ranges instead of functions on columns.
7. Seek, Scan, or Lookup?	Distinguish jumping to keys, reading many pages, and fetching extra columns after an index match.
8. How many rows do I expect?	Cardinality changes the best plan; few rows and many rows often want different strategies.
9. Will joins, sorts, or aggregates add work?	Account for CPU, memory, and tempdb work introduced by joins, ORDER BY, GROUP BY, SUM, and COUNT.
10. Are NULLs or transactions part of the problem?	Use NULL-aware predicates and keep long transactions from increasing locking or blocking.
11. Can the database do less work?	Return the correct result while reducing unnecessary reads, lookups, sorts, and row flow.

Aggregation Engine Focus

Define the group grain before choosing aggregate functions.
Separate WHERE row filters from HAVING group filters.
Estimate how many rows flow into the aggregate.

Goal: Return the correct result while making SQL Server do less work.

CCF Reasoning

Spaces Before Syntax

Base Component
Group

Answer Space
The result contains one row per group, and HAVING filters groups after aggregates exist.

Problem Space
Tables, rows, columns, keys, relationships, ordering, NULLs, duplicates, and constraints.

Decision Space
Scanner/access pattern, filter, relationship, recorder, invariant, and finalizer.

Invariant: the final result must satisfy the business contract after every relational transformation.

Predict

Predict Before Execution

Before reading the final SQL, decide which rows survive, which rows disappear, whether rows multiply or collapse, what one output row represents, and whether ordering is deterministic.

Reveal Prediction Ruling

The result contains one row per group, and HAVING filters groups after aggregates exist.

Build

Build The Query Progressively

Ask what result is needed and what one output row represents.
Find where the evidence lives: tables, columns, keys, and relationships.
Filter the rows with selective, sargable predicates whenever the requirement allows it.
Check whether SQL Server can seek, whether it must scan, and whether lookups are being invited.
Estimate row flow before adding joins, sorts, aggregates, windows, or transaction scope.
Apply the final condition and projection only after the Answer Space is stable.

-- SQL Case File 003 pattern
-- Rows become groups; groups become aggregate evidence.
-- Build from evidence, not from memorized syntax.

Prove

Prove The Result

Compare the input evidence, intermediate state, and final output against the Answer Space. The proof fails if the query returns the right-looking shape for the wrong reason.

SQL rule: Rows become groups; groups become aggregate evidence.

Wrong Turn

The Query That Runs But Lies

Debugging Challenge

Filtering groups in WHERE when the condition belongs in HAVING.

Engine checkpoint: Re-run the checklist and find the first question the wrong query cannot answer honestly.

Compile?
Often yes.

Run?
Often yes.

Correct?
Only if the Answer Space survives.

Efficient?
Only if SQL Server avoids unnecessary work.

SQL Server Lens

Logical SQL vs Physical SQL Server

Logical SQL
What result is SQL required to produce? Which rows, columns, groups, partitions, or relationships prove it?

Physical SQL Server
How might SQL Server seek, scan, lookup, sort, group, join, lock, or compute that result?

Seek
Jump directly to matching keys when predicates and indexes allow it.

Scan
Read many rows or pages when SQL Server has no narrower path or the row count justifies it.

Lookup
Fetch extra columns after a nonclustered index match, which can repeat work at scale.

Do not assume one execution plan without indexes, statistics, row counts, predicates, and expected cardinality.

Interview Room

Progressive Interview Simulation

Foundational: Define Aggregation in plain language.
Predict: Walk through a tiny result without running SQL.
Edge case: Explain how NULL, duplicates, ordering, cardinality, or concurrency can change the result.
Wrong turn: Repair this issue: Filtering groups in WHERE when the condition belongs in HAVING.
Scale: Discuss what changes when the largest table has 100 million rows, then name the likely seek, scan, lookup, sort, join, aggregate, lock, or tempdb pressure.
Defense: Explain the tradeoff of one alternative solution.

Case Challenge

Unguided Variation

Group order rows manually and compare COUNT(*), COUNT(column), SUM, and HAVING.

Change one business rule, one edge case, and one scale constraint. Predict how each change affects the Answer Space before changing the SQL.

Mastery Gate

Before You Close The Case

Predict
Determine output from tables and SQL.

Construct
Write SQL from the requirement.

Explain
State why the query works.

Debug
Repair the plausible wrong turn.

Defend
Answer follow-ups under interview pressure.

Case Closed

SQL Case File: 003

Case Closed

Aggregation

Base Component	Group
Answer Space	The result contains one row per group, and HAVING filters groups after aggregates exist.
Problem Space	Relevant data, constraints, relationships, ordering, NULLs, duplicates, and scale.
Decision Space	Choose the operation that lawfully transforms evidence into the answer.
SQL Reasoning Engine	Use the engine to define what one output row means, filter source rows first, then measure the aggregate work left for SQL Server.
SQL Pattern	Rows become groups; groups become aggregate evidence.
Common Trap	Filtering groups in WHERE when the condition belongs in HAVING.
Hands-On Lab	Group order rows manually and compare COUNT(*), COUNT(column), SUM, and HAVING.
Interview Principle	Explain the result, then the query, then the likely SQL Server work.

If you remember only one thing from this Case File: before writing SQL, ask what data is needed, how to filter it, whether the columns are indexed, whether the predicate is sargable, and how much work SQL Server has to do.