CTEs, Subqueries & Query Structure

01 · Overview

Build a query in steps

A CTE is one named step. Many small steps are easier to read than one big query.

⏱ 8 min · Topic 1 of 13

A long query that does everything at once is hard to read. You can split it into small steps and give each step a name.

A CTE (common table expression) is one named step. You write it with WITH. The main query then uses the name like a table.

This query names the paid orders, then reads from that name:

Name the paid ordersworked example

SQL

Input data

orders6 rows

order_id	customer_id	status	amount	order_date
101	1	paid	40.00	2026-01-03
102	1	paid	25.00	2026-01-10
103	2	refunded	60.00	2026-01-11
104	3	paid	15.50	2026-01-15
105	3	pending	NULL	2026-01-20
106	4	paid	80.00	2026-01-21

WITH paid_orders AS (
  SELECT order_id, customer_id, amount
  FROM orders
  WHERE status = 'paid'
)
SELECT order_id, customer_id, amount
FROM paid_orders
ORDER BY order_id;

Result · 4 rows

order_id	customer_id	amount
101	1	40.00
102	1	25.00
104	3	15.50
106	4	80.00

The WITH part keeps four paid orders. The last SELECT only reads them.

A CTE lives only while this one query runs. The database does not save it.

What you will learn

You met subqueries in chapter 2. Here you use them with CTEs. You build bigger queries that you can still check by hand.

In this chapter, you learn to:

Chain CTEs, and check one step at a time.
Use a subquery that gives one value.
Use a subquery that looks at the row it is checking.
Put a subquery in FROM.
Clean a column once, and walk a tree with a recursive CTE.
Keep a query easy to read.

The examples use the same shop as chapters 1 to 3. The recursive section adds one small table, with its own rows.

02 · CTEs

Chain CTEs, step by step

Each CTE does one job. You read the steps from top to bottom.

⏱ 8 min · Topic 2 of 13

One WITH can hold many CTEs. You separate them with commas. A later step can use any step above it.

Here is a question: how much has each customer paid?

Step 1 keeps the paid orders. Step 2 adds up the amounts for each customer. The last SELECT adds the name:

Paid revenue for each customerworked example

SQL

Input data

customers5 rows

customer_id	name	country
1	Asha	IN
2	Ben	GB
3	Carla	US
4	Dev	NULL
5	Omar	IN

orders6 rows

order_id	customer_id	status	amount	order_date
101	1	paid	40.00	2026-01-03
102	1	paid	25.00	2026-01-10
103	2	refunded	60.00	2026-01-11
104	3	paid	15.50	2026-01-15
105	3	pending	NULL	2026-01-20
106	4	paid	80.00	2026-01-21

WITH paid_orders AS (
  SELECT order_id, customer_id, amount
  FROM orders
  WHERE status = 'paid'
),
customer_revenue AS (
  SELECT customer_id, SUM(amount) AS revenue
  FROM paid_orders
  GROUP BY customer_id
)
SELECT c.name, r.revenue
FROM customer_revenue AS r
JOIN customers AS c ON c.customer_id = r.customer_id
ORDER BY r.revenue DESC;

Result · 3 rows

name	revenue
Dev	80.00
Asha	65.00
Carla	15.50

Ben has only a refunded order. Omar has no orders. Neither is in the result.

Step 1 gives four rows. Step 2 turns them into one row for each customer who paid. This is the middle result:

Step 2: customer_revenue

customer_id	revenue
1	65.00
3	15.50
4	80.00

Customer 1 has two paid orders: 40.00 + 25.00. The last SELECT joins this result to customers to get the names.

CTEs and speed

A CTE helps you read a query. It does not make the query faster.

Some databases run a CTE as part of the main query. Some store its rows first.

You can see which one happens in the query plan. You will meet query plans in a later chapter.

03 · Checking

Check one step at a time

Point a small check at one step before you build the next step.

⏱ 8 min · Topic 3 of 13

When a step has a name, you can test it alone. Replace the last SELECT with a small check. The steps above it stay the same.

The orders table has 4 paid orders, so paid_orders should have 4 rows. Count them:

Count the rows in one stepworked example

SQL

Input data

orders6 rows

order_id	customer_id	status	amount	order_date
101	1	paid	40.00	2026-01-03
102	1	paid	25.00	2026-01-10
103	2	refunded	60.00	2026-01-11
104	3	paid	15.50	2026-01-15
105	3	pending	NULL	2026-01-20
106	4	paid	80.00	2026-01-21

WITH paid_orders AS (
  SELECT order_id, customer_id, amount
  FROM orders
  WHERE status = 'paid'
)
SELECT COUNT(*) AS rows_in_step
FROM paid_orders;

Result · 1 row

rows_in_step
4

The count is 4, as expected. The step is safe to build on.

Check that no row repeats

Each order should appear once in this step. Compare the number of rows with the number of different order_id values:

Does any order repeat?worked example

SQL

Input data

orders6 rows

order_id	customer_id	status	amount	order_date
101	1	paid	40.00	2026-01-03
102	1	paid	25.00	2026-01-10
103	2	refunded	60.00	2026-01-11
104	3	paid	15.50	2026-01-15
105	3	pending	NULL	2026-01-20
106	4	paid	80.00	2026-01-21

WITH paid_orders AS (
  SELECT order_id, customer_id, amount
  FROM orders
  WHERE status = 'paid'
)
SELECT COUNT(*)                 AS rows_in_step,
       COUNT(DISTINCT order_id) AS different_orders
FROM paid_orders;

Result · 1 row

rows_in_step	different_orders
4	4

Both numbers are 4, so no order repeats.

Suppose a join to order_items sat inside this step. Orders 101 and 106 have two items each. The step would hold these six rows:

paid_orders after a join to order_items

order_id
101
101
102
104
106
106

You would see 6 rows but only 4 different orders. Two numbers that differ tell you rows repeat.

Watch out

Do not write all the steps first and check only the last number. Then a bug can hide in any step. Check each step right after you write it.

04 · Subqueries

A subquery that gives one value

The subquery works out one number. Every row is compared with that number.

⏱ 8 min · Topic 4 of 13

A subquery is a query inside round brackets ( ). SQL runs it first and uses its result like a small table. A scalar subquery gives exactly one value: one row and one column.

Use it to compare every row with one number from the whole table. Here, the number is the average order amount. Run the subquery alone first:

The average order amountworked example

SQL

Input data

orders6 rows

order_id	customer_id	status	amount	order_date
101	1	paid	40.00	2026-01-03
102	1	paid	25.00	2026-01-10
103	2	refunded	60.00	2026-01-11
104	3	paid	15.50	2026-01-15
105	3	pending	NULL	2026-01-20
106	4	paid	80.00	2026-01-21

SELECT ROUND(AVG(amount), 2) AS average_amount
FROM orders;

Result · 1 row

average_amount
44.10

AVG skips the NULL in order 105. It uses 5 amounts: 220.50 ÷ 5 = 44.10.

Now put that query in brackets inside WHERE. You get the orders that are bigger than the average:

Orders above the averageworked example

SQL

SELECT order_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders)
ORDER BY amount DESC;

Result · 2 rows

order_id	amount
106	80.00
103	60.00

Orders 101 (40.00), 102 (25.00) and 104 (15.50) are below 44.10. Order 105 has a NULL amount, and a test with NULL is never true.

The database runs the subquery once. Then it compares every order with that one number.

It must give one value

If the subquery gives many rows, the comparison has no single number to use. This query fails:

This failsworked example

SQL

SELECT order_id
FROM orders
WHERE amount > (SELECT amount FROM orders);
-- ERROR: more than one row returned by a subquery
--        used as an expression

The subquery gives six rows, not one. Use an aggregate such as AVG or MAX to get one value.

05 · Subqueries

A subquery that checks each row

A correlated subquery uses a column from the outer row, so its answer changes for each row.

⏱ 9 min · Topic 5 of 13

A correlated subquery uses a column from the outer query. The outer query is the main query that holds the subquery. It reads one row at a time, and the subquery runs with that row’s values.

The EXISTS subquery from chapter 2 is correlated too. Here is a new question: which orders are bigger than the average of the same customer’s orders?

The average is different for each customer. So the subquery must know the customer of the order it checks:

Orders above their own customer’s averageworked example

SQL

Input data

orders6 rows

order_id	customer_id	status	amount	order_date
101	1	paid	40.00	2026-01-03
102	1	paid	25.00	2026-01-10
103	2	refunded	60.00	2026-01-11
104	3	paid	15.50	2026-01-15
105	3	pending	NULL	2026-01-20
106	4	paid	80.00	2026-01-21

SELECT o.order_id, o.customer_id, o.amount
FROM orders AS o
WHERE o.amount > (
  SELECT AVG(o2.amount)
  FROM orders AS o2
  WHERE o2.customer_id = o.customer_id
)
ORDER BY o.order_id;

Result · 1 row

order_id	customer_id	amount
101	1	40.00

Look at the line o2.customer_id = o.customer_id. The o belongs to the outer query. That link is the correlation.

This table shows what the subquery gives for each order:

The customer average for each order

order_id	customer_id	amount	customer average	kept?
101	1	40.00	32.50	yes
102	1	25.00	32.50	no
103	2	60.00	60.00	no
104	3	15.50	15.50	no
105	3	NULL	15.50	no
106	4	80.00	80.00	no

Orders 103, 104 and 106 are the only orders with an amount for their customer. Each equals its own average, and equal is not bigger.

Think of the subquery as running again for each outer row. The database may find a faster way, but the result is the same.

The same answer with a CTE

A CTE can give the same result. It works out each customer’s average once, then joins it to the orders:

The same query with a CTEworked example

SQL

WITH customer_avg AS (
  SELECT customer_id, AVG(amount) AS avg_amount
  FROM orders
  GROUP BY customer_id
)
SELECT o.order_id, o.customer_id, o.amount
FROM orders AS o
JOIN customer_avg AS a ON a.customer_id = o.customer_id
WHERE o.amount > a.avg_amount
ORDER BY o.order_id;

Result · 1 row

order_id	customer_id	amount
101	1	40.00

The CTE gives the average its own name. You can run it alone and see one row for each customer.

Interview question

“What is a correlated subquery?” Say: the inner query uses a column of the outer query. So it depends on the current outer row. Then say you can often rewrite it as a join to a grouped CTE.

06 · Subqueries

A subquery in FROM

A derived table is a subquery that the outer query reads like a table.

⏱ 8 min · Topic 6 of 13

A derived table is a subquery in the FROM clause. The outer query reads it like a table. You give it a name, called an alias.

Chapter 2 showed a join that repeats rows. Orders 101 and 106 have two items each, so their amounts were added twice. The total was 340.50, not 220.50.

This is a second look at that fix, now with its name. Make one row for each order before you join:

A correct total after a joinworked example

SQL

Input data

orders6 rows

order_id	customer_id	status	amount	order_date
101	1	paid	40.00	2026-01-03
102	1	paid	25.00	2026-01-10
103	2	refunded	60.00	2026-01-11
104	3	paid	15.50	2026-01-15
105	3	pending	NULL	2026-01-20
106	4	paid	80.00	2026-01-21

order_items7 rows

order_id	product	quantity
101	Pen	2
101	Notebook	1
102	Bag	1
103	Lamp	1
104	Pen	3
106	Chair	1
106	Lamp	1

SELECT SUM(o.amount) AS total,
       SUM(i.units)  AS units
FROM orders AS o
JOIN (
  SELECT order_id, SUM(quantity) AS units
  FROM order_items
  GROUP BY order_id
) AS i ON i.order_id = o.order_id;

Result · 1 row

total	units
220.50	10

The total is the true 220.50. The 10 units are 2 + 1 + 1 + 1 + 3 + 1 + 1.

The derived table i has one row for each order. Each order now appears once in the join:

The derived table i

order_id	units
101	3
102	1
103	1
104	3
106	2

Order 105 has no items, so the join drops it. Its amount is NULL, so the total does not change.

An aggregate of an aggregate

A derived table also helps when you need a number from numbers you already added up. First add up the amounts for each customer. Then average those totals:

The average customer totalworked example

SQL

SELECT ROUND(AVG(revenue), 2) AS avg_customer_revenue
FROM (
  SELECT customer_id, SUM(amount) AS revenue
  FROM orders
  GROUP BY customer_id
) AS customer_totals;

Result · 1 row

avg_customer_revenue
55.13

The totals count all orders, including refunded ones, to keep the example small. They are 65.00, 60.00, 15.50 and 80.00. Their average is 220.50 ÷ 4 = 55.125.

Watch out

Always give a derived table an alias. Some databases give an error without one. A good name also tells the reader what one row means.

07 · Structure

Clean once, use everywhere

Write each cleaning rule one time, in one step.

⏱ 6 min · Topic 7 of 13

Sometimes the same fix appears many times in one query. Every copy is a chance for a mistake. Dev has no country, so we use COALESCE (from chapter 1) to call it “unknown”.

This query counts orders for each country, and leaves out GB. The same COALESCE is written three times:

The same fix three timesworked example

SQL

Input data

customers5 rows

customer_id	name	country
1	Asha	IN
2	Ben	GB
3	Carla	US
4	Dev	NULL
5	Omar	IN

orders6 rows

order_id	customer_id	status	amount	order_date
101	1	paid	40.00	2026-01-03
102	1	paid	25.00	2026-01-10
103	2	refunded	60.00	2026-01-11
104	3	paid	15.50	2026-01-15
105	3	pending	NULL	2026-01-20
106	4	paid	80.00	2026-01-21

SELECT COALESCE(c.country, 'unknown') AS country,
       COUNT(o.order_id)               AS orders,
       SUM(o.amount)                   AS revenue
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE COALESCE(c.country, 'unknown') <> 'GB'
GROUP BY COALESCE(c.country, 'unknown')
ORDER BY MIN(c.customer_id);

Result · 3 rows

country	orders	revenue
IN	2	65.00
US	2	15.50
unknown	1	80.00

The answer is right. But look at the three COALESCE copies: in SELECT, in WHERE and in GROUP BY.

Say you change “unknown” to “not set” in the SELECT and forget the other two. The database stops with an error. With one copy, you edit one line.

The fix: clean the column once, in its own step. Later steps use the clean name:

Clean once in a CTEworked example

SQL

WITH clean_customers AS (
  SELECT customer_id,
         COALESCE(country, 'unknown') AS country
  FROM customers
)
SELECT c.country,
       COUNT(o.order_id) AS orders,
       SUM(o.amount)     AS revenue
FROM clean_customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE c.country <> 'GB'
GROUP BY c.country
ORDER BY MIN(c.customer_id);

Result · 3 rows

country	orders	revenue
IN	2	65.00
US	2	15.50
unknown	1	80.00

The same rows as before. The rule “unknown” now lives in one line. To change it, you edit one place.

Remember

Do you see the same COALESCE or CASE three times? Move it into a cleanup step at the top.

08 · Recursion

Walk a tree with recursion

where the walk starts. Recursive step: one step along the tree. The walk stops when a step finds nothing.

⏱ 12 min · Topic 8 of 13

Some data is a tree. Each row points to its boss in the same table. How many levels the tree has is not fixed, so a fixed number of joins cannot walk it.

This section adds one new table, staff:

staff

staff_id	name	manager_id
1	Mira	NULL
2	Joe	1
3	Lena	1
4	Sam	2
5	Tia	2
6	Uma	4
7	Raj	3

The manager_id column holds the staff_id of the boss. NULL means no boss, so Mira is the owner.

Look at the picture: each line goes from a boss to the people who report to that boss.

A direct report is a person who reports straight to a boss. Sam and Tia are direct reports of Joe. Uma reports to Sam, so she is a report of a report.

A recursive CTE is a CTE that uses its own name. It has two parts joined by UNION ALL, which stacks the rows of both parts.

The first part is the anchor. It gives the first rows.

The second part is the recursive step. It finds the next rows from the rows found so far.

Find everyone under Joe (staff_id 2), at any depth.

The depth column counts the levels below Joe. 1 means a direct report. 2 means a report of a report:

Everyone under Joeworked example

SQL

Input data

staff7 rows

staff_id	name	manager_id
1	Mira	NULL
2	Joe	1
3	Lena	1
4	Sam	2
5	Tia	2
6	Uma	4
7	Raj	3

WITH RECURSIVE team AS (
  -- anchor: Joe's direct reports
  SELECT staff_id, name, 1 AS depth
  FROM staff
  WHERE manager_id = 2

  UNION ALL

  -- recursive step: the reports of the people found so far
  SELECT s.staff_id, s.name, t.depth + 1
  FROM staff AS s
  JOIN team AS t ON s.manager_id = t.staff_id
)
SELECT staff_id, name, depth
FROM team
ORDER BY depth, staff_id;

Result · 3 rows

staff_id	name	depth
4	Sam	1
5	Tia	1
6	Uma	2

The engine repeats the step until a round finds no new rows. Each round, team holds only the rows found in the last round. This table shows the rounds:

Round	Rows found	Why
Anchor	Sam, Tia	Their manager_id is 2
Round 1	Uma	Her manager_id is 4. Sam and Tia were found, and only Sam has a report.
Round 2	none	Nobody reports to Uma. The walk stops.

Write WITH RECURSIVE. SQL Server and Oracle leave out the word RECURSIVE.

Watch out

One wrong manager_id can make a loop. If Mira reported to Uma, the walk under Joe would go round and round. Add a limit in the recursive step: WHERE t.depth < 10.

Interview question

“List everyone under a manager.” Self joins stop at a fixed depth and return fewer rows with no error. Say the depth is not fixed, so you use a recursive CTE.

09 · Recursion

Build a path from the top

Start at the top. Let a text column grow by one name at each step.

⏱ 5 min · Topic 9 of 13

You can walk the tree from the top, too. Start at the owner, who has no boss. Add a path column that grows at each step.

Use IS NULL for the start, because = NULL is never true. The symbol || joins text:

The reporting path of everyoneworked example

SQL

Input data

staff7 rows

staff_id	name	manager_id
1	Mira	NULL
2	Joe	1
3	Lena	1
4	Sam	2
5	Tia	2
6	Uma	4
7	Raj	3

WITH RECURSIVE chain AS (
  SELECT staff_id, name, name AS path, 1 AS depth
  FROM staff
  WHERE manager_id IS NULL

  UNION ALL

  SELECT s.staff_id, s.name,
         c.path || ' > ' || s.name,
         c.depth + 1
  FROM staff AS s
  JOIN chain AS c ON s.manager_id = c.staff_id
)
SELECT name, depth, path
FROM chain
ORDER BY staff_id;

Result · 7 rows

name	depth	path
Mira	1	Mira
Joe	2	Mira > Joe
Lena	2	Mira > Lena
Sam	3	Mira > Joe > Sam
Tia	3	Mira > Joe > Tia
Uma	4	Mira > Joe > Sam > Uma
Raj	3	Mira > Lena > Raj

Here depth counts from Mira, who is depth 1. Uma is at depth 4: Mira, Joe, Sam, Uma.

Each round adds one name to the path of the row before it.

Round 1 finds Joe and Lena. Round 2 finds Sam, Tia and Raj. Round 3 finds Uma.

10 · Readability

When nesting gets too deep

If you cannot say what one row of each step means, the query is too nested.

⏱ 8 min · Topic 10 of 13

You read nested subqueries from the inside out. That is slow.

Here is a question: which customers have a total above the average customer total? In this query, the inner query that adds up the totals appears twice:

Hard to read: nestedworked example

SQL

Input data

customers5 rows

customer_id	name	country
1	Asha	IN
2	Ben	GB
3	Carla	US
4	Dev	NULL
5	Omar	IN

orders6 rows

order_id	customer_id	status	amount	order_date
101	1	paid	40.00	2026-01-03
102	1	paid	25.00	2026-01-10
103	2	refunded	60.00	2026-01-11
104	3	paid	15.50	2026-01-15
105	3	pending	NULL	2026-01-20
106	4	paid	80.00	2026-01-21

SELECT c.name, t.revenue
FROM (
  SELECT customer_id, SUM(amount) AS revenue
  FROM orders
  GROUP BY customer_id
) AS t
JOIN customers AS c ON c.customer_id = t.customer_id
WHERE t.revenue > (
  SELECT AVG(revenue)
  FROM (
    SELECT customer_id, SUM(amount) AS revenue
    FROM orders
    GROUP BY customer_id
  ) AS t2
)
ORDER BY t.revenue DESC;

Result · 3 rows

name	revenue
Dev	80.00
Asha	65.00
Ben	60.00

The totals count all orders, including refunded ones, to keep the example small. The average is 55.125. Carla has 15.50, so she is below it.

Give each step a name with a CTE. The totals step is now written once, and each step has one job:

Easier to read: named stepsworked example

SQL

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS revenue
  FROM orders
  GROUP BY customer_id
),
average_total AS (
  SELECT AVG(revenue) AS avg_revenue
  FROM customer_totals
)
SELECT c.name, t.revenue
FROM customer_totals AS t
JOIN customers AS c ON c.customer_id = t.customer_id
WHERE t.revenue > (SELECT avg_revenue FROM average_total)
ORDER BY t.revenue DESC;

Result · 3 rows

name	revenue
Dev	80.00
Asha	65.00
Ben	60.00

The same result. customer_totals has one row for each customer. average_total has one row.

Use this test: can you say what one row of each step means? If you cannot, the query is too nested.

CTE or subquery?

Both do the same job. This table shows how they differ:

Point	CTE	Subquery
Name	You name it at the top	You write it where you use it
Use twice	Use the name again	Write the query again
Reading order	From top to bottom	From the inside out
Best for	Many steps, or a step used twice	One small step used once

Interview question

“Improve this nested query.” Say: I pull each subquery into a named CTE. Then I can say what one row means and run each step alone.

11 · Final shape

Keep the last SELECT simple

Do the work in named steps. Let the last SELECT only show the answer.

⏱ 5 min · Topic 11 of 13

The last SELECT comes after all the CTEs. It makes the answer. Readers look at it first, so it should do very little.

It may pick columns, join in a name, and sort. If it needs SUM or CASE, add a step above it.

In this query, the CTE does the counting and adding. The last SELECT only adds names and sorts:

The work is in the CTEworked example

SQL

Input data

customers5 rows

customer_id	name	country
1	Asha	IN
2	Ben	GB
3	Carla	US
4	Dev	NULL
5	Omar	IN

orders6 rows

order_id	customer_id	status	amount	order_date
101	1	paid	40.00	2026-01-03
102	1	paid	25.00	2026-01-10
103	2	refunded	60.00	2026-01-11
104	3	paid	15.50	2026-01-15
105	3	pending	NULL	2026-01-20
106	4	paid	80.00	2026-01-21

WITH customer_stats AS (
  SELECT customer_id,
         COUNT(*) AS orders,
         SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END)
           AS paid_amount
  FROM orders
  GROUP BY customer_id
)
SELECT c.name, s.orders, s.paid_amount
FROM customer_stats AS s
JOIN customers AS c ON c.customer_id = s.customer_id
ORDER BY s.paid_amount DESC, c.name;

Result · 4 rows

name	orders	paid_amount
Dev	1	80.00
Asha	2	65.00
Carla	2	15.50
Ben	1	0

Ben has one refunded order, so his paid_amount is 0. Omar has no orders, so he has no row.

Read the last SELECT aloud: “each customer, their orders and paid amount, biggest paid amount first.” That is the question itself.

Remember

Do you want to say “and also…” when you describe the query? Put each “and also” in its own step.

12 · Practice

Practice lab

Say the steps first. Then write the query and run each step.

⏱ 11 min · Topic 12 of 13

Each exercise opens in the SQL workspace with its own tables. Write the query, run it, and check the result.

They follow the order of this chapter. The last five use a recursive CTE.

How to practise

Before you write SQL, say the steps in plain words: “first I keep…, then I add up…, then I show…”. Then write one CTE for each step.

Active recall Lab problems

1Paid order CTE decompositionMake a CTE with the paid and shipped orders. Then add up the revenue for each buyer.Open 2Above average order amountShow the orders that are bigger than the average order amount.Open 3Customers with shipped ordersUse EXISTS to show the customers who have at least one shipped order.Open 4Buyer revenue derived tableAdd up the revenue for each buyer in a subquery in FROM. Keep buyers with 200 or more.Open 5Clean order status CTEClean the status values in one CTE. Then count the orders for each clean status.Open 6Readable country revenue queryShow the revenue for each country. Use several CTEs with clear names.Open 7Everyone beneath one managerFind everyone under one manager, at any depth. A fixed number of self joins stops too early.Open 8The reporting chain above each personShow the chain of bosses for each person as one text path. Start from the top.Open 9Headcount at each level of the orgWork out the level of each person. The table has no level column. Then count the people at each level.Open 10Team size beneath each top managerCarry the top manager of each person through the walk. Then count the team of each top manager.Open 11Stop the recursion two levels downStop the walk after two levels. Put the limit in the recursive step.Open

13 · Next Chapter

Next Chapter

Next, you compare rows without merging them. You rank rows, find the latest row, and remove duplicates.

⏱ 3 min · Topic 13 of 13

Next chapter

Window Functions & Analytics Patterns

Next, you compare rows without merging them. You rank rows, find the latest row, and remove duplicates.

Preview chapter 05