SQL Batch Processing — Applied Correlated Subqueries

QUESTION 1

Correlated subquery — retrieve only each user’s “latest order”

Correlated subqueryMAXLatest recordsOrder API

Background ▼

A correlated subquery references a column from the outer query and is evaluated for each outer row, making it useful for finding a maximum or minimum under the same condition as the current row.

SELECT *
FROM   orders o1
WHERE  o1.ordered_at = (
  SELECT MAX(o2.ordered_at)       -- The inner query references the outer o1.user_id → correlated subquery
  FROM   orders o2
  WHERE  o2.user_id = o1.user_id  -- ← This is the core of the correlation: evaluated for each outer row
);

Retrieving the latest row in a group—such as each user’s latest login or each product’s last price change—is one of the most common API and batch-processing patterns.

Key point:A correlated subquery can cost the number of outer rows × subquery executions, so a ROW_NUMBER() window-function version may be faster for large data sets. It remains a readable choice for small data sets and straightforward requirements.

Problem ▼

From the orders table, retrieve the row for each user (user_id) with the maximum ordered_at. If a user has multiple orders, return only the latest row. Sort the result by user_id ascending.

Tables used

▸ orders

order_id	user_id	amount	ordered_at
1001	U01	3200	2024-03-10
1002	U01	1500	2024-05-20
1003	U02	8000	2024-04-01
1004	U02	2100	2024-06-15
1005	U03	500	2024-02-28
1006	U01	4700	2024-06-01

Expected Output ▼

order_id	user_id	amount	ordered_at
1006	U01	4700	2024-06-01
1004	U02	2100	2024-06-15
1005	U03	500	2024-02-28

Model Answer ▼

-- Retrieve each user’s latest order with a correlated subquery
SELECT
  o1.order_id,
  o1.user_id,
  o1.amount,
  o1.ordered_at
FROM orders o1                   -- Outer query (o1)
WHERE o1.ordered_at = (
  SELECT MAX(o2.ordered_at)      -- Get the latest date within the same user_id
  FROM orders o2
  WHERE o2.user_id = o1.user_id  -- Correlate with the outer row’s (o1) user_id
)
ORDER BY o1.user_id;             -- Sort by user ID ascending

/*
  Logical execution order (correlated subquery):
  1. FROM orders o1             → Read rows
  2. WHERE (Correlated subquery)     → Evaluate the subquery first and filter rows
  3. SELECT                     → Evaluate columns
  4. ORDER BY                   → Sort and output
*/

Explanation (table transitions & key points) ▼

▼ Solution 1: Evaluation with a correlated subquery

SELECT
  o1.order_id, o1.user_id,
  o1.amount, o1.ordered_at
FROM orders o1
WHERE o1.ordered_at = (
  SELECT MAX(o2.ordered_at)
  FROM orders o2
  WHERE o2.user_id = o1.user_id
)
ORDER BY o1.user_id;

LEGEND ▼

Rows read / loaded

① FROM orders o1 ▼

FROM orders o1Read all 6 rows from the orders table. o1 is an alias. The outer query evaluates these 6 rows one at a time.

1 / 4

order_id	user_id	amount	ordered_at
1001	U01	3200	2024-03-10
1002	U01	1500	2024-05-20
1003	U02	8000	2024-04-01
1004	U02	2100	2024-06-15
1005	U03	500	2024-02-28
1006	U01	4700	2024-06-01

Read all 6 rows

▼ Solution 2: Alternative pattern using ROW_NUMBER()

WITH ranked AS (
  SELECT
    order_id, user_id, ordered_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ordered_at DESC) AS rn
  FROM orders
)
SELECT order_id, user_id, ordered_at
FROM ranked
WHERE rn = 1;

LEGEND ▼

Rows read / loaded

① FROM orders ▼

FROM ordersRead all data.

1 / 4

order_id	user_id	ordered_at
1001	U01	2024-03-10
1002	U01	2024-05-20
1003	U02	2024-04-01
1004	U02	2024-06-15
1005	U03	2024-02-28
1006	U01	2024-06-01

LEARNING POINTS

Basic refresher: What is a table alias:FROM orders o1 uses o1 as an alternate name (alias) for the table. It is useful when referencing the same table twice or shortening a long table name. AS o1 is equivalent, but AS is optional.

How to identify a correlated subquery:If a column from the outer table (here, o1.user_id) appears inside the subquery, it is correlated. It runs for each outer row, so its complexity is the number of rows × subquery cost. EXPLAIN displays it as a “SubPlan”.

ROW_NUMBER() alternative pattern:

WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ordered_at DESC) rn FROM orders) SELECT * FROM ranked WHERE rn = 1;

assigns rn=1 to the row with the greatest ordered_at within each user_id, so it safely reduces ties at the same timestamp to one row.

EXISTS vs IN vs correlated subquery: Use EXISTS for existence checks, IN for value-list matching, and a correlated subquery (or window function) for finding a group maximum. Choosing by purpose is a practical SQL basic.

ANTI-PATTERNS

Ignoring ties at the latest timestamp:Filtering with MAX(ordered_at) returns both orders when two orders share a timestamp. If the requirement is exactly one row, use ROW_NUMBER().

Applying a correlated subquery indiscriminately to a large table: On a table with millions of rows, it can become extremely slow because of the number of rows × a subquery that cannot use an index. Check the plan with EXPLAIN ANALYZE first and rewrite it with a window function or JOIN if needed.

Practical column: “Latest-record retrieval” is a common API pattern

API endpoints that return the latest row in a group—such as a user’s last login time, a product’s latest price, or an order’s latest status—appear constantly in production. Knowing this pattern alone can greatly improve backend implementation quality. Adding indexes to updated_at or created_at can also speed up the MAX calculation inside the subquery.

QUESTION 2

WITH clause (CTE) — Build a monthly sales report step by step

WITH / CTESUM / AVGSales reportsBatch aggregation

Background ▼

A WITH clause (CTE, or Common Table Expression) defines a temporary named result set inside a query. Instead of nesting complex subqueries, you can separate the work into step-by-step blocks, greatly improving readability and maintainability.

WITH
  cte_name1 AS (              -- Block 1: define the first temporary table
    SELECT ... FROM some_table
  ),
  cte_name2 AS (              -- Block 2: cte_name1 is available here
    SELECT ... FROM cte_name1
  )
SELECT * FROM cte_name2;       -- The final SELECT produces the actual output

When to use it:1) when you want to flatten deeply nested subqueries, 2) when you want to reference the same subquery multiple times (MATERIALIZEDthe MATERIALIZED option can force one computation), and 3) when writing recursive queries.

Problem ▼

From the sales table, retrieve sales totals by month and product category, plus each category’s percentage of the monthly total. Use CTEs to build it in three stages: “monthly category aggregation” → “monthly total” → “ratio calculation”. Sort by month and category ascending.

Tables used

▸ sales

sale_id	category	amount	sold_at
1	food	3000	2024-05-03
2	drink	1500	2024-05-10
3	food	2000	2024-05-20
4	drink	2500	2024-06-05
5	food	4000	2024-06-12
6	drink	1000	2024-06-20

Expected Output ▼

month	category	cat_total	month_total	ratio_pct
2024-05	drink	1500	6500	23.08
2024-05	food	5000	6500	76.92
2024-06	drink	3500	7500	46.67
2024-06	food	4000	7500	53.33

Model Answer ▼

WITH
  cat_monthly AS (                                  -- ① Calculate sales totals by month and category
    SELECT
      TO_CHAR(sold_at, 'YYYY-MM') AS month,
      category,
      SUM(amount) AS cat_total
    FROM sales
    GROUP BY TO_CHAR(sold_at, 'YYYY-MM'), category
  ),
  month_total AS (                                  -- ② Total all categories by month (reuse ①)
    SELECT
      month,
      SUM(cat_total) AS month_total
    FROM cat_monthly                                -- CTE ①  reference
    GROUP BY month
  )

-- ③ Final SELECT: JOIN ① and ② to calculate the ratio
SELECT
  cm.month,
  cm.category,
  cm.cat_total,
  mt.month_total,
  ROUND(
    cm.cat_total::NUMERIC / mt.month_total * 100, 2
  ) AS ratio_pct                               -- Cast to ::NUMERIC to retain decimal places
FROM cat_monthly cm
JOIN month_total mt ON cm.month = mt.month  -- Join CTE ① and ② on month
ORDER BY cm.month, cm.category;

/*
  Logical execution order (WITH clause):
  1. CTE cat_monthly  → Define the CTE (aggregation)
  2. CTE month_total  → Define the CTE (aggregation)
  3. FROM / JOIN      → Join (matching rows only)
  4. SELECT           → Evaluate columns (ratio_pct)
  5. ORDER BY         → Sort and output
*/

Explanation (table transitions & key points) ▼

▼ Solution: Step-by-step aggregation with CTEs

WITH
  cat_monthly AS (
    SELECT TO_CHAR(sold_at,'YYYY-MM') AS month, category,
           SUM(amount) AS cat_total
    FROM sales
    GROUP BY TO_CHAR(sold_at,'YYYY-MM'), category
  ),

  month_total AS (
    SELECT month, SUM(cat_total) AS month_total
    FROM cat_monthly
    GROUP BY month
  )

SELECT cm.month, cm.category, cm.cat_total,
       mt.month_total, ROUND(cm.cat_total::NUMERIC / mt.month_total * 100, 2) AS ratio_pct
FROM cat_monthly cm
JOIN month_total mt ON cm.month = mt.month
ORDER BY cm.month, cm.category;

LEGEND ▼

Rows read / loaded

① FROM sales ▼

FROM salesRead all 6 rows from the sales table. This is the source data for aggregating sold_at by month.

1 / 5

sale_id	category	amount	sold_at
1	food	3000	2024-05-03
2	drink	1500	2024-05-10
3	food	2000	2024-05-20
4	drink	2500	2024-06-05
5	food	4000	2024-06-12
6	drink	1000	2024-06-20

Read all 6 rows

LEARNING POINTS

Basic refresher: the relationship between GROUP BY and SUM:GROUP BY groups rows with the same values in the specified columns.SUM(amount) sums amount within each group.GROUP BY only columns listed in it and aggregate functions SELECT may be selected (other columns are not allowed).

CTEs are “named intermediate tables for processing”:Once a CTE has a name, later CTEs and the final SELECT can use it directly. You avoid repeating an aggregation, and the query reads like documentation of the processing flow.

::NUMERIC casts and division by zero:In PostgreSQL, integer ÷ integer also produces an integer (for example, 3 / 2 = 1).::NUMERIC converts to a numeric value for an accurate ratio. Also, NULLIF(month_total, 0) prevents division by zero.

Alternative without CTEs (window function): SUM(SUM(amount)) OVER (PARTITION BY month) Combining aggregate and window functions this way lets you get “each category total” and “the monthly total” together without creating an intermediate table.

MATERIALIZED hint:In PostgreSQL 12 and later, WITH cte AS MATERIALIZED (...) forces the CTE to be physically computed exactly once (the planner decides by default). This makes performance easier to predict when the same CTE is referenced multiple times.

ANTI-PATTERNS

Nesting subqueries too deeply:Without a CTE, the same query becomes SELECT ... FROM (SELECT ... FROM (SELECT ... FROM sales) ...) ... a three- or four-level nest, making it nearly impossible to follow what each subquery returns. CTEs dramatically reduce maintenance cost.

Selecting more columns than necessary inside a CTE:With CTEs, SELECT * includes unnecessary columns in the intermediate result and increases the data volume. List only the columns you will use later.

Practical column: Batch aggregation reports and CTE design

When generating monthly or weekly sales reports in a batch, organize the process into four CTE blocks—read raw data, aggregate, transform, and output—to respond quickly to SQL changes. CTEs are readable, making the logic easy to explain in reviews.

QUESTION 3

CASE WHEN + Conditional aggregation — inventory-status pivot aggregation with FILTER

CASE WHENSUM / FILTERPivot aggregationInventory API

Background ▼

CASE WHEN is SQL’s conditional expression. Embedding it in an aggregate function enables conditional aggregation (pivot aggregation), where only rows meeting a condition are aggregated. PostgreSQL also supports the dedicated FILTER (WHERE ...) syntax.

-- Conditional aggregation with CASE WHEN (standard SQL)
SUM(CASE WHEN status = 'active' THEN amount ELSE 0 END) AS active_sum

-- Conditional aggregation with FILTER (PostgreSQL)
SUM(amount) FILTER (WHERE status = 'active') AS active_sum

What is pivot aggregation:It expands row-oriented data into columns so that aggregates for multiple categories appear on one row. Think of an Excel pivot table. The key is to place multiple category totals side by side on one row rather than GROUP BY into separate rows.

Problem ▼

From the inventory table, retrieve the count of in-stock items (quantity > 0), out-of-stock items (quantity = 0), and all items on one row per warehouse as a pivot aggregation.

Tables used

▸ inventory

item_id	warehouse	quantity
A01	Tokyo	50
A02	Tokyo	0
A03	Tokyo	20
B01	Osaka	0
B02	Osaka	0
B03	Osaka	15
C01	Fukuoka	100
C02	Fukuoka	30

Expected Output ▼

warehouse	in_stock	out_of_stock	total_items
Tokyo	2	1	3
Osaka	1	2	3
Fukuoka	2	0	2

Model Answer ▼

SELECT
  warehouse,
  COUNT(CASE WHEN quantity > 0 THEN 1 END) AS in_stock,  -- ① CASE WHEN conditional aggregation (true=1 / false=NULL; COUNT ignores NULL)
  COUNT(*) FILTER (WHERE quantity = 0) AS out_of_stock,  -- ② Conditional aggregation with FILTER (PostgreSQL extension)  -- Count only rows where quantity = 0
  COUNT(*) AS total_items                                                                       -- Count all rows

FROM inventory
GROUP BY warehouse   -- Group by warehouse
ORDER BY MIN(item_id);  -- Preserve the fixture order via the smallest item_id

/*
  Logical execution order:
  1. FROM inventory      → Read rows
  2. GROUP BY warehouse  → grouping
  3. Aggregate-function evaluation → evaluate the aggregates (COUNT / FILTER)
  4. SELECT              → Evaluate columns
  5. ORDER BY            → Sort and output
*/

Explanation (table transitions & key points) ▼

▼ Solution: Pivot aggregation with CASE WHEN / FILTER

SELECT
  warehouse,
  COUNT(CASE WHEN quantity > 0 THEN 1 END) AS in_stock,
  COUNT(*) FILTER (WHERE quantity = 0) AS out_of_stock,
  COUNT(*) AS total_items
FROM inventory
GROUP BY warehouse
ORDER BY MIN(item_id);

LEGEND ▼

Rows read / loaded

① FROM inventory ▼

FROM inventoryRead all 8 rows from inventory. warehouse and quantity provide the values used for aggregation.

1 / 4

item_id	warehouse	quantity
A01	Tokyo	50
A02	Tokyo	0
A03	Tokyo	20
B01	Osaka	0
B02	Osaka	0
B03	Osaka	15
C01	Fukuoka	100
C02	Fukuoka	30

Read all 8 rows

LEARNING POINTS

Basic refresher: CASE WHEN structure:CASE WHEN condition THEN value ELSE default value END is the basic syntax. If ELSE is omitted, non-matching rows become NULL. Because COUNT ignores NULL, the “THEN 1, no ELSE” pattern counts only rows that meet the condition.

When pivot aggregation is useful:A typical use is converting vertical category data into a horizontal report: put each month’s success/failure counts or each warehouse’s in-stock/out-of-stock counts on one row. Use it when aggregation should reduce rows and add columns.

SUM(CASE WHEN ... THEN amount END) pattern:This applies to SUM as well as COUNT.SUM(CASE WHEN category = 'food' THEN amount ELSE 0 END) puts only the food-category sales total beside the totals for other categories on the same row.

Combining it with window functions:COUNT(*) FILTER (WHERE qty > 0) OVER (PARTITION BY warehouse) FILTER can also be combined with aggregate window functions. This is an advanced use that performs group aggregation and row-level processing together.

ANTI-PATTERNS

Confusing CASE WHEN ELSE 0 with ELSE NULL:For conditional aggregation with SUM, ELSE 0 and omitted ELSE (NULL) usually produce the same result, but AVG differs.AVG(CASE WHEN qty > 0 THEN qty ELSE 0 END) includes zero rows in the average, whereasAVG(CASE WHEN qty > 0 THEN qty END) averages only in-stock rows. Make the intent explicit.

Confusing the roles of WHERE and CASE WHEN:If you only need to keep in-stock rows, WHERE is enough. Use CASE WHEN when you need to keep rows while transforming values or branching an aggregation. Unnecessary CASE WHEN reduces readability.

Practical column: Applying this to an inventory-alert API

In an inventory-management API, a typical use case is a batch that alerts warehouses with many out-of-stock items. Aggregating each warehouse’s status in one SQL query and returning it to the application avoids the N+1 problem of issuing a separate query per warehouse. In addition, HAVING out_of_stock > 0 limits the result to warehouses with out-of-stock items.

QUESTION 4

LAG / LEAD — Visualize trends with month-over-month and row-over-row changes

LAG / LEADOVER PARTITION BYMonth-over-month changeSales analysis

Background ▼

LAG(column, n) is a window function that returns the value n rows before the current row. LEAD(column, n) returns the value n rows after it. Its key feature is adding the difference from a previous row to the same row without aggregating rows with GROUP BY.

LAG(col, 1, 0) OVER (            -- Value one row before col; default 0 when there is no previous row
  PARTITION BY group_col         -- Keep an independent order within each group
  ORDER BY     sort_col          -- Define “previous” and “next” by this order (required)
)

LEAD(col, 1) OVER (             -- Value one row after col (the next row)
  ORDER BY sort_col
)

ORDER BY is required: When using LAG/LEAD in a window function, ORDER BY inside the OVER clause Without a defined order, “the previous row” is undefined.

Problem ▼

monthly_sales table, retrieve monthly sales, previous-month sales, and month-over-month change (%) for each product (product_id). When there is no previous-month data (the first month), previous-month sales and month-over-month change may be NULL. Sort by product_id and month ascending.

Tables used

▸ monthly_sales

product_id	month	revenue
P01	2024-03	10000
P01	2024-04	12000
P01	2024-05	9000
P02	2024-03	5000
P02	2024-04	7500
P02	2024-05	7500

Expected Output ▼

product_id	month	revenue	prev_revenue	mom_pct
P01	2024-03	10000	NULL	NULL
P01	2024-04	12000	10000	+20.00
P01	2024-05	9000	12000	-25.00
P02	2024-03	5000	NULL	NULL
P02	2024-04	7500	5000	+50.00
P02	2024-05	7500	7500	0.00

Model Answer ▼

SELECT
  product_id,
  month,
  revenue,
  LAG(revenue, 1) OVER (                                                                -- ① Get the revenue one row earlier (the previous row when ordered by month for each product)
    PARTITION BY product_id
    ORDER BY month
  ) AS prev_revenue,
  ROUND(                                                                                -- ② Month-over-month calculation: (current month - previous month) / previous month * 100
    (revenue - LAG(revenue, 1) OVER (PARTITION BY product_id ORDER BY month))::NUMERIC
    / NULLIF(LAG(revenue, 1) OVER (PARTITION BY product_id ORDER BY month), 0)
    * 100, 2
  ) AS mom_pct

FROM monthly_sales
ORDER BY product_id, month;

/*
  Logical execution order (window functions):
  1. FROM monthly_sales  → Read rows
  2. Window-function evaluation → evaluate window functions (preserve row count)
  3. Format values             → Format values (ROUND, NULLIF)
  4. SELECT              → Evaluate columns
  5. ORDER BY            → Sort and output
*/

Explanation (table transitions & key points) ▼

▼ Solution: Calculate month-over-month change with LAG

SELECT
  product_id, month, revenue,
  LAG(revenue, 1) OVER (
    PARTITION BY product_id
    ORDER BY month
  ) AS prev_revenue,

  ROUND(
    (revenue - LAG(revenue, 1) OVER (PARTITION BY product_id ORDER BY month))::NUMERIC
    / NULLIF(LAG(revenue, 1) OVER (PARTITION BY product_id ORDER BY month), 0)
    * 100, 2
  ) AS mom_pct

FROM monthly_sales
ORDER BY product_id, month;

LEGEND ▼

Rows read / loaded

① FROM monthly_sales ▼

FROM monthly_salesRead all 6 rows from monthly_sales.

1 / 4

product_id	month	revenue
P01	2024-03	10000
P01	2024-04	12000
P01	2024-05	9000
P02	2024-03	5000
P02	2024-04	7500
P02	2024-05	7500

Read all 6 rows

▼ Advanced: Compare against the first month with FIRST_VALUE()

SELECT
  product_id, month, revenue,
  FIRST_VALUE(revenue) OVER (
    PARTITION BY product_id
    ORDER BY month
  ) AS first_revenue,
  ROUND(revenue::NUMERIC / FIRST_VALUE(revenue) OVER (PARTITION BY product_id ORDER BY month) * 100, 1) AS growth_pct
FROM monthly_sales
ORDER BY product_id, month;

LEGEND ▼

Rows read / loaded

① FROM monthly_sales ▼

FROM monthly_salesRead all data.

1 / 4

product_id	month	revenue
P01	2024-03	10000
P01	2024-04	12000
P01	2024-05	9000
P02	2024-03	5000
P02	2024-04	7500
P02	2024-05	7500

LEARNING POINTS

Basic refresher: window functions vs GROUP BY:GROUP BY aggregates a group into one row, while a window function keeps the original rows and attaches an aggregate value. That is the key difference.If there is an OVER() clause, it is a window function.

PARTITION BY is optional:Without PARTITION BY, all rows are treated as one partition. For month-over-month change across all products, LAG(revenue,1) OVER (ORDER BY month) is sufficient.

FIRST_VALUE / LAST_VALUE comparison:FIRST_VALUE(revenue) OVER (PARTITION BY product_id ORDER BY month) attaches the first row’s value (= sales in the oldest month) to every row in the partition. It is useful for growth rates such as “current-month sales / first-month sales × 100.”

Classify quantiles with NTILE(n):NTILE(4) OVER (ORDER BY revenue DESC) splits sales into top-25%, 50%, 75%, and 100% groups. It is useful for quartile analysis and ranking APIs.

ANTI-PATTERNS

Forgetting ORDER BY in the OVER clause:LAG/LEAD cannot work unless ORDER BY defines previous and next. LAG without ORDER BY may error or behave nondeterministically, depending on the database.

Ignoring missing months:If April is missing, LAG returns March as the previous record for May; it does not infer February as the month before March. If consecutive months are required, fill the calendar with generate_series before applying LAG.

Practical column: Designing a trend-analysis API

In a dashboard API, trend charts for month-over-month or week-over-week changes are standard. LAG removes the need for application-side difference calculations and lets SQL return a complete response. The SQL can control whether the first month’s change is zero or hidden by choosing 0 or NULL as LAG’s third argument.

QUESTION 5

Recursive CTE (WITH RECURSIVE) — retrieve all descendants in a category hierarchy

WITH RECURSIVEUNION ALLHierarchical dataCategory API

Background ▼

WITH RECURSIVE is syntax for writing a recursive query in which a CTE references itself. It is used to traverse hierarchical data such as category trees, organization charts, and bills of materials.

WITH RECURSIVE cte AS (
  -- ① Anchor member (the recursion starting point; runs once at the beginning)
  SELECT id, parent_id, name, 0 AS depth
  FROM   categories
  WHERE  id = 1                  -- Select one starting row

  UNION ALL                      -- Connect the anchor and recursive members (fast because duplicates are not removed)

  -- ② Recursive member (cte references the previous iteration; repeat until it returns zero rows)
  SELECT c.id, c.parent_id, c.name, r.depth + 1
  FROM   categories c
  JOIN   cte         r ON c.parent_id = r.id  -- Retrieve rows whose parent ID is the previous iteration’s id
)
SELECT * FROM cte;

Infinite-loop warning:If the data contains a circular reference (A→B→A), recursion never stops. Always add a limit such as depth < 10 or loop detection using a path column.

Problem ▼

categories is a self-referencing hierarchy through parent_id. Retrieve all descendant categories starting from category_id = 1, including grandchildren and great-grandchildren, not just direct children with their depth (hierarchy level). Treat the starting point as depth 0.

Tables used

▸ categories

category_id	parent_id	name
1	NULL	Food
2	1	Drinks
3	1	Snacks
4	2	Coffee
5	2	Juice
6	3	Chocolate
7	10	Miscellaneous (separate tree)

Expected Output ▼

category_id	parent_id	name	depth
1	NULL	Food	0
2	1	Drinks	1
3	1	Snacks	1
4	2	Coffee	2
5	2	Juice	2
6	3	Chocolate	2

Model Answer ▼

WITH RECURSIVE category_tree AS (

  -- ① Anchor member: retrieve the starting row (Food = category_id 1)
  SELECT
    category_id,
    parent_id,
    name,
    0 AS depth           -- Set the starting depth to 0
  FROM categories
  WHERE category_id = 1

  UNION ALL

  -- ② Recursive member: retrieve rows whose parent is the previous result (ct) id
  SELECT
    c.category_id,
    c.parent_id,
    c.name,
    ct.depth + 1                        -- Increase depth by 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.category_id
  WHERE ct.depth < 10                   -- Stop recursion at depth 10 (loop prevention)
)

SELECT
  category_id,
  parent_id,
  name,
  depth
FROM category_tree
ORDER BY depth, category_id;

/*
  Logical execution order (recursive CTE):
  1. Evaluate the anchor member (starting point)
  2. Recursive iteration 1 (expand children)
  3. Recursive iteration 2 (expand children)
  4. Stop when no rows are added
  5. UNION ALL  → take the union
  6. Evaluate the outer query → sort and output with ORDER BY
*/

Explanation (table transitions & key points) ▼

▼ Solution: Retrieve all descendants with a recursive CTE

WITH RECURSIVE category_tree AS (
  SELECT
    category_id, parent_id, name, 0 AS depth
  FROM categories
  WHERE category_id = 1

  UNION ALL

  SELECT
    c.category_id, c.parent_id, c.name, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.category_id
  WHERE ct.depth < 10
)

SELECT
  category_id, parent_id, name, depth
FROM category_tree
ORDER BY depth, category_id;

LEGEND ▼

Rows read / loaded

① Anchor member (starting point) ▼

WHERE category_id = 1 → Food (depth=0)Retrieve the starting row (Food, id=1) from categories. This is where recursion begins; assign depth=0.

1 / 4

category_id	parent_id	name	depth
1	NULL	Food	0

Anchor: 1 row

▼ Advanced: Generate path strings with a recursive CTE

WITH RECURSIVE category_tree AS (
  SELECT
    category_id, parent_id, name,
    name::TEXT AS path
  FROM categories
  WHERE category_id = 1

  UNION ALL

  SELECT
    c.category_id, c.parent_id, c.name,
    ct.path || ' > ' || c.name AS path
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT category_id, name, path
FROM category_tree
ORDER BY path;

LEGEND ▼

Rows read / loaded

① Anchor member (starting point) ▼

name::TEXT AS pathRetrieve the starting row (Food) and use its name as the initial path value.

1 / 4

category_id	name	path
1	Food	Food

LEARNING POINTS

Basic refresher: self-referencing tables:A self-referencing table is a structure in which rows in the same table reference another row as their parent.parent_id in the same table points to category_id . It is widely used for organization charts, category trees, and bills of materials. A NULL parent_id means “no parent = root.”

Loop detection with a path column:ARRAY[category_id] AS path Starting from path || c.category_id and accumulating through recursion,WHERE NOT c.category_id = ANY(ct.path) detects circular references and completely prevents infinite loops. This is safer than a depth limit.

Generating path strings:'Food > Drinks > Coffee' can also be built with a recursive CTE. In the anchor, name AS path, and in the recursive member, ct.path || ' > ' || c.name AS path append the values so that each row receives its full path.

The ltree extension:PostgreSQL provides ltree as a path type, and 'food.drink.coffee' represents trees with strings such as this,@> (ancestor check) and ~ (path-pattern matching) enable fast hierarchical searches. For large hierarchies, ltree is more suitable.

ANTI-PATTERNS

Unprotected recursion that can loop forever: If production data contains a circular reference (A→B→C→A), recursion continues until the database exhausts memory/CPU. Always add a depth limit or loop detection with a path column.

Issuing N+1 queries through application-side recursion:An application pattern of “get children → get their children → …” issues queries proportional to depth × breadth, a recursive version of the N+1 problem. WITH RECURSIVE retrieves the entire hierarchy in one query.

Practical column: Designing an e-commerce category API

E-commerce APIs often return category trees such as “Food > Drinks > Coffee”. WITH RECURSIVE enables an efficient implementation: retrieve the whole tree in one SQL query, then assemble the tree structure in the application. Conversely, to retrieve all ancestors of a category and build breadcrumbs, start at the leaf category and recurse by following parent_id.