SQL Case File: 005 | Correct Code Framework

Case Brief

Window Functions Investigation

Customer Success needs the most recent completed order for every customer. One draft returns a single company-wide order; another returns every order.

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 multiple completed orders per customer, a cancelled order, and a tie on OrderDate.

Business requirement	One deterministic winning row survives per partition when rank 1 is filtered.
Relevant constructs	OVER, PARTITION BY, ORDER BY, ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER
Reasoning engine focus	Use the engine to pick the needed columns, filter completed orders, check the partition/order access path, and estimate sort or lookup work.
Distractor to watch	Incorrect PARTITION BY returns one latest row for the whole table.

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.

Window Functions Engine Focus

Filter to eligible rows before ranking partitions.
Declare the partition key and deterministic order.
Check whether the index order can reduce sort and lookup work.

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

CCF Reasoning

Spaces Before Syntax

Base Component
Partition

Answer Space
One deterministic winning row survives per partition when rank 1 is filtered.

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

One deterministic winning row survives per partition when rank 1 is filtered.

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 005 pattern
-- Use ROW_NUMBER() OVER with PARTITION BY and ORDER BY to rank rows inside each analytical frame.
-- 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: Use ROW_NUMBER() OVER with PARTITION BY and ORDER BY to rank rows inside each analytical frame.

Wrong Turn

The Query That Runs But Lies

Debugging Challenge

Incorrect PARTITION BY returns one latest row for the whole table.

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 Window Functions 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: Incorrect PARTITION BY returns one latest row for the whole table.
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

Rank orders per customer, predict ties, and defend ROW_NUMBER versus RANK.

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: 005

Case Closed

Window Functions

Base Component	Partition
Answer Space	One deterministic winning row survives per partition when rank 1 is filtered.
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 pick the needed columns, filter completed orders, check the partition/order access path, and estimate sort or lookup work.
SQL Pattern	Use ROW_NUMBER() OVER with PARTITION BY and ORDER BY to rank rows inside each analytical frame.
Common Trap	Incorrect PARTITION BY returns one latest row for the whole table.
Hands-On Lab	Rank orders per customer, predict ties, and defend ROW_NUMBER versus RANK.
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.