Common Table Expressions (CTEs) Flashcards

Given WITH sales_cte AS (SELECT region, SUM(amount) total FROM sales GROUP BY region) SELECT * FROM sales_cte WHERE total > 1000, what does the CTE do?

Answer: Aggregates sales by region before filtering high totals

The CTE first groups and sums sales by region, then the outer query filters regions over 1000.

You need each employee shown with their manager's name from a self-referencing table. Which approach fits best?

Answer: A CTE (recursive or simple join) on the employees table

A CTE handling the self-reference cleanly resolves employee-to-manager relationships.

A CTE that ranks rows with ROW_NUMBER() is commonly used to:

Answer: Deduplicate by keeping only the first row per group

Numbering rows in a CTE then filtering rn = 1 is a standard deduplication pattern.

If a recursive CTE for a folder tree returns duplicate paths, the likely cause is:

Answer: A cycle in the data not handled by the recursion

Cycles in hierarchical data can cause repeated rows unless cycle detection is added.

What does this generate: WITH RECURSIVE nums AS (SELECT 1 n UNION ALL SELECT n+1 FROM nums WHERE n < 5)?

Answer: The numbers 1 through 5

It starts at 1 and increments until n reaches 5, producing 1,2,3,4,5.

To reuse a filtered subset of orders in two different joins within one query, a CTE helps by:

Answer: Defining the subset once and referencing it by name

Defining the subset as a named CTE lets you reference the same logic in multiple joins cleanly.

A query stacks three CTEs where each builds on the previous. This pattern is best described as:

Answer: A chained or pipelined transformation of data

Sequential CTEs that each transform the prior result form a readable, pipelined data flow.