Week 01

Subqueries, CTEs & Set Operations

Day 62 to 3 hrs

What to Focus On

"Above the department average" sounds like one condition, but it needs two steps: calculate the average, then compare each employee against it. Subqueries and CTEs let you express those steps within one SQL statement.

Start by identifying the result you need before answering the main question:

Question	What you must compute first
Employees earning more than their department average	the average salary per department
Second highest salary in the company	the highest salary
Customers who ordered in July but not in August	the list of August customers
Products priced above their category average	the average price per category
Customers who never placed an order	the set of customers who did order

The useful distinction is the shape of that intermediate result: one value, a list of values, or a table. We will also compare complete query results with UNION, INTERSECT and EXCEPT.

Calculate the Comparison Value First

A subquery is a query nested inside another statement. For employees earning above the company average, the inner query returns the average and the outer query uses it as the cutoff.

We will use this employees table.

emp_id	name	dept	salary
1	Asha	IT	62000
2	Bala	IT	41000
3	Chetan	IT	55000
4	Divya	HR	38000
5	Farhan	HR	38000
6	Gita	Sales	47000
7	Harish	Sales	52000
8	Ishan	Sales	39000

The ask: "employees earning more than the company average".

Step 1. Compute the number alone. AVG(salary) is an aggregate function: it reads a set of rows and returns one number.

SQL

SELECT AVG(salary) FROM employees;

AVG(salary)
46500.0

Eight salaries went in, one number came out. The cutoff comes from the whole table rather than from one employee row.

Step 2. Plug that query in where the number would go.

SQL

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY emp_id;

name	salary
Asha	62000
Chetan	55000
Gita	47000
Harish	52000

The inner query ran once and collapsed to 46500, so the outer condition became salary > 46500, and four people cleared it. A subquery returning exactly one value like this is a scalar subquery, and you may write it anywhere a plain value is legal.

Gita earns less than Harish but appears before him because ORDER BY emp_id determines the sequence. The comparison column and the sort column need not be the same.

Where you put the subquery decides what it is allowed to return:

Where you write it	It must return	You use it for
`WHERE x > (...)`	one value (scalar)	comparing a row to an aggregate
`WHERE x IN (...)` or `EXISTS (...)`	a column, or any rows at all	membership, existence
`FROM (...) AS t`	a whole table (derived table)	aggregate first, then join back
`SELECT (...) AS c`	one value (scalar)	an extra computed column

Tip
Tip: If a MySQL or Postgres judge says "subquery returns more than 1 row", your scalar subquery is not scalar. Decide whether the question needs an aggregate such as MAX or AVG, membership with IN, or a more specific filter. Do not add an aggregate just to suppress the error.

The Pattern

Find the number or list that is not visible inside a single row. That is your inner query.
Decide its scope: one number for the whole table, or one per group.
Write the inner query alone and run it. Confirm the value before you nest anything.
Plug it in: WHERE to filter, FROM to join against, SELECT for an extra column.
Add the aliases and the ORDER BY the statement asks for.

Now change the comparison: employees earning more than their own department's average. The number changes per department, so the inner query has to know which department the current row belongs to.

SQL

SELECT name, dept, salary
FROM employees e
WHERE salary > (SELECT AVG(x.salary) FROM employees x WHERE x.dept = e.dept)
ORDER BY emp_id;

Read it line by line:

FROM employees e gives the table the short name e, so the inner query can point at its columns.
SELECT AVG(x.salary) FROM employees x opens the same table again as x and averages salaries in it.
WHERE x.dept = e.dept is the important line. It narrows that inner average to the department of the row we are looking at right now.
WHERE salary > (...) keeps the outer row only if its salary beats the number that came back.
ORDER BY emp_id puts the survivors in employee id order.

The comparison for each outer row:

outer row	dept	salary	inner AVG for that dept	keep?
Asha	IT	62000	52666.67	yes
Bala	IT	41000	52666.67	no
Chetan	IT	55000	52666.67	yes
Divya	HR	38000	38000.0	no, not strictly greater
Farhan	HR	38000	38000.0	no, not strictly greater
Gita	Sales	47000	46000.0	yes
Harish	Sales	52000	46000.0	yes
Ishan	Sales	39000	46000.0	no

There are three department averages, and each employee is compared with the relevant one.

A subquery that references an outer-query column is a correlated subquery. Repeated work can make it expensive on a large table, but the actual cost depends on the engine, indexes and execution plan.

Check the boundary. Both HR employees earn exactly their department average, so > excludes them. If the question says "at least the average", use >= instead.

Correct with `>`	Same query with `>=`
Asha, Chetan, Gita, Harish	Asha, Chetan, Divya, Farhan, Gita, Harish

Two extra names, four rows against six, and nothing in the error log to say which the setter wanted. The English picks the operator.

An alternative is to calculate one row per department and join that result back to employees. The subquery in FROM is called a derived table.

SQL

SELECT e.name, e.dept, e.salary
FROM employees e
JOIN (SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept) d
  ON d.dept = e.dept
WHERE e.salary > d.avg_sal
ORDER BY e.emp_id;

name	dept	salary
Asha	IT	62000
Chetan	IT	55000
Gita	Sales	47000
Harish	Sales	52000

The output is identical. This form makes the per-department aggregation explicit instead of expressing it as a per-employee check.

For a performance comparison, inspect the execution plans. The shorter query is not necessarily the faster one.

Whichever form you use, the clauses run in the order you learnt on Day 2. Here is a dry run on today's second table, the seven-row orders table printed in Variation 2. HAVING is the filter that runs after GROUP BY, on grouped rows, which is why a condition on SUM belongs there and not in WHERE.

SQL

SELECT customer, SUM(amount) AS total
FROM orders
WHERE order_date < '2026-08-01'
GROUP BY customer
HAVING SUM(amount) > (SELECT AVG(amount) FROM orders)
ORDER BY total DESC;

Step	What it does here	Rows left
FROM orders	all seven orders	7
WHERE order_date < '2026-08-01'	July orders only	4
GROUP BY customer	Ananya, Rahul, Sneha	3
HAVING SUM(amount) > 1131.5	Rahul's 890 is dropped	2
SELECT customer, SUM(amount)	builds the two output columns	2
ORDER BY total DESC	Sneha 2100, then Ananya 1680.5	2

Seven rows walk in and two walk out.

The subquery inside HAVING is uncorrelated. It is the average of all seven orders, 1131.5, computed once, and it does not care about the WHERE line above it.

find the hidden number
write the inner query alone and run it
plug it into WHERE / FROM / SELECT
fix aliases and ORDER BY

Check what you learned

In the department-average query above, why do both HR rows disappear?

Query Templates

SQL

-- 1. Scalar: every row against one number for the whole table.
SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY emp_id;

-- 2. Correlated: every row against its own group's number.
SELECT name, dept FROM employees e
WHERE e.salary > (SELECT AVG(x.salary) FROM employees x WHERE x.dept = e.dept);

-- 3. Derived table: average per department, then join back; includes equal salaries.
SELECT e.name, e.salary
FROM employees e
JOIN (SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept) d
  ON d.dept = e.dept
WHERE e.salary >= d.avg_sal
ORDER BY e.emp_id;

-- 4. Second highest distinct value with a scalar subquery.
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Template 4 is the subquery way to the answer you found on Day 1 with DISTINCT and LIMIT 1 OFFSET 1: the largest salary strictly below the maximum. It returns one row with NULL when there is no second value, with no extra wrapping.

The CTE chain, where each step gets a name with WITH, is built step by step in Variation 2 below.

Variations

Variation 1 - Membership with IN

When the inner query returns a list instead of a single value, compare with IN, which asks one simple thing: is this value present in that list?

The usual shape is two steps: find the groups that qualify, then pull every row belonging to them.

Step one, which departments have at least three people? COUNT(*) counts rows in a group, and the condition sits in HAVING because it is about the group, not the row.

SQL

SELECT dept FROM employees GROUP BY dept HAVING COUNT(*) >= 3;

dept
IT
Sales

HR has two employees, so it misses the cut and the list has two entries. Step two, feed that list to the outer query.

SQL

SELECT name, dept FROM employees
WHERE dept IN (SELECT dept FROM employees GROUP BY dept HAVING COUNT(*) >= 3)
ORDER BY emp_id;

name	dept
Asha	IT
Bala	IT
Chetan	IT
Gita	Sales
Harish	Sales
Ishan	Sales

All six people from IT and Sales come through, Divya and Farhan go out with their department. IN fits because the question is about membership in the set of qualifying departments.

Variation 2 - A two-step CTE pipeline

Second scenario, the orders table:

order_id	customer	order_date	amount
101	Ananya	2026-07-03	1250.50
102	Rahul	2026-07-08	890.00
103	Ananya	2026-07-19	430.00
104	Sneha	2026-07-27	2100.00
105	Rahul	2026-08-02	1500.00
106	Vikram	2026-08-11	760.00
107	Ananya	2026-08-23	990.00

The ask: "customers whose total spend is above the average customer's total spend". Two aggregations sit inside that one line, so two steps. Step one, total per customer.

SQL

SELECT customer, SUM(amount) AS total FROM orders GROUP BY customer ORDER BY total DESC;

customer	total
Ananya	2670.5
Rahul	2390.0
Sneha	2100.0
Vikram	760.0

Seven orders became four totals, whose average is 1980.13. that is not the average order value, it is the average of the totals.

Step two, name that result spend with WITH and filter it against its own average.

A CTE, short for common table expression, is a derived table that you name and write above the main query instead of inside it. Chain as many as you like, separated by commas, and each may read the ones defined before it. Use a CTE when naming an intermediate result makes the query easier to follow or reuse. It is not automatically faster: whether it is computed once or folded into the main query depends on the engine.

orders (7 rows)
spend: total per customer (4 rows)
average of those totals = 1980.13
big: totals above it (3 rows)
ORDER BY total DESC

SQL

WITH spend AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
), big AS (
    SELECT * FROM spend WHERE total > (SELECT AVG(total) FROM spend)
)
SELECT customer, printf('%.2f', total) AS total
FROM big
ORDER BY big.total DESC;

customer	total
Ananya	2670.50
Rahul	2390.00
Sneha	2100.00

Vikram's 760 is the only total below 1980.13, so his is the single row dropped.

Notice that spend is used twice, as the source of big and inside the subquery that averages it. That reuse is the real reason to prefer a CTE over pasting a derived table in two places.

The final sort uses big.total, the numeric total. The output alias total holds formatted text from printf; sorting that alias can put 900.00 before 2100.00.

Formatting the result. If the SQLite question requires two decimal places, the formatted rows should look like this:

Text

Ananya|2670.50
Rahul|2390.00
Sneha|2100.00

That is why the query uses printf('%.2f', total). Swap it for ROUND(total, 2) and you get this:

Text

Ananya|2670.5
Rahul|2390.0
Sneha|2100.0

The amounts are unchanged, but the trailing zeroes are missing. Use printf when the required result is text with exactly two decimal places.

Variation 3 - Set operations

Two SELECT statements with the same columns, in the same order, can be stacked and combined into one result.

Operator	Keeps	Duplicates
`UNION`	rows in either query	removed
`UNION ALL`	rows in either query	kept
`INTERSECT`	rows present in both	removed
`EXCEPT`	rows in the first, not in the second	removed

July customers are Ananya, Rahul and Sneha. August customers are Rahul, Vikram and Ananya. Same two branches, four operators:

SQL

SELECT customer FROM orders WHERE order_date LIKE '2026-07%'
INTERSECT
SELECT customer FROM orders WHERE order_date LIKE '2026-08%'
ORDER BY customer;

operator	output
`UNION`	Ananya, Rahul, Sneha, Vikram
`UNION ALL`	7 rows, with Ananya appearing three times
`INTERSECT`	Ananya, Rahul
`EXCEPT`	Sneha

Only the operator changes, and the row count swings from 1 to 7.

EXCEPT is the cleanest way to write "ordered in July but not in August". Swap the branches and the answer becomes Vikram, so branch order is part of the answer, not a style choice.

The trailing ORDER BY sorts the combined result. For a branch-specific ORDER BY with LIMIT, wrap that branch in a derived table or CTE before combining it. Output column names come from the first SELECT, so put your aliases there.

IMP
Choose based on duplicates: UNION removes repeated result rows, including repeats within either branch. UNION ALL retains them and avoids deduplication work. Even non-overlapping branches can contain duplicates of their own.

SQLite supports all four. MySQL got INTERSECT and EXCEPT only in 8.0.31, so in a MySQL interview offer the NOT EXISTS rewrite as your fallback.

Check what you learned

SELECT customer FROM orders WHERE amount > 1000 UNION ALL SELECT customer FROM orders WHERE amount < 500 ORDER BY customer. Where does the ORDER BY apply?

Common Mistakes

A scalar subquery returns several rows. SQLite uses the first row, while other engines may reject the query. Decide what single value the question actually needs. Do not add MAX just to make the query run.
NOT IN reads a nullable column. If the inner list contains even one NULL, NOT IN returns no rows at all, as Day 5 showed. Use NOT EXISTS, or remove the NULLs from the inner list with WHERE col IS NOT NULL.
The boundary is wrong. “Above average” needs >; “at least the average” needs >=. Include an equal-to-average row when checking your query.
The aggregation is at the wrong level. Average order amount and average customer total answer different questions. Write down what each intermediate row represents before taking another average.
The output format differs. When two decimal places are required as text, use SQLite's printf. ROUND changes the numeric precision but does not guarantee trailing zeroes.
Set-operation columns do not line up. Both branches need the same number of columns in corresponding positions. Compatible types matter too; SQLite accepting a mixture of names and numbers does not make the result meaningful.
A correlated query repeats expensive work. Check the plan and indexes before rewriting. A grouped derived table or CTE joined back to the base table is worth comparing, but the syntax alone does not guarantee a speedup.

Interview Notes

What is a correlated subquery? It references a column from the outer query. In our example, e.dept determines which salaries the inner AVG includes. Explain that dependency first; how often the engine evaluates it is an execution-plan question.

Is a join faster than a subquery? There is no general winner. The engine may turn different SQL forms into similar plans. Compare plans on representative data, including indexes and row counts, rather than deciding from the syntax.

IN or EXISTS? IN expresses membership; EXISTS expresses whether a matching row exists. For absence checks, remember that NOT IN and NOT EXISTS differ when NULLs are present. Positive IN still returns known matches even if its list contains NULL.

Does a CTE make a query faster? A CTE names an intermediate result for one statement. It helps organise and reuse a calculation. Whether that result is materialised or inlined, and whether this improves performance, depends on the engine and query.

UNION or UNION ALL? UNION removes duplicate result rows; UNION ALL keeps them. Use the required duplicate behaviour to choose. UNION ALL avoids deduplication work, but is not a substitute when the answer requires unique rows.

Second highest salary without window functions? SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees) gives 55000 here. It finds the second distinct salary and returns NULL if none exists. ORDER BY with OFFSET needs DISTINCT to handle ties and returns no row if the second value is absent.

Top row per group without window functions? Keep rows equal to a correlated group maximum: (SELECT MAX(v) FROM t x WHERE x.g = t.g). This retains all ties. If the question wants exactly one row per group, you need a rule for choosing between them.

Quick Test

Check what you learned

In the department-average query you change > to >=. Which extra rows appear?

SQL

SELECT name FROM employees e
WHERE salary >= (SELECT AVG(x.salary) FROM employees x WHERE x.dept = e.dept);

Week 01

Subqueries, CTEs & Set Operations

Day 62 to 3 hrs

What to Focus On

Start by identifying the result you need before answering the main question:

Question	What you must compute first
Employees earning more than their department average	the average salary per department
Second highest salary in the company	the highest salary
Customers who ordered in July but not in August	the list of August customers
Products priced above their category average	the average price per category
Customers who never placed an order	the set of customers who did order

The useful distinction is the shape of that intermediate result: one value, a list of values, or a table. We will also compare complete query results with UNION, INTERSECT and EXCEPT.

Calculate the Comparison Value First

A subquery is a query nested inside another statement. For employees earning above the company average, the inner query returns the average and the outer query uses it as the cutoff.

We will use this employees table.

emp_id	name	dept	salary
1	Asha	IT	62000
2	Bala	IT	41000
3	Chetan	IT	55000
4	Divya	HR	38000
5	Farhan	HR	38000
6	Gita	Sales	47000
7	Harish	Sales	52000
8	Ishan	Sales	39000

The ask: "employees earning more than the company average".

Step 1. Compute the number alone. AVG(salary) is an aggregate function: it reads a set of rows and returns one number.

SQL

SELECT AVG(salary) FROM employees;

AVG(salary)
46500.0

Eight salaries went in, one number came out. The cutoff comes from the whole table rather than from one employee row.

Step 2. Plug that query in where the number would go.

SQL

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY emp_id;

name	salary
Asha	62000
Chetan	55000
Gita	47000
Harish	52000

Gita earns less than Harish but appears before him because ORDER BY emp_id determines the sequence. The comparison column and the sort column need not be the same.

Where you put the subquery decides what it is allowed to return:

Where you write it	It must return	You use it for
`WHERE x > (...)`	one value (scalar)	comparing a row to an aggregate
`WHERE x IN (...)` or `EXISTS (...)`	a column, or any rows at all	membership, existence
`FROM (...) AS t`	a whole table (derived table)	aggregate first, then join back
`SELECT (...) AS c`	one value (scalar)	an extra computed column

Tip
Tip: If a MySQL or Postgres judge says "subquery returns more than 1 row", your scalar subquery is not scalar. Decide whether the question needs an aggregate such as MAX or AVG, membership with IN, or a more specific filter. Do not add an aggregate just to suppress the error.

The Pattern

Find the number or list that is not visible inside a single row. That is your inner query.
Decide its scope: one number for the whole table, or one per group.
Write the inner query alone and run it. Confirm the value before you nest anything.
Plug it in: WHERE to filter, FROM to join against, SELECT for an extra column.
Add the aliases and the ORDER BY the statement asks for.

SQL

SELECT name, dept, salary
FROM employees e
WHERE salary > (SELECT AVG(x.salary) FROM employees x WHERE x.dept = e.dept)
ORDER BY emp_id;

Read it line by line:

FROM employees e gives the table the short name e, so the inner query can point at its columns.
SELECT AVG(x.salary) FROM employees x opens the same table again as x and averages salaries in it.
WHERE x.dept = e.dept is the important line. It narrows that inner average to the department of the row we are looking at right now.
WHERE salary > (...) keeps the outer row only if its salary beats the number that came back.
ORDER BY emp_id puts the survivors in employee id order.

The comparison for each outer row:

outer row	dept	salary	inner AVG for that dept	keep?
Asha	IT	62000	52666.67	yes
Bala	IT	41000	52666.67	no
Chetan	IT	55000	52666.67	yes
Divya	HR	38000	38000.0	no, not strictly greater
Farhan	HR	38000	38000.0	no, not strictly greater
Gita	Sales	47000	46000.0	yes
Harish	Sales	52000	46000.0	yes
Ishan	Sales	39000	46000.0	no

There are three department averages, and each employee is compared with the relevant one.

Check the boundary. Both HR employees earn exactly their department average, so > excludes them. If the question says "at least the average", use >= instead.

Correct with `>`	Same query with `>=`
Asha, Chetan, Gita, Harish	Asha, Chetan, Divya, Farhan, Gita, Harish

Two extra names, four rows against six, and nothing in the error log to say which the setter wanted. The English picks the operator.

An alternative is to calculate one row per department and join that result back to employees. The subquery in FROM is called a derived table.

SQL

SELECT e.name, e.dept, e.salary
FROM employees e
JOIN (SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept) d
  ON d.dept = e.dept
WHERE e.salary > d.avg_sal
ORDER BY e.emp_id;

name	dept	salary
Asha	IT	62000
Chetan	IT	55000
Gita	Sales	47000
Harish	Sales	52000

The output is identical. This form makes the per-department aggregation explicit instead of expressing it as a per-employee check.

For a performance comparison, inspect the execution plans. The shorter query is not necessarily the faster one.

SQL

SELECT customer, SUM(amount) AS total
FROM orders
WHERE order_date < '2026-08-01'
GROUP BY customer
HAVING SUM(amount) > (SELECT AVG(amount) FROM orders)
ORDER BY total DESC;

Step	What it does here	Rows left
FROM orders	all seven orders	7
WHERE order_date < '2026-08-01'	July orders only	4
GROUP BY customer	Ananya, Rahul, Sneha	3
HAVING SUM(amount) > 1131.5	Rahul's 890 is dropped	2
SELECT customer, SUM(amount)	builds the two output columns	2
ORDER BY total DESC	Sneha 2100, then Ananya 1680.5	2

Seven rows walk in and two walk out.

The subquery inside HAVING is uncorrelated. It is the average of all seven orders, 1131.5, computed once, and it does not care about the WHERE line above it.

find the hidden number
write the inner query alone and run it
plug it into WHERE / FROM / SELECT
fix aliases and ORDER BY

Check what you learned

In the department-average query above, why do both HR rows disappear?

Query Templates

SQL

-- 1. Scalar: every row against one number for the whole table.
SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY emp_id;

-- 2. Correlated: every row against its own group's number.
SELECT name, dept FROM employees e
WHERE e.salary > (SELECT AVG(x.salary) FROM employees x WHERE x.dept = e.dept);

-- 3. Derived table: average per department, then join back; includes equal salaries.
SELECT e.name, e.salary
FROM employees e
JOIN (SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept) d
  ON d.dept = e.dept
WHERE e.salary >= d.avg_sal
ORDER BY e.emp_id;

-- 4. Second highest distinct value with a scalar subquery.
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

The CTE chain, where each step gets a name with WITH, is built step by step in Variation 2 below.

Variations

Variation 1 - Membership with IN

When the inner query returns a list instead of a single value, compare with IN, which asks one simple thing: is this value present in that list?

The usual shape is two steps: find the groups that qualify, then pull every row belonging to them.

Step one, which departments have at least three people? COUNT(*) counts rows in a group, and the condition sits in HAVING because it is about the group, not the row.

SQL

SELECT dept FROM employees GROUP BY dept HAVING COUNT(*) >= 3;

dept
IT
Sales

HR has two employees, so it misses the cut and the list has two entries. Step two, feed that list to the outer query.

SQL

SELECT name, dept FROM employees
WHERE dept IN (SELECT dept FROM employees GROUP BY dept HAVING COUNT(*) >= 3)
ORDER BY emp_id;

name	dept
Asha	IT
Bala	IT
Chetan	IT
Gita	Sales
Harish	Sales
Ishan	Sales

All six people from IT and Sales come through, Divya and Farhan go out with their department. IN fits because the question is about membership in the set of qualifying departments.

Variation 2 - A two-step CTE pipeline

Second scenario, the orders table:

order_id	customer	order_date	amount
101	Ananya	2026-07-03	1250.50
102	Rahul	2026-07-08	890.00
103	Ananya	2026-07-19	430.00
104	Sneha	2026-07-27	2100.00
105	Rahul	2026-08-02	1500.00
106	Vikram	2026-08-11	760.00
107	Ananya	2026-08-23	990.00

The ask: "customers whose total spend is above the average customer's total spend". Two aggregations sit inside that one line, so two steps. Step one, total per customer.

SQL

SELECT customer, SUM(amount) AS total FROM orders GROUP BY customer ORDER BY total DESC;

customer	total
Ananya	2670.5
Rahul	2390.0
Sneha	2100.0
Vikram	760.0

Seven orders became four totals, whose average is 1980.13. that is not the average order value, it is the average of the totals.

Step two, name that result spend with WITH and filter it against its own average.

orders (7 rows)
spend: total per customer (4 rows)
average of those totals = 1980.13
big: totals above it (3 rows)
ORDER BY total DESC

SQL

WITH spend AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
), big AS (
    SELECT * FROM spend WHERE total > (SELECT AVG(total) FROM spend)
)
SELECT customer, printf('%.2f', total) AS total
FROM big
ORDER BY big.total DESC;

customer	total
Ananya	2670.50
Rahul	2390.00
Sneha	2100.00

Vikram's 760 is the only total below 1980.13, so his is the single row dropped.

Notice that spend is used twice, as the source of big and inside the subquery that averages it. That reuse is the real reason to prefer a CTE over pasting a derived table in two places.

The final sort uses big.total, the numeric total. The output alias total holds formatted text from printf; sorting that alias can put 900.00 before 2100.00.

Formatting the result. If the SQLite question requires two decimal places, the formatted rows should look like this:

Text

Ananya|2670.50
Rahul|2390.00
Sneha|2100.00

That is why the query uses printf('%.2f', total). Swap it for ROUND(total, 2) and you get this:

Text

Ananya|2670.5
Rahul|2390.0
Sneha|2100.0

The amounts are unchanged, but the trailing zeroes are missing. Use printf when the required result is text with exactly two decimal places.

Variation 3 - Set operations

Two SELECT statements with the same columns, in the same order, can be stacked and combined into one result.

Operator	Keeps	Duplicates
`UNION`	rows in either query	removed
`UNION ALL`	rows in either query	kept
`INTERSECT`	rows present in both	removed
`EXCEPT`	rows in the first, not in the second	removed

July customers are Ananya, Rahul and Sneha. August customers are Rahul, Vikram and Ananya. Same two branches, four operators:

SQL

SELECT customer FROM orders WHERE order_date LIKE '2026-07%'
INTERSECT
SELECT customer FROM orders WHERE order_date LIKE '2026-08%'
ORDER BY customer;

operator	output
`UNION`	Ananya, Rahul, Sneha, Vikram
`UNION ALL`	7 rows, with Ananya appearing three times
`INTERSECT`	Ananya, Rahul
`EXCEPT`	Sneha

Only the operator changes, and the row count swings from 1 to 7.

EXCEPT is the cleanest way to write "ordered in July but not in August". Swap the branches and the answer becomes Vikram, so branch order is part of the answer, not a style choice.

IMP
Choose based on duplicates: UNION removes repeated result rows, including repeats within either branch. UNION ALL retains them and avoids deduplication work. Even non-overlapping branches can contain duplicates of their own.

SQLite supports all four. MySQL got INTERSECT and EXCEPT only in 8.0.31, so in a MySQL interview offer the NOT EXISTS rewrite as your fallback.

Check what you learned

SELECT customer FROM orders WHERE amount > 1000 UNION ALL SELECT customer FROM orders WHERE amount < 500 ORDER BY customer. Where does the ORDER BY apply?

Common Mistakes

A scalar subquery returns several rows. SQLite uses the first row, while other engines may reject the query. Decide what single value the question actually needs. Do not add MAX just to make the query run.
NOT IN reads a nullable column. If the inner list contains even one NULL, NOT IN returns no rows at all, as Day 5 showed. Use NOT EXISTS, or remove the NULLs from the inner list with WHERE col IS NOT NULL.
The boundary is wrong. “Above average” needs >; “at least the average” needs >=. Include an equal-to-average row when checking your query.
The aggregation is at the wrong level. Average order amount and average customer total answer different questions. Write down what each intermediate row represents before taking another average.
The output format differs. When two decimal places are required as text, use SQLite's printf. ROUND changes the numeric precision but does not guarantee trailing zeroes.
Set-operation columns do not line up. Both branches need the same number of columns in corresponding positions. Compatible types matter too; SQLite accepting a mixture of names and numbers does not make the result meaningful.
A correlated query repeats expensive work. Check the plan and indexes before rewriting. A grouped derived table or CTE joined back to the base table is worth comparing, but the syntax alone does not guarantee a speedup.

Interview Notes

Quick Test

Check what you learned

In the department-average query you change > to >=. Which extra rows appear?

SQL

SELECT name FROM employees e
WHERE salary >= (SELECT AVG(x.salary) FROM employees x WHERE x.dept = e.dept);