HumachLearnLearn · explore · apply
Start learning
SQL for Analytics · Lesson 39

CTEs and Readable Queries

CTEs and Readable Queries is a core SQL analytical operation.

ConceptWorked examplePracticeKnowledge check
Textbook walkthrough

What CTEs and Readable Queries actually means

CTEs and Readable Queries is a core SQL analytical operation. SQL describes the result set you want from relational tables; reliable queries require clear row grain, join keys, grouping logic and an understanding of NULL behaviour.

CTEs and Readable Queries matters because SQL defines the grain and composition of analytical result sets. Filtering, joining, grouping and window logic can change row counts and denominators, so query structure is part of the analytical reasoning.

Deeper walkthrough

Read CTEs and Readable Queries as a mechanism, not a recipe

Treat this as a sequence of observable decisions rather than one opaque command. Stage 1: Start from the table(s) and define the row grain of the desired result. Stage 2: Filter rows with WHERE before aggregation when the condition concerns raw rows. Stage 3: Group only when multiple rows should collapse into one result per key. Final checkpoint: Audit counts and duplicates after each join or aggregation.

Mechanism

Follow the transformation

Start from the table(s) and define the row grain of the desired result.

Filter rows with WHERE before aggregation when the condition concerns raw rows.

Group only when multiple rows should collapse into one result per key.

Evidence

Know what would convince you

  • Build a 4–8 row toy table and calculate the expected result before running the SQL.
  • Check result row counts, distinct keys and NULLs against the stated grain.
Useful distinctionWHERE: Filters input rows.
How it works

Trace the mechanism step by step

  1. Start from the table(s) and define the row grain of the desired result.
  2. Filter rows with WHERE before aggregation when the condition concerns raw rows.
  3. Group only when multiple rows should collapse into one result per key.
  4. Join on keys whose cardinality you understand.
  5. Use window functions when you need group-aware calculations without collapsing rows.
  6. Audit counts and duplicates after each join or aggregation.
Worked demonstration

Make the concept concrete

Demonstration

SQL example

-- Step 1 — Define a named intermediate result (CTE) so the query can be read and checked in stages.
WITH order_totals AS (
  -- Step 2 — Choose the output fields/expressions that the query should return.
  SELECT customer_id, SUM(amount) AS total_amount
  -- Step 3 — Identify the source table or intermediate relation that supplies rows.
  FROM orders
  -- Step 4 — Filter individual rows before aggregation.
  WHERE status = 'complete'
  -- Step 5 — Create groups that aggregation functions will summarise.
  GROUP BY customer_id
)
-- Step 6 — Choose the output fields/expressions that the query should return.
SELECT customer_id, total_amount,
       RANK() OVER (ORDER BY total_amount DESC) AS revenue_rank
-- Step 7 — Identify the source table or intermediate relation that supplies rows.
FROM order_totals
-- Step 8 — Sort the final result into a deliberate presentation order.
ORDER BY revenue_rank;
Expected / illustrative result
One row per customer with complete-order revenue and a ranking that does not collapse the result further.
Interpret the result.

For CTEs and Readable Queries, trace at least one source row through the query and explain why it appears, disappears, duplicates or receives its calculated value in the result.

Distinctions & related ideas

Know what this is — and what it is not

WHEREFilters input rows.
GROUP BYCollapses rows into one result per group.
JOINCombines columns/rows from related tables.
WINDOW functionComputes across related rows while retaining row-level output.
CTENames an intermediate query to make multi-stage logic readable.
Use deliberately

When it is appropriate

Use CTEs and Readable Queries when the required answer can be expressed from relational rows while preserving a clearly defined result grain, key logic and NULL behaviour.

Boundary conditions

When to stop or reconsider

Stop and restate the query when the output grain, join cardinality, ordering requirement or NULL treatment is ambiguous; a query that runs can still duplicate or omit valid rows.

Common mistakes

Failure modes to recognise

  • Forgetting the intended output grain and accidentally duplicating or collapsing rows.
  • Using a join/group/window without validating keys, partitions, ordering or NULL behaviour.
  • Trusting a plausible result without reconciling counts and a few rows to the source tables.
Verification

How to check the result

  • Build a 4–8 row toy table and calculate the expected result before running the SQL.
  • Check result row counts, distinct keys and NULLs against the stated grain.
  • Trace at least one source row through filters, joins, groups or windows to its final output row/value.
Hands-on practice

Demonstrate understanding

Try this:

Build a tiny, inspectable example of CTEs and Readable Queries. First start from the table(s) and define the row grain of the desired result. Then filter rows with WHERE before aggregation when the condition concerns raw rows. Write the expected result before running it, and explain one condition that would make the result misleading or invalid.

Create tiny source tables where you can enumerate every row by hand. State the output grain first, then run the query and reconcile each result row.
Knowledge check

Check reasoning, not memorisation

Before trusting a result from CTEs and Readable Queries, which check provides the strongest evidence that you understand and applied it correctly?

Quick reference

Keep the important distinctions visible

Step 1Start from the table(s) and define the row grain of the desired result.
Step 2Filter rows with WHERE before aggregation when the condition concerns raw rows.
Step 3Group only when multiple rows should collapse into one result per key.
Step 4Join on keys whose cardinality you understand.
Lesson summary

What to remember

  • CTEs and Readable Queries is a core SQL analytical operation. SQL describes the result set you want from relational tables; reliable queries require clear row grain, join keys, grouping logic and an understanding of NULL behaviour.
  • Start from the table(s) and define the row grain of the desired result.
  • Forgetting the intended output grain and accidentally duplicating or collapsing rows.
  • Build a 4–8 row toy table and calculate the expected result before running the SQL.