In this post we are going to cover subqueries, CTEs and recursive queries. These are 7 real SQL interview questions, asked the way they come up on the job, and the last one is a scenario where a manager wants one customer summary that is still readable a month later.
Interviewers ask about subqueries and CTEs because they show how you structure logic, and because two classic traps live here: NOT IN with a NULL, and a NULL key that never matches.
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 SQL Server RealTime Questions Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: A teammate's query repeats the same spend calculation in two nested subqueries. What is the difference between a subquery and a CTE, and when would you rewrite it as a CTE?
- Question 2: HR wants the employees who earn more than the average of their own department. How do you write it with a correlated subquery?
- Question 3: You need the customers who have at least one Shipped order. Would you use IN or EXISTS, and what is the real difference?
- Question 4: You look for employees who manage nobody with NOT IN against manager_id, and the query returns zero rows. Why, and how do you fix it?
- Question 5: HR wants the full org chart: every employee, their level and the reporting path from the CEO. How do you build it in SQL?
- Question 6: A monthly revenue chart is missing May because nothing was delivered that month. How do you generate every month so empty months still show 0?
- Scenario 7: A manager wants one customer summary with total spent, number of orders, last order date and favorite category, including customers who never ordered. How do you build it so it stays readable?
- The full SQL script
The short answer
A subquery is a query nested inside another; a CTE (WITH ...) is a named subquery that lives for one statement, so complex logic reads top to bottom and can be reused. EXISTS stops at the first match, NOT IN returns nothing if the list contains a NULL (use NOT EXISTS), and a recursive CTE walks hierarchies and generates series.
Keep that in mind. Every question below is one piece of it.
Let's create some sample data
We use a small shop database called TB Shop: departments, employees, customers, products, orders, order items and payments. The script is written for PostgreSQL, and it runs on SQL Server, MySQL 8 or Snowflake with very small changes. Run it once and every query in this post works.
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
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 DECIMAL(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 DECIMAL(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 DECIMAL(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 DECIMAL(10,2));
INSERT INTO payments VALUES
(1, 1001, 1250.00), (2, 1002, 300.00), (3, 1003, 400.00), (4, 1003, 250.00), (5, 1005, 1200.00);Here are the employees. Aisha, the CEO, has no manager and no department, and Zara has no department either. Both of these NULLs matter later.
| emp_id | name | dept_id | manager_id | salary | hire_date |
|---|---|---|---|---|---|
| 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 |
And here are the orders. Notice May: order 1010 is Shipped and order 1011 is Cancelled, so May has no delivered order at all.
| order_id | customer_id | order_date | status | amount |
|---|---|---|---|---|
| 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 |
Question 1: A teammate's query repeats the same spend calculation in two nested subqueries. What is the difference between a subquery and a CTE, and when would you rewrite it as a CTE?
A subquery is a query nested inside another query. A CTE, a common table expression, is the same thing with a name: you define it up front with WITH, and it only lives for that one statement.
Let's ask the same question both ways: which customers spend more than the average customer? Here is the subquery version first.
SELECT c.name, s.total_spent
FROM (SELECT customer_id, SUM(amount) AS total_spent
FROM orders
WHERE status <> 'Cancelled'
GROUP BY customer_id) s
JOIN customers c ON c.customer_id = s.customer_id
WHERE s.total_spent > (
SELECT AVG(t.total_spent)
FROM (SELECT SUM(amount) AS total_spent
FROM orders
WHERE status <> 'Cancelled'
GROUP BY customer_id) t)
ORDER BY s.total_spent DESC;| name | total_spent |
|---|---|
| Sara Khan | 1550.00 |
| Lina Park | 1200.00 |
| Mei Chen | 1175.00 |
It works, but the spend per customer is written twice. Now the CTE version.
WITH spend AS (
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
WHERE status <> 'Cancelled'
GROUP BY customer_id
)
SELECT c.name, s.total_spent
FROM spend s
JOIN customers c ON c.customer_id = s.customer_id
WHERE s.total_spent > (SELECT AVG(total_spent) FROM spend)
ORDER BY s.total_spent DESC;| name | total_spent |
|---|---|
| Sara Khan | 1550.00 |
| Lina Park | 1200.00 |
| Mei Chen | 1175.00 |
Same three customers: Sara Khan with 1550.00, Lina Park with 1200.00 and Mei Chen with 1175.00. The difference is that spend is defined once and used twice, and the query reads top to bottom.
What about performance? Usually it is similar. SQL Server always inlines a CTE, so one used twice is calculated twice. PostgreSQL inlines a CTE used once and calculates one used twice only once, and MATERIALIZED or NOT MATERIALIZED overrides that.
Choose a CTE for readability, for reuse inside one statement, and because recursion needs one. Do not promise the interviewer it is faster.
Question 2: HR wants the employees who earn more than the average of their own department. How do you write it with a correlated subquery?
This is a correlated subquery. The inner query refers to the outer row, e.dept_id, so logically it runs again for every employee, with that employee's department.
SELECT e.name, e.dept_id, e.salary,
(SELECT CAST(AVG(1.0 * e2.salary) AS DECIMAL(10,2))
FROM employees e2
WHERE e2.dept_id = e.dept_id) AS dept_avg
FROM employees e
WHERE e.salary > (SELECT AVG(1.0 * e2.salary)
FROM employees e2
WHERE e2.dept_id = e.dept_id)
ORDER BY e.dept_id, e.salary DESC;| name | dept_id | salary | dept_avg |
|---|---|---|---|
| Daniel | 10 | 105000 | 86666.67 |
| Sara | 10 | 95000 | 86666.67 |
| Omar | 20 | 120000 | 93750.00 |
Daniel (105000) and Sara (95000) earn more than the Sales average of 86666.67. Omar, with 120000, is the only one above the Engineering average of 93750.00. John is the only person in HR, so he equals his own average, and > leaves him out.
Now look at who is missing: Aisha and Zara. Their dept_id is NULL, and NULL = NULL is not true, so the inner query finds no rows and they are never compared. If the business wants them in the report, give them their own group on purpose.
We also used AVG(1.0 * salary), because SQL Server returns an integer AVG for an integer column.
AVG(salary) OVER (PARTITION BY dept_id) is a window function that calculates every department's average in a single pass. We use it later in this series.
Question 3: You need the customers who have at least one Shipped order. Would you use IN or EXISTS, and what is the real difference?
Both answer this question. IN builds the list of values and checks membership. EXISTS asks a yes or no question per row and stops at the first match. Let's start with IN.
SELECT c.customer_id, c.name
FROM customers c
WHERE c.customer_id IN (SELECT o.customer_id
FROM orders o
WHERE o.status = 'Shipped')
ORDER BY c.customer_id;| customer_id | name |
|---|---|
| 1 | Sara Khan |
| 4 | Mei Chen |
Now the same question with EXISTS. It is correlated: for each customer, is there at least one matching order? The select list inside does not matter, so SELECT 1 is the convention.
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 = 'Shipped')
ORDER BY c.customer_id;| customer_id | name |
|---|---|
| 1 | Sara Khan |
| 4 | Mei Chen |
Same two customers: Sara Khan and Mei Chen. Modern optimizers turn both into the same semi join, so on most databases the performance is the same.
| How it thinks | Best for | |
|---|---|---|
| IN | build the list, then check membership | a simple list of values |
| EXISTS | yes or no per row, stops at the first match | several columns, any condition |
The real difference: the negative forms behave very differently with NULLs. That is the next question.
Question 4: You look for employees who manage nobody with NOT IN against manager_id, and the query returns zero rows. Why, and how do you fix it?
Because the list contains a NULL. Aisha, the CEO, has a NULL manager_id, and once a NULL is in the list, NOT IN can never be true.
Here is the natural query: every employee whose emp_id is not in the list of manager IDs.
SELECT emp_id, name
FROM employees
WHERE emp_id NOT IN (SELECT manager_id
FROM employees)
ORDER BY emp_id;| emp_id | name |
|---|
Zero rows. Not one. x NOT IN (1, 2, NULL) means x <> 1 AND x <> 2 AND x <> NULL. Any comparison with NULL is unknown, not true, so the whole condition is never true for any employee.
The fix is NOT EXISTS. It only asks whether a matching row exists, so a NULL cannot hide every row.
SELECT e.emp_id, e.name
FROM employees e
WHERE NOT EXISTS (SELECT 1
FROM employees m
WHERE m.manager_id = e.emp_id)
ORDER BY e.emp_id;| emp_id | name |
|---|---|
| 3 | Lina |
| 4 | Ravi |
| 5 | Mei |
| 7 | Daniel |
| 8 | Fatima |
| 10 | Zara |
Six employees manage nobody: Lina, Ravi, Mei, Daniel, Fatima and Zara.
Use NOT EXISTS by default. If you must use NOT IN, add WHERE manager_id IS NOT NULL inside the subquery.
Question 5: HR wants the full org chart: every employee, their level and the reporting path from the CEO. How do you build it in SQL?
With a recursive CTE. It has two parts joined by UNION ALL: an anchor that returns the starting row, and a recursive part that joins back to the rows found so far.
Here the anchor is the employee with no manager, Aisha, at level 1. The recursive part finds whoever reports to someone already in the result, adds 1 to the level and appends the name to the path. We cast the path to VARCHAR(200) in both parts, because their types must match.
WITH RECURSIVE org AS (
-- anchor: the CEO, who has no manager
SELECT emp_id, name, 1 AS lvl,
CAST(name AS VARCHAR(200)) AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- recursive part: direct reports of the rows found so far
SELECT e.emp_id, e.name, o.lvl + 1,
CAST(CONCAT(o.path, ' > ', e.name) AS VARCHAR(200))
FROM employees e
JOIN org o ON e.manager_id = o.emp_id
)
SELECT emp_id, name, lvl, path
FROM org
ORDER BY path;| emp_id | name | lvl | path |
|---|---|---|---|
| 1 | Aisha | 1 | Aisha |
| 9 | John | 2 | Aisha > John |
| 10 | Zara | 3 | Aisha > John > Zara |
| 2 | Omar | 2 | Aisha > Omar |
| 3 | Lina | 3 | Aisha > Omar > Lina |
| 5 | Mei | 3 | Aisha > Omar > Mei |
| 4 | Ravi | 3 | Aisha > Omar > Ravi |
| 6 | Sara | 2 | Aisha > Sara |
| 7 | Daniel | 3 | Aisha > Sara > Daniel |
| 8 | Fatima | 3 | Aisha > Sara > Fatima |
It stops when a pass finds no new rows. Omar, Sara and John are at level 2, and the other six are at level 3, each with the full path from the CEO, like Aisha > Omar > Lina. Ordering by the path even gives you a tree view.
Dialect notes: SQL Server writes it without the RECURSIVE keyword, and it fails with an error after 100 levels unless you set OPTION (MAXRECURSION n).
In a real project, bad data with a loop in it would never end. Add a level limit in the recursive part.
Question 6: A monthly revenue chart is missing May because nothing was delivered that month. How do you generate every month so empty months still show 0?
Generate the months first, then LEFT JOIN the data to them. GROUP BY can only return groups that have rows.
Let's see the problem. Here is delivered revenue by month.
SELECT EXTRACT(MONTH FROM order_date) AS month_no,
COUNT(*) AS delivered_orders,
SUM(amount) AS revenue
FROM orders
WHERE status = 'Delivered'
GROUP BY EXTRACT(MONTH FROM order_date)
ORDER BY month_no;| month_no | delivered_orders | revenue |
|---|---|---|
| 1 | 2 | 1550.00 |
| 2 | 2 | 1850.00 |
| 3 | 1 | 400.00 |
| 4 | 2 | 325.00 |
| 6 | 1 | 50.00 |
Months 1, 2, 3, 4 and 6. May is missing, because its two orders were Shipped and Cancelled. A chart built on this skips May, and nobody notices.
Now the fix. A recursive CTE starts at 2026-01-01 and adds one month per pass until June. Then we LEFT JOIN the orders to the months.
WITH RECURSIVE months (month_start) AS (
SELECT CAST('2026-01-01' AS DATE)
UNION ALL
SELECT CAST(month_start + INTERVAL '1' MONTH AS DATE)
FROM months
WHERE month_start < CAST('2026-06-01' AS DATE)
)
SELECT m.month_start,
COUNT(o.order_id) AS delivered_orders,
CAST(COALESCE(SUM(o.amount), 0) AS DECIMAL(10,2)) AS revenue
FROM months m
LEFT JOIN orders o
ON o.order_date >= m.month_start
AND o.order_date < m.month_start + INTERVAL '1' MONTH
AND o.status = 'Delivered'
GROUP BY m.month_start
ORDER BY m.month_start;| month_start | delivered_orders | revenue |
|---|---|---|
| 2026-01-01 | 2 | 1550.00 |
| 2026-02-01 | 2 | 1850.00 |
| 2026-03-01 | 1 | 400.00 |
| 2026-04-01 | 2 | 325.00 |
| 2026-05-01 | 0 | 0.00 |
| 2026-06-01 | 1 | 50.00 |
May is there now, with 0 delivered orders and 0.00 revenue. Three details make it work. The status filter sits in the ON clause, not in WHERE, so the empty month survives the LEFT JOIN. COUNT(o.order_id) counts only matched rows, so it gives 0. And COALESCE turns the empty SUM into 0.
In SQL Server, use DATEADD(month, 1, month_start) and drop RECURSIVE. PostgreSQL also has generate_series, and in a warehouse you simply join to the date dimension.
Scenario 7: A manager wants one customer summary with total spent, number of orders, last order date and favorite category, including customers who never ordered. How do you build it so it stays readable?
Do not write one giant query. Write chained CTEs, one business rule each.
totals: the count, the sum and the latest date per customer, without cancelled orders.cat_spend: the order lines joined to products, summed per customer and category.fav: ROW_NUMBER per customer, highest spend first, with the category name as a tie-breaker so the answer is always the same.
WITH totals AS (
SELECT customer_id, COUNT(*) AS order_count,
SUM(amount) AS total_spent, MAX(order_date) AS last_order
FROM orders WHERE status <> 'Cancelled'
GROUP BY customer_id),
cat_spend AS (
SELECT o.customer_id, p.category, SUM(oi.qty * oi.unit_price) AS spent
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status <> 'Cancelled'
GROUP BY o.customer_id, p.category),
fav AS (
SELECT customer_id, category, ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY spent DESC, category) AS rn
FROM cat_spend)
SELECT c.name, COALESCE(t.order_count, 0) AS orders,
COALESCE(t.total_spent, 0.00) AS total_spent,
t.last_order, f.category AS favorite
FROM customers c
LEFT JOIN totals t ON t.customer_id = c.customer_id
LEFT JOIN fav f ON f.customer_id = c.customer_id AND f.rn = 1
ORDER BY total_spent DESC, c.name;| name | orders | total_spent | last_order | favorite |
|---|---|---|---|---|
| Sara Khan | 2 | 1550.00 | 2026-05-09 | Electronics |
| Lina Park | 1 | 1200.00 | 2026-02-20 | Electronics |
| Mei Chen | 3 | 1175.00 | 2026-04-22 | Furniture |
| Ravi Patel | 1 | 400.00 | 2026-03-15 | Furniture |
| Omar Ali | 2 | 375.00 | 2026-04-01 | Electronics |
| Sara K. | 1 | 50.00 | 2026-06-11 | Accessories |
| Chen Wu | 0 | 0.00 | NULL | NULL |
Sara Khan leads with 1550.00 from 2 orders, last order 2026-05-09, favorite Electronics. Mei Chen has 3 orders and her favorite is Furniture.
The final SELECT starts from customers with LEFT JOINs. That is why Chen Wu is still in the list, with 0 orders, 0.00 spent and no favorite. A manager would rather see a zero than lose a customer.
And because every CTE is one step, you can run each one on its own when a number looks wrong.
Key points to remember
- A CTE is a named subquery for one statement: readable, reusable
- Correlated subqueries run per outer row; NULL keys never match
- EXISTS stops at the first match; IN checks a list
- NOT IN + a NULL = no rows: use NOT EXISTS
- Recursive CTEs walk hierarchies and generate series
The full SQL script
Here is everything in one script: the sample data and every query from this post, in order. Copy it and run it top to bottom.
Show the full SQL script (sample data and every query)
-- ========================================================================
-- SQL Interview Q&A #4 - Subqueries, CTEs and recursive queries
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16 (and cross-checked on DuckDB). Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA sql_q04; SET search_path TO sql_q04;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
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 DECIMAL(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 DECIMAL(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 DECIMAL(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 DECIMAL(10,2));
INSERT INTO payments VALUES
(1, 1001, 1250.00), (2, 1002, 300.00), (3, 1003, 400.00), (4, 1003, 250.00), (5, 1005, 1200.00);
-- Step 1: Subquery version: customers who spend more than the average customer
SELECT c.name, s.total_spent
FROM (SELECT customer_id, SUM(amount) AS total_spent
FROM orders
WHERE status <> 'Cancelled'
GROUP BY customer_id) s
JOIN customers c ON c.customer_id = s.customer_id
WHERE s.total_spent > (
SELECT AVG(t.total_spent)
FROM (SELECT SUM(amount) AS total_spent
FROM orders
WHERE status <> 'Cancelled'
GROUP BY customer_id) t)
ORDER BY s.total_spent DESC;
-- Step 2: CTE version: define spend once, use it twice
WITH spend AS (
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
WHERE status <> 'Cancelled'
GROUP BY customer_id
)
SELECT c.name, s.total_spent
FROM spend s
JOIN customers c ON c.customer_id = s.customer_id
WHERE s.total_spent > (SELECT AVG(total_spent) FROM spend)
ORDER BY s.total_spent DESC;
-- Step 3: Correlated subquery: earn more than their own department average
SELECT e.name, e.dept_id, e.salary,
(SELECT CAST(AVG(1.0 * e2.salary) AS DECIMAL(10,2))
FROM employees e2
WHERE e2.dept_id = e.dept_id) AS dept_avg
FROM employees e
WHERE e.salary > (SELECT AVG(1.0 * e2.salary)
FROM employees e2
WHERE e2.dept_id = e.dept_id)
ORDER BY e.dept_id, e.salary DESC;
-- Step 4: IN: customers with an order still in Shipped status
SELECT c.customer_id, c.name
FROM customers c
WHERE c.customer_id IN (SELECT o.customer_id
FROM orders o
WHERE o.status = 'Shipped')
ORDER BY c.customer_id;
-- Step 5: EXISTS: the same question, asked per customer
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 = 'Shipped')
ORDER BY c.customer_id;
-- Step 6: The trap: NOT IN against a list that contains NULL returns no rows
SELECT emp_id, name
FROM employees
WHERE emp_id NOT IN (SELECT manager_id
FROM employees)
ORDER BY emp_id;
-- Step 7: The fix: NOT EXISTS ignores NULLs
SELECT e.emp_id, e.name
FROM employees e
WHERE NOT EXISTS (SELECT 1
FROM employees m
WHERE m.manager_id = e.emp_id)
ORDER BY e.emp_id;
-- Step 8: Recursive CTE: org chart with level and path from the CEO
WITH RECURSIVE org AS (
-- anchor: the CEO, who has no manager
SELECT emp_id, name, 1 AS lvl,
CAST(name AS VARCHAR(200)) AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- recursive part: direct reports of the rows found so far
SELECT e.emp_id, e.name, o.lvl + 1,
CAST(CONCAT(o.path, ' > ', e.name) AS VARCHAR(200))
FROM employees e
JOIN org o ON e.manager_id = o.emp_id
)
SELECT emp_id, name, lvl, path
FROM org
ORDER BY path;
-- Step 9: Delivered revenue by month: May is missing
SELECT EXTRACT(MONTH FROM order_date) AS month_no,
COUNT(*) AS delivered_orders,
SUM(amount) AS revenue
FROM orders
WHERE status = 'Delivered'
GROUP BY EXTRACT(MONTH FROM order_date)
ORDER BY month_no;
-- Step 10: Recursive CTE generates every month, LEFT JOIN fills the gap
WITH RECURSIVE months (month_start) AS (
SELECT CAST('2026-01-01' AS DATE)
UNION ALL
SELECT CAST(month_start + INTERVAL '1' MONTH AS DATE)
FROM months
WHERE month_start < CAST('2026-06-01' AS DATE)
)
SELECT m.month_start,
COUNT(o.order_id) AS delivered_orders,
CAST(COALESCE(SUM(o.amount), 0) AS DECIMAL(10,2)) AS revenue
FROM months m
LEFT JOIN orders o
ON o.order_date >= m.month_start
AND o.order_date < m.month_start + INTERVAL '1' MONTH
AND o.status = 'Delivered'
GROUP BY m.month_start
ORDER BY m.month_start;
-- Step 11: Customer summary as chained CTEs
WITH totals AS (
SELECT customer_id, COUNT(*) AS order_count,
SUM(amount) AS total_spent, MAX(order_date) AS last_order
FROM orders WHERE status <> 'Cancelled'
GROUP BY customer_id),
cat_spend AS (
SELECT o.customer_id, p.category, SUM(oi.qty * oi.unit_price) AS spent
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status <> 'Cancelled'
GROUP BY o.customer_id, p.category),
fav AS (
SELECT customer_id, category, ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY spent DESC, category) AS rn
FROM cat_spend)
SELECT c.name, COALESCE(t.order_count, 0) AS orders,
COALESCE(t.total_spent, 0.00) AS total_spent,
t.last_order, f.category AS favorite
FROM customers c
LEFT JOIN totals t ON t.customer_id = c.customer_id
LEFT JOIN fav f ON f.customer_id = c.customer_id AND f.rn = 1
ORDER BY total_spent DESC, c.name;
Part 5: Ranking Window Functions
The sample data on this page is invented for teaching. Every output table is the real result of the query above it, run on PostgreSQL 16 over that sample data, and the portable queries were also checked on a second engine. Where SQL Server syntax is different, the post says so.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.