In this post we are going to cover CTEs and recursive queries in PostgreSQL: how Postgres plans a CTE, WITH RECURSIVE for hierarchies, generate_series, data-modifying CTEs, EXISTS against JOIN, and how to stop a recursion that never ends. These are 7 real PostgreSQL interview questions, asked the way they come up on the job.
A CTE, a common table expression, is a named query that you write before the main query with the WITH keyword. It makes long SQL readable, and with the word RECURSIVE it can walk trees and graphs. Interviewers like this topic because Postgres has its own behaviour here, and it changed in version 12.
Every query below is complete, so you can copy it, run it on the sample data and get exactly the output shown under it.
Part 4 of the Real Time PostgreSQL Interview Questions and Answers series on TechBrothersIT.
- Let's create some sample data
- Question 1: A teammate says: never use CTEs in Postgres, they are an optimization fence and always slow. Is that still true?
- Question 2: HR wants the full org chart: every employee with their level in the company and the chain of managers above them. The table only stores each person's direct manager. How do you query it?
- Question 3: The daily sales chart has holes. Days with no sales are simply missing, so the line jumps from one day to the next. How do you return every day, with zero where nothing was sold?
- Question 4: You need to move cancelled orders from the orders table to an archive table. It must be safe: a row must never be lost, and never be copied twice. How do you do it in one statement?
- Question 5: Marketing wants the list of customers who have at least one delivered order. Your JOIN returns Omar and Mei twice. How do you fix it properly?
- Question 6: A recursive query on a parent and child table never finishes, and it fills the disk with temporary files. What happened, and how do you make the query safe?
- Question 7: Finance wants, for every manager, the number of people under them at any level, and the total salary of that whole team. How do you roll a hierarchy up?
- Key points to remember
- The full SQL script
Let's create some sample data
We use a small shop database called TB Shop: departments, employees, customers, products, orders, order items and payments. For this post we add big_orders with 100,000 generated rows, daily_sales with some days missing, an empty orders_archive table, and links, a small graph that contains a cycle. The script below creates everything this post needs. It was written for and run on PostgreSQL 16.
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
INSERT INTO departments VALUES (10, 'Sales'), (20, 'Engineering'), (30, 'HR'), (40, 'Finance');
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT, -- NULL = not assigned yet
manager_id INT, -- NULL = top of the company
salary INT,
hire_date DATE
);
INSERT INTO employees VALUES
(1, 'Aisha', NULL, NULL, 150000, '2018-03-01'),
(2, 'Omar', 20, 1, 120000, '2019-06-15'),
(3, 'Lina', 20, 2, 90000, '2021-01-10'),
(4, 'Ravi', 20, 2, 90000, '2022-04-05'),
(5, 'Mei', 20, 2, 75000, '2024-09-01'),
(6, 'Sara', 10, 1, 95000, '2020-02-20'),
(7, 'Daniel', 10, 6, 105000, '2021-07-01'),
(8, 'Fatima', 10, 6, 60000, '2023-11-12'),
(9, 'John', 30, 1, 70000, '2019-09-30'),
(10,'Zara', NULL, 9, 55000, '2026-01-05');
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(20),
city VARCHAR(20),
region VARCHAR(10), -- NULL = unknown
email VARCHAR(40)
);
INSERT INTO customers VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@mail.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@mail.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@mail.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@mail.com'),
(5, 'Ravi Patel', 'Austin', NULL, 'ravi@mail.com'),
(6, 'Chen Wu', 'Denver', 'West', 'chen@mail.com'),
(7, 'Sara K.', 'Toronto', 'East', 'sara@mail.com');
CREATE TABLE products (product_id INT PRIMARY KEY, product VARCHAR(20), category VARCHAR(15), price NUMERIC(10,2));
INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1200.00),
(2, 'Monitor', 'Electronics', 300.00),
(3, 'Keyboard', 'Accessories', 50.00),
(4, 'Mouse', 'Accessories', 25.00),
(5, 'Desk', 'Furniture', 400.00),
(6, 'Chair', 'Furniture', 250.00);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(10),
amount NUMERIC(10,2)
);
INSERT INTO orders VALUES
(1001, 1, '2026-01-05', 'Delivered', 1250.00),
(1002, 2, '2026-01-18', 'Delivered', 300.00),
(1003, 4, '2026-02-02', 'Delivered', 650.00),
(1004, 1, '2026-02-14', 'Cancelled', 50.00),
(1005, 3, '2026-02-20', 'Delivered', 1200.00),
(1006, 4, '2026-03-03', 'Shipped', 275.00),
(1007, 5, '2026-03-15', 'Delivered', 400.00),
(1008, 2, '2026-04-01', 'Delivered', 75.00),
(1009, 4, '2026-04-22', 'Delivered', 250.00),
(1010, 1, '2026-05-09', 'Shipped', 300.00),
(1011, 3, '2026-05-30', 'Cancelled', 400.00),
(1012, 7, '2026-06-11', 'Delivered', 50.00);
CREATE TABLE order_items (order_id INT, product_id INT, qty INT, unit_price NUMERIC(10,2));
INSERT INTO order_items VALUES
(1001, 1, 1, 1200.00), (1001, 3, 1, 50.00),
(1002, 2, 1, 300.00),
(1003, 5, 1, 400.00), (1003, 6, 1, 250.00),
(1004, 3, 1, 50.00),
(1005, 1, 1, 1200.00),
(1006, 6, 1, 250.00), (1006, 4, 1, 25.00),
(1007, 5, 1, 400.00),
(1008, 4, 3, 25.00),
(1009, 6, 1, 250.00),
(1010, 2, 1, 300.00),
(1011, 5, 1, 400.00),
(1012, 3, 1, 50.00);
CREATE TABLE payments (payment_id INT PRIMARY KEY, order_id INT, paid_amount NUMERIC(10,2));
INSERT INTO payments VALUES
(1, 1001, 1250.00), (2, 1002, 300.00),
(3, 1003, 400.00), (4, 1003, 250.00), -- order 1003 was paid in two parts
(5, 1005, 1200.00), (6, 1007, 400.00),
(7, 1008, 75.00), (8, 1009, 250.00), (9, 1012, 50.00);
-- a bigger table, so the planner has a real choice between an index and a full scan
CREATE TABLE big_orders AS
SELECT g AS order_id, (g % 1000) + 1 AS customer_id, (g % 500) + 10 AS amount
FROM generate_series(1, 100000) AS g;
ALTER TABLE big_orders ADD PRIMARY KEY (order_id);
ANALYZE big_orders;
CREATE TABLE daily_sales (sale_id INT PRIMARY KEY, sale_date DATE, amount INT);
INSERT INTO daily_sales VALUES
(1, '2026-06-01', 100), (2, '2026-06-02', 200), (3, '2026-06-02', 50),
(4, '2026-06-04', 300), (5, '2026-06-07', 150); -- no sales on June 3, 5 and 6
CREATE TABLE orders_archive (LIKE orders INCLUDING ALL);
CREATE TABLE links (src TEXT, dst TEXT);
INSERT INTO links VALUES ('A', 'B'), ('B', 'C'), ('C', 'A'), ('C', 'D'); -- C points back to A: a cycleQuestion 1: A teammate says: never use CTEs in Postgres, they are an optimization fence and always slow. Is that still true?
Not since PostgreSQL 12. A CTE that is used once, is not recursive and has no side effects is inlined into the main query, so filters and indexes work through it. You can still choose with MATERIALIZED or NOT MATERIALIZED.
Same query, two ways to run it. Inlined, Postgres pushes the filter into the CTE. It uses the primary key index and touches one row. Materialized, it first builds the whole CTE result, all 100,000 rows, and only then looks for order 42 inside that temporary result, with no index to help.
Here is the proof. A plain CTE, filtered on order ID. The plan is a single Index Scan on the primary key. The CTE is gone. Postgres rewrote it as a subquery.
EXPLAIN (COSTS OFF)
WITH o AS (
SELECT * FROM big_orders
)
SELECT * FROM o
WHERE order_id = 42;Index Scan using big_orders_pkey on big_orders Index Cond: (order_id = 42)
Now add the word MATERIALIZED. The plan changes: a CTE Scan with a filter on top, and under it a sequential scan of the whole table.
EXPLAIN (COSTS OFF)
WITH o AS MATERIALIZED (
SELECT * FROM big_orders
)
SELECT * FROM o
WHERE order_id = 42;CTE Scan on o
Filter: (order_id = 42)
CTE o
-> Seq Scan on big_ordersSo when is MATERIALIZED useful? When the CTE is expensive and you read it several times, or when you really want to stop the planner from pushing a filter down. If a CTE is used more than once, Postgres materializes it by default.
Used once → inlined. Used twice or more, recursive, or with INSERT/UPDATE/DELETE → materialized.
Question 2: HR wants the full org chart: every employee with their level in the company and the chain of managers above them. The table only stores each person's direct manager. How do you query it?
Use a recursive CTE. The anchor selects the top of the tree. The recursive step joins employees to the rows found so far, one level deeper each time, and it stops when a step returns no new rows.
Watch it run. The anchor finds the row with no manager: Aisha, level one. Step one joins employees to Aisha and finds Omar, Sara and John, level two. Step two joins employees to those three and finds six more, level three. Step three finds nobody, so the recursion stops.
Here is the query. The anchor is above UNION ALL. The recursive step is below it, and it refers to the CTE itself. Each step adds one to the level and appends the name to the path.
WITH RECURSIVE org AS (
SELECT emp_id, name, 1 AS level, name::text AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.name, o.level + 1,
o.path || ' > ' || e.name
FROM employees e
JOIN org o ON e.manager_id = o.emp_id
)
SELECT level, name, path
FROM org
ORDER BY path;| level | name | path |
|---|---|---|
| 1 | Aisha | Aisha |
| 2 | John | Aisha > John |
| 3 | Zara | Aisha > John > Zara |
| 2 | Omar | Aisha > Omar |
| 3 | Lina | Aisha > Omar > Lina |
| 3 | Mei | Aisha > Omar > Mei |
| 3 | Ravi | Aisha > Omar > Ravi |
| 2 | Sara | Aisha > Sara |
| 3 | Daniel | Aisha > Sara > Daniel |
| 3 | Fatima | Aisha > Sara > Fatima |
Ten employees, three levels, and the path shows the chain of managers. Sorting by the path gives the tree order for free.
ORDER BY path prints the tree in order: each manager, then their team.
Question 3: The daily sales chart has holes. Days with no sales are simply missing, so the line jumps from one day to the next. How do you return every day, with zero where nothing was sold?
Rows that do not exist cannot be grouped. First build the calendar with generate_series, then LEFT JOIN the sales to it and use COALESCE to turn the NULLs into zero.
The problem is on the left. The sales table has rows for June 1st, 2nd, 4th and 7th only. So we generate a calendar: one row for every day of the week. The LEFT JOIN keeps every day. Days with sales get their total, and the three empty days get a zero.
| sale_date | amount |
|---|---|
| Jun 01 | 100 |
| Jun 02 | 200 |
| Jun 02 | 50 |
| Jun 04 | 300 |
| Jun 07 | 150 |
| day | sales |
|---|---|
| Jun 01 | 100 |
| Jun 02 | 250 |
| Jun 03 | 0 |
| Jun 04 | 300 |
| Jun 05 | 0 |
| Jun 06 | 0 |
| Jun 07 | 150 |
Here is the query. generate_series returns one timestamp per day between the two dates. We left join the sales on the date and sum them. Seven days come back, and June 3rd, 5th and 6th show zero.
SELECT d::date AS day,
COALESCE(SUM(s.amount), 0) AS sales
FROM generate_series(DATE '2026-06-01',
DATE '2026-06-07',
INTERVAL '1 day') AS d
LEFT JOIN daily_sales s ON s.sale_date = d::date
GROUP BY d
ORDER BY d;| day | sales |
|---|---|
| 2026-06-01 | 100 |
| 2026-06-02 | 250 |
| 2026-06-03 | 0 |
| 2026-06-04 | 300 |
| 2026-06-05 | 0 |
| 2026-06-06 | 0 |
| 2026-06-07 | 150 |
In other databases you would write a recursive CTE to count the days. In Postgres, generate_series is the simple and fast way. It also works with numbers, and with any interval, like one hour or one month.
generate_series works for numbers, days, hours, months: any step you give it.
Question 4: You need to move cancelled orders from the orders table to an archive table. It must be safe: a row must never be lost, and never be copied twice. How do you do it in one statement?
Use a data-modifying CTE. DELETE ... RETURNING removes the rows and hands them over, and the main statement inserts exactly those rows into the archive. It is one statement, so it is atomic.
Two cancelled orders are in the table. The DELETE inside the CTE removes them, and RETURNING star passes the deleted rows along, like a small temporary table. The INSERT reads them and writes them to the archive. If anything fails, the whole statement rolls back, and nothing moved.
| order_id | status |
|---|---|
| 1003 | Delivered |
| 1004 | Cancelled |
| 1010 | Shipped |
| 1011 | Cancelled |
DELETE ... RETURNING *INSERT INTO orders_archive| order_id | status |
|---|---|
| 1004 | Cancelled |
| 1011 | Cancelled |
Here is the statement. The CTE deletes and returns. The INSERT selects from the CTE. Postgres answers INSERT 0 2: two rows were archived.
WITH moved AS (
DELETE FROM orders
WHERE status = 'Cancelled'
RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved;Result: INSERT 0 2
And a quick check: ten orders are left, and two are in the archive.
SELECT (SELECT COUNT(*) FROM orders) AS orders_left,
(SELECT COUNT(*) FROM orders_archive) AS archived;| orders_left | archived |
|---|---|
| 10 | 2 |
One detail for the interview. All parts of the statement see the same snapshot of the data. So the main query cannot see the changes made by the CTE in the table itself. It can only see them through RETURNING.
The parts share one snapshot. Pass changed rows along with RETURNING.
Question 5: Marketing wants the list of customers who have at least one delivered order. Your JOIN returns Omar and Mei twice. How do you fix it properly?
You do not need the order rows. You only need to know that one exists. Use EXISTS. Postgres runs it as a semi join: each customer comes back at most once. Adding DISTINCT only hides the problem.
Look at what the JOIN does. Sara has one delivered order: one row. Omar has two, so he comes back twice. Mei has two, so she comes back twice as well. EXISTS asks a yes or no question for each customer. Is there a delivered order? Yes. Then keep the customer once, and stop looking.
| customer_id | name |
|---|---|
| 1 | Sara Khan |
| 2 | Omar Ali |
| 4 | Mei Chen |
| order_id | customer_id |
|---|---|
| 1001 | 1 |
| 1002 | 2 |
| 1008 | 2 |
| 1003 | 4 |
| 1009 | 4 |
First the JOIN. Eight rows for six customers. Omar and Mei appear twice.
SELECT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'Delivered'
ORDER BY c.customer_id;| customer_id | name |
|---|---|
| 1 | Sara Khan |
| 2 | Omar Ali |
| 2 | Omar Ali |
| 3 | Lina Park |
| 4 | Mei Chen |
| 4 | Mei Chen |
| 5 | Ravi Patel |
| 7 | Sara K. |
Now EXISTS. Six rows, one per customer. And the plan confirms it: a semi join.
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
AND o.status = 'Delivered')
ORDER BY c.customer_id;| customer_id | name |
|---|---|
| 1 | Sara Khan |
| 2 | Omar Ali |
| 3 | Lina Park |
| 4 | Mei Chen |
| 5 | Ravi Patel |
| 7 | Sara K. |
EXPLAIN (COSTS OFF)
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
AND o.status = 'Delivered');Hash Semi Join
Hash Cond: (c.customer_id = o.customer_id)
-> Seq Scan on customers c
-> Hash
-> Seq Scan on orders o
Filter: ((status)::text = 'Delivered'::text)IN with a subquery gives the same semi join here. I still prefer EXISTS, because it also works with more than one column, and its opposite, NOT EXISTS, is safe with NULLs.
IN (subquery) → same semi join. But only NOT EXISTS is NULL-safe.
Question 6: A recursive query on a parent and child table never finishes, and it fills the disk with temporary files. What happened, and how do you make the query safe?
The data has a cycle: A leads to B, B to C, and C back to A, so every step finds new rows forever. From PostgreSQL 14, add the CYCLE clause. It tracks the path and stops when a row is visited twice.
Follow the links. A goes to B. B goes to C. C goes to D, which is fine, but C also goes back to A. Now the recursion starts again from A, and again, and again. Nothing in the query says stop.
We can see the loop safely with a LIMIT. After the fourth row, the same links start to repeat. Without the LIMIT, this would run until the disk is full.
WITH RECURSIVE walk AS (
SELECT src, dst FROM links WHERE src = 'A'
UNION ALL
SELECT l.src, l.dst
FROM links l
JOIN walk w ON l.src = w.dst
)
SELECT * FROM walk
LIMIT 8;| src | dst |
|---|---|
| A | B |
| B | C |
| C | A |
| C | D |
| A | B |
| B | C |
| C | A |
| C | D |
Now the same query with the CYCLE clause, and no LIMIT. Postgres adds two columns: the path so far, and a flag. When the walk arrives at B for the second time, it marks that row as a cycle and does not continue from it. The query ends by itself.
WITH RECURSIVE walk AS (
SELECT src, dst FROM links WHERE src = 'A'
UNION ALL
SELECT l.src, l.dst
FROM links l
JOIN walk w ON l.src = w.dst
) CYCLE dst SET is_cycle USING path
SELECT src, dst, is_cycle, path
FROM walk;| src | dst | is_cycle | path |
|---|---|---|---|
| A | B | f | {(B)} |
| B | C | f | {(B),(C)} |
| C | A | f | {(B),(C),(A)} |
| C | D | f | {(B),(C),(D)} |
| A | B | t | {(B),(C),(A),(B)} |
On older versions, keep an array of visited IDs yourself and stop when the next ID is already in it. And as a seat belt, a statement timeout protects the server from any query that runs away.
Before 14: carry an array of visited IDs. Always: set a statement_timeout.
Question 7: Finance wants, for every manager, the number of people under them at any level, and the total salary of that whole team. How do you roll a hierarchy up?
Start the recursion from every employee as their own root, and carry the root's ID down the tree. Then GROUP BY the root: each manager gets one row for their whole subtree.
Take Omar as the root. The walk goes down from him and finds Lina, Ravi and Mei. Every row it finds is stamped with the root: Omar. Take Sara as the root, and it finds Daniel and Fatima. Take Aisha, and it finds everybody. Then we group by the stamp and add up.
Here is the query. The anchor has no WHERE clause: every employee starts as a root. The recursive step walks down and keeps the root ID. The outer query groups by the root, and subtracts the manager's own row.
WITH RECURSIVE sub AS (
SELECT emp_id AS root_id, emp_id, salary
FROM employees
UNION ALL
SELECT s.root_id, e.emp_id, e.salary
FROM sub s
JOIN employees e ON e.manager_id = s.emp_id
)
SELECT m.name AS manager,
COUNT(*) - 1 AS people_below,
SUM(s.salary) - m.salary AS payroll_below
FROM sub s
JOIN employees m ON m.emp_id = s.root_id
GROUP BY m.emp_id, m.name, m.salary
HAVING COUNT(*) > 1
ORDER BY people_below DESC;| manager | people_below | payroll_below |
|---|---|---|
| Aisha | 9 | 760000 |
| Omar | 3 | 255000 |
| Sara | 2 | 165000 |
| John | 1 | 55000 |
Aisha has nine people and a payroll of 760,000 under her. Omar has three, Sara has two, and John has one. On a very large hierarchy this self-expanding walk gets heavy, so teams often store the result in a closure table, or use the ltree extension.
Huge trees: precompute a closure table, or store paths with ltree.
Key points to remember
- Since PostgreSQL 12 a simple CTE is inlined. Use MATERIALIZED to force the old behaviour
- Recursive CTE = anchor UNION ALL recursive step, repeated until no new rows
- generate_series builds the calendar, a LEFT JOIN fills the gaps
- DELETE ... RETURNING inside a CTE moves rows in one atomic statement
- Use EXISTS for "has at least one", and CYCLE to stop loops in the data
The full SQL script
Here is everything in one script: the sample data and every query from this post, in order.
Show the full SQL script (sample data and every query)
-- ========================================================================
-- PostgreSQL Interview Q&A #4 - CTEs and recursive queries
-- TechBrothersIT - run on PostgreSQL 16
-- ========================================================================
-- Sample data
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
INSERT INTO departments VALUES (10, 'Sales'), (20, 'Engineering'), (30, 'HR'), (40, 'Finance');
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT, -- NULL = not assigned yet
manager_id INT, -- NULL = top of the company
salary INT,
hire_date DATE
);
INSERT INTO employees VALUES
(1, 'Aisha', NULL, NULL, 150000, '2018-03-01'),
(2, 'Omar', 20, 1, 120000, '2019-06-15'),
(3, 'Lina', 20, 2, 90000, '2021-01-10'),
(4, 'Ravi', 20, 2, 90000, '2022-04-05'),
(5, 'Mei', 20, 2, 75000, '2024-09-01'),
(6, 'Sara', 10, 1, 95000, '2020-02-20'),
(7, 'Daniel', 10, 6, 105000, '2021-07-01'),
(8, 'Fatima', 10, 6, 60000, '2023-11-12'),
(9, 'John', 30, 1, 70000, '2019-09-30'),
(10,'Zara', NULL, 9, 55000, '2026-01-05');
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(20),
city VARCHAR(20),
region VARCHAR(10), -- NULL = unknown
email VARCHAR(40)
);
INSERT INTO customers VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@mail.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@mail.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@mail.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@mail.com'),
(5, 'Ravi Patel', 'Austin', NULL, 'ravi@mail.com'),
(6, 'Chen Wu', 'Denver', 'West', 'chen@mail.com'),
(7, 'Sara K.', 'Toronto', 'East', 'sara@mail.com');
CREATE TABLE products (product_id INT PRIMARY KEY, product VARCHAR(20), category VARCHAR(15), price NUMERIC(10,2));
INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1200.00),
(2, 'Monitor', 'Electronics', 300.00),
(3, 'Keyboard', 'Accessories', 50.00),
(4, 'Mouse', 'Accessories', 25.00),
(5, 'Desk', 'Furniture', 400.00),
(6, 'Chair', 'Furniture', 250.00);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(10),
amount NUMERIC(10,2)
);
INSERT INTO orders VALUES
(1001, 1, '2026-01-05', 'Delivered', 1250.00),
(1002, 2, '2026-01-18', 'Delivered', 300.00),
(1003, 4, '2026-02-02', 'Delivered', 650.00),
(1004, 1, '2026-02-14', 'Cancelled', 50.00),
(1005, 3, '2026-02-20', 'Delivered', 1200.00),
(1006, 4, '2026-03-03', 'Shipped', 275.00),
(1007, 5, '2026-03-15', 'Delivered', 400.00),
(1008, 2, '2026-04-01', 'Delivered', 75.00),
(1009, 4, '2026-04-22', 'Delivered', 250.00),
(1010, 1, '2026-05-09', 'Shipped', 300.00),
(1011, 3, '2026-05-30', 'Cancelled', 400.00),
(1012, 7, '2026-06-11', 'Delivered', 50.00);
CREATE TABLE order_items (order_id INT, product_id INT, qty INT, unit_price NUMERIC(10,2));
INSERT INTO order_items VALUES
(1001, 1, 1, 1200.00), (1001, 3, 1, 50.00),
(1002, 2, 1, 300.00),
(1003, 5, 1, 400.00), (1003, 6, 1, 250.00),
(1004, 3, 1, 50.00),
(1005, 1, 1, 1200.00),
(1006, 6, 1, 250.00), (1006, 4, 1, 25.00),
(1007, 5, 1, 400.00),
(1008, 4, 3, 25.00),
(1009, 6, 1, 250.00),
(1010, 2, 1, 300.00),
(1011, 5, 1, 400.00),
(1012, 3, 1, 50.00);
CREATE TABLE payments (payment_id INT PRIMARY KEY, order_id INT, paid_amount NUMERIC(10,2));
INSERT INTO payments VALUES
(1, 1001, 1250.00), (2, 1002, 300.00),
(3, 1003, 400.00), (4, 1003, 250.00), -- order 1003 was paid in two parts
(5, 1005, 1200.00), (6, 1007, 400.00),
(7, 1008, 75.00), (8, 1009, 250.00), (9, 1012, 50.00);
-- a bigger table, so the planner has a real choice between an index and a full scan
CREATE TABLE big_orders AS
SELECT g AS order_id, (g % 1000) + 1 AS customer_id, (g % 500) + 10 AS amount
FROM generate_series(1, 100000) AS g;
ALTER TABLE big_orders ADD PRIMARY KEY (order_id);
ANALYZE big_orders;
CREATE TABLE daily_sales (sale_id INT PRIMARY KEY, sale_date DATE, amount INT);
INSERT INTO daily_sales VALUES
(1, '2026-06-01', 100), (2, '2026-06-02', 200), (3, '2026-06-02', 50),
(4, '2026-06-04', 300), (5, '2026-06-07', 150); -- no sales on June 3, 5 and 6
CREATE TABLE orders_archive (LIKE orders INCLUDING ALL);
CREATE TABLE links (src TEXT, dst TEXT);
INSERT INTO links VALUES ('A', 'B'), ('B', 'C'), ('C', 'A'), ('C', 'D'); -- C points back to A: a cycle
-- Step 1 (question 1): cte inlined
EXPLAIN (COSTS OFF)
WITH o AS (
SELECT * FROM big_orders
)
SELECT * FROM o
WHERE order_id = 42;
-- Step 2 (question 1): cte materialized
EXPLAIN (COSTS OFF)
WITH o AS MATERIALIZED (
SELECT * FROM big_orders
)
SELECT * FROM o
WHERE order_id = 42;
-- Step 3 (question 2): org chart
WITH RECURSIVE org AS (
SELECT emp_id, name, 1 AS level, name::text AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.name, o.level + 1,
o.path || ' > ' || e.name
FROM employees e
JOIN org o ON e.manager_id = o.emp_id
)
SELECT level, name, path
FROM org
ORDER BY path;
-- Step 4 (question 3): fill missing days
SELECT d::date AS day,
COALESCE(SUM(s.amount), 0) AS sales
FROM generate_series(DATE '2026-06-01',
DATE '2026-06-07',
INTERVAL '1 day') AS d
LEFT JOIN daily_sales s ON s.sale_date = d::date
GROUP BY d
ORDER BY d;
-- Step 5 (question 4): archive cancelled
WITH moved AS (
DELETE FROM orders
WHERE status = 'Cancelled'
RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved;
-- Step 6 (question 4): check counts
SELECT (SELECT COUNT(*) FROM orders) AS orders_left,
(SELECT COUNT(*) FROM orders_archive) AS archived;
-- Step 7 (question 5): join duplicates
SELECT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'Delivered'
ORDER BY c.customer_id;
-- Step 8 (question 5): exists semi join
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
AND o.status = 'Delivered')
ORDER BY c.customer_id;
-- Step 9 (question 5): exists plan
EXPLAIN (COSTS OFF)
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
AND o.status = 'Delivered');
-- Step 10 (question 6): endless walk
WITH RECURSIVE walk AS (
SELECT src, dst FROM links WHERE src = 'A'
UNION ALL
SELECT l.src, l.dst
FROM links l
JOIN walk w ON l.src = w.dst
)
SELECT * FROM walk
LIMIT 8;
-- Step 11 (question 6): safe walk
WITH RECURSIVE walk AS (
SELECT src, dst FROM links WHERE src = 'A'
UNION ALL
SELECT l.src, l.dst
FROM links l
JOIN walk w ON l.src = w.dst
) CYCLE dst SET is_cycle USING path
SELECT src, dst, is_cycle, path
FROM walk;
-- Step 12 (question 7): team rollup
WITH RECURSIVE sub AS (
SELECT emp_id AS root_id, emp_id, salary
FROM employees
UNION ALL
SELECT s.root_id, e.emp_id, e.salary
FROM sub s
JOIN employees e ON e.manager_id = s.emp_id
)
SELECT m.name AS manager,
COUNT(*) - 1 AS people_below,
SUM(s.salary) - m.salary AS payroll_below
FROM sub s
JOIN employees m ON m.emp_id = s.root_id
GROUP BY m.emp_id, m.name, m.salary
HAVING COUNT(*) > 1
ORDER BY people_below DESC;
Part 5: Indexes
The sample data on this page is invented for teaching. Every output table and query plan is the real result of the query above it, run on PostgreSQL 16 over that sample data.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.