Advanced SQL

Common Table Expressions

CTE

• CTE

• Recursive CTEs

• Activity Solutions

Common Table Expressions (CTEs)

A common table expression (CTE) names a query result that you can reference within a single SQL statement.

• Defined with WITH.
• Reusable in the statement
• Its scope ends when the statement ends.

A CTE helps organize query logic. It does not create a permanent table.

A CTE is a logical named result. SQLite can choose different execution strategies, so do not assume that every CTE stores a physical intermediate table. Source: https://www.sqlite.org/lang_with.html

The structure of an ordinary CTE

WITH large_invoices AS (
    SELECT InvoiceId, CustomerId, Total
    FROM Invoice
    WHERE Total >= 20
)
SELECT InvoiceId, CustomerId, Total
FROM large_invoices
ORDER BY Total DESC, InvoiceId;

1

Define a common table expression (CTE) named large_invoices.

2

Select each invoice’s ID, customer ID, and total.

3

Read records from the Invoice table.

4

Keep invoices with a total of at least 20.

5

Display the invoice ID, customer ID, and total in the final result.

6

Read the filtered records from large_invoices.

7

Sort by total from largest to smallest. For invoices with the same total, sort by invoice ID in ascending order.

CTE vs. Subquery

Feature	Subquery	CTE
Readability	Low (Nested)	High (Sequential)
Reusability	Can only be used once	Can be referenced multiple times
Debugging	Difficult	Easy (Test CTE separately)
Complexity	Hard to manage	Great for complex logic

When deciding which to use, remember: If you are nesting more than one level deep, or if you need to use the same logic twice in one query, reach for a CTE.

Aggregating inside a CTE

Question: Which billing countries have total sales above 200?

WITH country_sales AS (
    SELECT BillingCountry, SUM(Total) AS total_sales
    FROM Invoice
    GROUP BY BillingCountry
)
SELECT BillingCountry, ROUND(total_sales, 2) AS total_sales
FROM country_sales
WHERE total_sales > 200
ORDER BY total_sales DESC;

1

Define a common table expression (CTE) named country_sales.

2

Select each billing country and calculate its total sales by adding invoice totals.

3

Read records from the Invoice table.

4

Group invoices by billing country, producing one summary row per country.

5

Display each country and its total sales rounded to two decimal places.

6

Read the summarized records from country_sales.

7

Keep countries whose unrounded total sales exceed 200.

8

Sort by the displayed total sales from largest to smallest.

Multiple CTEs in one statement

WITH country_stats AS (
    SELECT BillingCountry, COUNT(*) AS invoice_count,
           SUM(Total) AS total_sales
    FROM Invoice
    GROUP BY BillingCountry
),
busy_countries AS (
    SELECT * FROM country_stats
    WHERE invoice_count >= 20
)
SELECT BillingCountry, invoice_count,
       ROUND(total_sales, 2) AS total_sales
FROM busy_countries
ORDER BY total_sales DESC
LIMIT 3;

1

Define the first CTE, country_stats, to summarize invoices by billing country.

2

Select each country and count its invoices.

3

Add invoice totals to calculate each country’s total sales.

4

Read records from the Invoice table.

5

Group invoices by country, producing one summary row per country.

6

Define the second CTE, busy_countries. A comma separates the two CTE definitions.

7

Select all columns from the first CTE.

8

Keep countries with at least 20 invoices.

9

Display each country’s name, invoice count, and total sales rounded to two decimal places.

10

Read the qualifying countries from the second CTE.

11

Sort by the rounded total sales from largest to smallest.

12

Return only the first three countries from the sorted results.

Use one WITH and commas between CTE definitions.

Joining a CTE to a table

Question: Which customers have spent the most?

WITH customer_spending AS (
    SELECT CustomerId, SUM(Total) AS total_spent
    FROM Invoice
    GROUP BY CustomerId
)
SELECT c.FirstName || ' ' || c.LastName AS customer,
       ROUND(s.total_spent, 2) AS total_spent
FROM Customer AS c
JOIN customer_spending AS s ON c.CustomerId = s.CustomerId
ORDER BY s.total_spent DESC, c.CustomerId
LIMIT 5;

1

Define a CTE named customer_spending to summarize spending by customer.

2

Select each customer’s ID and add their invoice totals to calculate total spending.

3

Read records from the Invoice table.

4

Group invoices by customer ID, producing one summary row per customer with invoices.

5

Combine the customer’s first and last names with a space, naming the output column customer.

6

Display total spending rounded to two decimal places.

7

Read customer information from Customer, using the alias c.

8

Use an inner join to match customers with their spending summaries by customer ID. The alias s refers to customer_spending.

9

Sort by unrounded total spending from largest to smallest. Break ties using customer ID in ascending order.

10

Return the first five customers from the sorted results.

The CTE summarizes invoices. The join adds customer names.

The output contains Helena Holý (49.62), Richard Cunningham (47.62), Luis Rojas (46.62), Ladislav Kovács (45.62), and Hugh O’Reilly (45.62), in that order after the CustomerId tie-breaker. The inner join includes customers with invoices. A LEFT JOIN plus COALESCE would be needed to include customers with zero invoices.

Activity 1: ordinary CTEs

A. Sales by billing country

Create a CTE with each billing country’s invoice count and total sales. Return countries with at least 20 invoices. Sort by total sales from largest to smallest.

B. Larger average invoices

Use two CTEs: first calculate invoice counts and average invoice totals by billing country, then keep countries with at least 20 invoices. Return those with an average invoice total above 5.50, sorted by average total from largest to smallest.

Recursive CTEs

• CTE

• Recursive CTEs

• Activity Solutions

What is recursion?

Recursion is a process that uses its own previous result to produce the next result. It needs a starting point and a condition that stops it.

Parts of a recursive CTE

Part	Purpose
WITH RECURSIVE	Introduces the CTE definitions
Anchor query	Produces the starting rows
UNION ALL	Combines generated rows, keeping duplicates (Adds Rows to the new table)
Recursive member	References the CTE to generate more rows
Termination rule	Prevents further expansion when finished
Final query	Selects and orders the result

The anchor and recursive member must return the same number of columns in corresponding positions.

Use consistent meanings and compatible values in corresponding columns. SQLite’s flexible type system should not be treated as a reason to mix unrelated values. SQLite accepts recursive CTEs even without the RECURSIVE keyword, but this lesson includes it for clarity. Source: https://www.sqlite.org/lang_with.html

First recursive example: counting

WITH RECURSIVE numbers(n) AS (
    SELECT 1                 -- Anchor Query
    UNION ALL
    SELECT n + 1             -- Recursive member
    FROM numbers             
    WHERE n < 5              -- Stops expansion at 5
)
SELECT n
FROM numbers
ORDER BY n;

Result: 1, 2, 3, 4, 5.

numbers(n) explicitly names the CTE’s output column.

Tracing the recursion

Current row	Does n < 5 hold?	New row
Anchor	Not evaluated	1
1	Yes	2
2	Yes	3
3	Yes	4
4	Yes	5
5	No	None

The condition checks the current n before producing n + 1.

Predict: What would WHERE n <= 5 produce?

Answer: 1 through 6. In SQLite’s evaluation model, the anchor fills a work queue, then SQLite processes queued rows and adds generated rows. This table traces a one-row-at-a-time sequence, not a claim about the evaluation order of every recursive query. Source: https://www.sqlite.org/lang_with.html

The Chinook employee hierarchy

Level below Andrew	Employees	Direct manager
0	Andrew Adams	None
1	Nancy Edwards, Michael Mitchell	Andrew Adams
2	Jane Peacock, Margaret Park, Steve Johnson	Nancy Edwards
2	Robert King, Laura Callahan	Michael Mitchell

A direct report is one link below a manager.

A descendant can be any number of links below that manager.

depth will count links from the chosen starting employee.

Let’s Look at Employee Table

SELECT *
FROM Employee;

All employees below Andrew

WITH RECURSIVE team(EmployeeId, employee_name, depth) AS (
    SELECT EmployeeId, FirstName || ' ' || LastName, 0
    FROM Employee
    WHERE EmployeeId = 1
    UNION ALL
    SELECT e.EmployeeId, e.FirstName || ' ' || e.LastName,
           t.depth + 1
    FROM Employee AS e
    JOIN team AS t ON e.ReportsTo = t.EmployeeId
)
SELECT employee_name, depth
FROM team
WHERE depth > 0
ORDER BY depth, employee_name;

1

Define a recursive common table expression named team with three columns: employee ID, employee name, and depth.

2

Begin the anchor query: select the starting employee’s ID, combine their first and last names using ||, and assign depth 0.

3

Read the starting employee’s information from the Employee table.

4

Choose employee 1—Andrew Adams in Chinook—as the starting point.

5

Combine the anchor row with rows generated by the recursive query, retaining duplicates.

6

Begin the recursive query: select each matching employee’s ID and full name.

7

Set the employee’s depth to their manager’s depth plus one.

8

Read employees from Employee, using e as a short alias.

9

Match employees to their managers in team. Each match finds a direct report; repeating this step finds employees farther down the hierarchy.

10

Close the definition of the recursive CTE.

11

Choose the employee name and depth for the final output.

12

Read from the completed team result.

13

Exclude the starting employee at depth 0, leaving only their descendants.

14

Sort by depth first, then alphabetically by employee name within each depth.

This query assumes Chinook’s acyclic hierarchy. The join follows a manager’s EmployeeId to employees whose ReportsTo matches it. Expansion stops when no matching reports remain. Later slides add protection for cyclic data.

Hierarchy query results

employee_name	depth
Michael Mitchell	1
Nancy Edwards	1
Jane Peacock	2
Laura Callahan	2
Margaret Park	2
Robert King	2
Steve Johnson	2

Do not assume a recursive query returns rows in a particular order without a final ORDER BY.

Laura’s chain of managers

WITH RECURSIVE chain AS (
    SELECT EmployeeId, FirstName, LastName, ReportsTo,
           0 AS depth
    FROM Employee WHERE EmployeeId = 8
    UNION ALL
    SELECT e.EmployeeId, e.FirstName, e.LastName, e.ReportsTo,
           c.depth + 1
    FROM Employee AS e
    JOIN chain AS c ON e.EmployeeId = c.ReportsTo
)
SELECT FirstName || ' ' || LastName AS manager, depth
FROM chain
WHERE depth > 0
ORDER BY depth;

1

Define a recursive common table expression (CTE) named chain to trace an employee’s managers.

2

Begin the anchor query by selecting the starting employee’s ID, name, and direct manager’s ID (ReportsTo).

3

Assign the starting employee a depth of 0.

4

Choose employee 8 as the starting point.

5

Combine the starting row with rows produced by the recursive query, retaining duplicates.

6

Begin the recursive query by selecting the matched manager’s information, including their own manager’s ID.

7

Increase depth by one for each step up the management hierarchy.

8

Read potential managers from Employee, using the alias e.

9

Find the manager of the current person in chain: match the manager’s EmployeeId to that person’s ReportsTo. This moves up the hierarchy.

10

Close the CTE definition. Recursion stops when no further manager matches.

11

Combine each manager’s first and last names with a space, label the column manager, and include depth.

12

Read the accumulated rows from chain.

13

Exclude the starting employee, keeping only their managers.

14

Sort from the nearest manager (depth = 1) upward through the hierarchy.

Andrew’s ReportsTo is NULL, so the recursive join finds no next manager. Laura remains in the CTE at depth 0 but does not appear in the displayed result.

Termination and depth limits

A recursive query ends when no unprocessed rows remain.

For these examples, expansion stops when:

• A numeric condition fails, such as n < 5.
• A join finds no more children or managers.
• An explicit depth condition prevents another step.

UNION ALL and UNION

Operator	Treatment of generated rows
UNION ALL	Keeps repeated rows
UNION	Discards duplicate complete rows

Duplicate elimination adds work. Select the operator based on the intended result and termination logic. Replacing UNION ALL with UNION does not automatically fix every cyclic traversal. Source: https://www.sqlite.org/lang_with.html

Activity 2: recursive CTEs

A. Nancy’s reports

Start with Nancy Edwards (EmployeeId = 2). Return her descendants and their depths, excluding Nancy. Sort by depth and name.

B. Jane’s managers

Start with Jane Peacock (EmployeeId = 3) and move upward through her managers.

C. Andrew’s report counts

Start with Andrew Adams (EmployeeId = 1). Count his descendants at each depth. Exclude depth 0 and sort by depth.

Activity Solutions

• CTE

• Recursive CTEs

• Activity Solutions

Activity 1A

WITH country_sales AS (
    SELECT BillingCountry,
           COUNT(*) AS invoice_count,
           SUM(Total) AS total_sales
    FROM Invoice
    GROUP BY BillingCountry
)
SELECT BillingCountry, invoice_count, total_sales
FROM country_sales
WHERE invoice_count >= 20
ORDER BY total_sales DESC;

1

Define a CTE named country_sales to summarize invoices by billing country.

2

Select the billing country.

3

Count the invoices in each country.

4

Add the invoice totals to calculate each country’s total sales.

5

Read the records from the Invoice table.

6

Group invoices by billing country, producing one summary row per country.

7

Display the country, invoice count, and total sales.

8

Read the summarized results from the CTE.

9

Keep countries with at least 20 invoices.

10

Sort total sales from largest to smallest.

Activity 1B

WITH country_summary AS (
    SELECT BillingCountry,
           COUNT(*) AS invoice_count,
           AVG(Total) AS average_total
    FROM Invoice
    GROUP BY BillingCountry
),
eligible_countries AS (
    SELECT BillingCountry, invoice_count, average_total
    FROM country_summary
    WHERE invoice_count >= 20
)
SELECT BillingCountry, invoice_count, average_total
FROM eligible_countries
WHERE average_total > 5.50
ORDER BY average_total DESC;

1

Define the first CTE, country_summary, to summarize invoices by billing country.

2

Count the invoices for each country.

3

Calculate each country’s average invoice total.

4

Group invoices by billing country, producing one summary row per country.

5

Define the second CTE, eligible_countries. A comma separates the two CTE definitions.

6

Read the summarized results from the first CTE.

7

Keep countries with at least 20 invoices.

8

Display the country, invoice count, and average invoice total from the second CTE.

9

Keep only countries whose average invoice total is strictly greater than 5.50.

10

Sort by average invoice total from largest to smallest.

Activity 2A

WITH RECURSIVE team AS (
    SELECT EmployeeId, FirstName || ' ' || LastName AS employee_name,
           0 AS depth
    FROM Employee
    WHERE EmployeeId = 2
    UNION ALL
    SELECT e.EmployeeId, e.FirstName || ' ' || e.LastName,
           t.depth + 1
    FROM Employee AS e
    JOIN team AS t ON e.ReportsTo = t.EmployeeId
)
SELECT employee_name, depth
FROM team
WHERE depth > 0
ORDER BY depth, employee_name;

1

Define a recursive CTE named team.

2

Select the starting employee and assign depth 0.

3

Choose Nancy Edwards as the starting employee.

4

Combine the anchor row with rows generated by recursion, retaining duplicates.

5

Assign each direct report a depth one greater than their manager’s.

6

Move down the hierarchy: find employees whose ReportsTo matches the current manager’s ID.

7

Exclude Nancy from the final result.

8

Sort by distance below Nancy, then alphabetically by name.

Activity 2B

WITH RECURSIVE chain AS (
    SELECT EmployeeId, FirstName, LastName, ReportsTo,
           0 AS depth
    FROM Employee
    WHERE EmployeeId = 3
    UNION ALL
    SELECT e.EmployeeId, e.FirstName, e.LastName, e.ReportsTo,
           c.depth + 1
    FROM Employee AS e
    JOIN chain AS c ON e.EmployeeId = c.ReportsTo
)
SELECT FirstName || ' ' || LastName AS manager, depth
FROM chain
WHERE depth > 0
ORDER BY depth;

1

Keep the employee’s manager ID (ReportsTo) so the recursive query can find the next manager. Start at depth 0.

2

Choose Jane Peacock as the starting employee.

3

Increase depth by one for each step upward.

4

Move up the hierarchy by matching the current person’s manager ID to an employee ID.

5

Display each manager’s full name and depth.

6

Exclude Jane, leaving only her managers.

7

Show the nearest manager first.

Activity 2C

WITH RECURSIVE descendants AS (
    SELECT EmployeeId, 0 AS depth
    FROM Employee
    WHERE EmployeeId = 1
    UNION ALL
    SELECT e.EmployeeId, d.depth + 1
    FROM Employee AS e
    JOIN descendants AS d ON e.ReportsTo = d.EmployeeId
)
SELECT depth, COUNT(*) AS descendant_count
FROM descendants
WHERE depth > 0
GROUP BY depth
ORDER BY depth;

1

Define a recursive CTE named descendants.

2

Select the starting employee and assign depth 0.

3

Choose Andrew Adams as the starting employee.

4

Combine the anchor row with rows generated by the recursive query.

5

Select each newly found employee and assign their manager’s depth plus one.

6

Move downward through the hierarchy by finding employees who report to the current person.

7

Display each depth and count the descendants at that depth.

8

Exclude Andrew, whose depth is 0.

9

Group the remaining employees by depth so each level has its own count.

10

Sort by depth in ascending order.