Go beyond the basics with subqueries, CTEs, window functions and the analytics patterns analysts use every day, practised in your browser.

SQL for Analysis

## Why it matters

Many real questions need two steps. "Which employees earn more than average?" means first working out the average, then comparing every row with it. "Which customers have placed an order?" means first finding the customer ids in `orders`, then looking them up in `customers`. A **subquery** lets you do both steps in one statement: a complete `SELECT` placed inside another query, in brackets.

In SQL Fundamentals you filtered with fixed values such as `WHERE salary > 60000`. A subquery replaces that hard-coded number with a value the database computes from the data, so the query stays correct when the data changes.

## Three places a subquery can go

**1. A single value in `WHERE` (a scalar subquery).** The inner query must return exactly one row and one column.

```sql
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
```

The database runs the inner `SELECT AVG(salary)` once, gets one number (say 67,400), and uses it like a constant.

**2. A list in `WHERE ... IN`.** The inner query returns one column with any number of rows.

```sql
-- Customers who have placed at least one order
SELECT id, name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);
```

The opposite, `NOT IN`, finds customers with no orders, but be careful: if the subquery returns even one `NULL`, `NOT IN` returns no rows at all, because `x NOT IN (1, NULL)` is never true. `NOT EXISTS` (below) does not have this problem.

**3. A table in `FROM` (a derived table).** The inner query produces a temporary result you can select from. It must have an alias.

```sql
SELECT region, AVG(total_spent) AS avg_customer_spend
FROM (
  SELECT c.region, c.id, SUM(o.amount) AS total_spent
  FROM customers AS c
  JOIN orders AS o ON o.customer_id = c.id
  GROUP BY c.region, c.id
) AS customer_totals
GROUP BY region;
```

This is the classic "aggregate of an aggregate": total per customer first, then the average of those totals per region. You cannot nest `AVG(SUM(...))` directly, so the subquery is required.

## Correlated subqueries

The subqueries above run once on their own. A **correlated** subquery refers to a column from the outer query, so it is evaluated for each outer row.

Example `employees` table:

| name | department | salary |
|---|---|---|
| Amara | Sales | 52000 |
| Ben | Sales | 61000 |
| Dev | Engineering | 98000 |
| Elif | Engineering | 87000 |

"Who earns more than the average **of their own department**?"

```sql
SELECT e.name, e.department, e.salary
FROM employees AS e
WHERE e.salary > (
  SELECT AVG(d.salary)
  FROM employees AS d
  WHERE d.department = e.department   -- links to the outer row
);
```

For Ben, the inner query averages Sales salaries (56,500), and 61,000 is higher, so Ben is kept. For Elif, it averages Engineering (92,500), and 87,000 is lower, so Elif is dropped. The aliases `e` (outer) and `d` (inner) are essential here: without them, `department = department` would compare the inner table with itself and always be true.

## EXISTS and NOT EXISTS

`EXISTS (subquery)` is true when the subquery returns at least one row. It is usually correlated and reads like plain English:

```sql
-- Customers with no orders at all
SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o WHERE o.customer_id = c.id
);
```

`SELECT 1` is a convention: `EXISTS` only checks whether a row exists, so the selected value does not matter. Use `NOT EXISTS` instead of `NOT IN` whenever the inner column could contain `NULL`.

## Subquery or join?

Many subqueries can be rewritten as joins, and modern databases often run both the same way. Pick the one that says what you mean:

- Use `IN` / `EXISTS` when you only need to **filter** by another table and do not need its columns. A join can multiply rows (a customer with three orders appears three times), and you would then need `DISTINCT`.
- Use a join when you need **columns from both** tables in the output.
- Use a derived table or, better, a CTE (next lesson) when you need an aggregate of an aggregate.

## Common mistakes

- **A scalar subquery that returns several rows.** `WHERE salary > (SELECT salary FROM employees WHERE department = 'Sales')` fails in most databases (SQLite quietly uses the first row, which is worse). Aggregate it or use `IN`.
- **Forgetting the alias on a derived table.** `FROM (SELECT ...)` needs `AS something` in PostgreSQL and MySQL.
- **`NOT IN` with NULLs.** Returns nothing. Switch to `NOT EXISTS`.
- **Ambiguous columns in a correlated subquery.** Always alias both sides.

## Quick recap

- A subquery is a `SELECT` inside brackets that supplies a value, a list or a table.
- Scalar subqueries return one value; `IN` takes a one-column list; `FROM` subqueries need an alias.
- A correlated subquery refers to the outer row and is evaluated per row.
- Prefer `NOT EXISTS` over `NOT IN` when NULLs are possible.

## Try it

Open the **Do** tab and turn a company-wide comparison into a per-department one with a correlated subquery.


Use a query inside a query to filter by computed values, lists from other tables and per-group averages.

Subqueries

Advanced SQL Tutorial | Subqueries

How to do Subqueries in SQL with Examples

## Why it matters

Analysis queries grow. A question like "which customers spend more than the average customer, and what region are they in?" can turn into a subquery inside a subquery inside a join, and nobody (including you next month) can read it. A **common table expression** (CTE) lets you name each step and write them top to bottom, in the order you think about the problem. CTEs are the single biggest readability upgrade in analytical SQL, and you will use them in almost every lesson from here on.

## The core idea

A CTE is a named, temporary result that exists only for one statement. You define it with `WITH name AS (...)` before the main `SELECT`, then use the name as if it were a table.

```sql
WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, total_spent
FROM customer_totals
WHERE total_spent > 200;
```

Nothing is stored in the database. When the statement finishes, `customer_totals` is gone.

## Rewriting a nested query

Here is a nested version of "customers whose total is above the average customer total":

```sql
SELECT c.name, t.total_spent
FROM customers AS c
JOIN (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
) AS t ON t.customer_id = c.id
WHERE t.total_spent > (
  SELECT AVG(total_spent)
  FROM (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
  ) AS t2
);
```

The per-customer total appears **twice**, and you have to read from the inside out. The CTE version defines it once:

```sql
WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT c.name, t.total_spent
FROM customer_totals AS t
JOIN customers AS c ON c.id = t.customer_id
WHERE t.total_spent > (SELECT AVG(total_spent) FROM customer_totals);
```

Same result, half the code, and each piece has a name that explains it.

## Chaining several CTEs

Separate CTEs with commas. Each one can use the ones defined before it, so you can build a small pipeline:

```sql
WITH completed AS (            -- step 1: only the orders that count
  SELECT customer_id, amount
  FROM orders
  WHERE status = 'completed'
),
customer_totals AS (           -- step 2: one row per customer
  SELECT customer_id, SUM(amount) AS total_spent
  FROM completed
  GROUP BY customer_id
),
benchmark AS (                 -- step 3: one number
  SELECT AVG(total_spent) AS avg_spent FROM customer_totals
)
SELECT t.customer_id, t.total_spent
FROM customer_totals AS t
CROSS JOIN benchmark AS b
WHERE t.total_spent > b.avg_spent;
```

`CROSS JOIN` with a one-row CTE is a common trick to make a single value available on every row. Note there is only one `WITH` keyword, at the very start.

A good habit while building a chain: end with `SELECT * FROM <latest step>` and run it, check the rows look right, then add the next step. This is how analysts debug long queries.

## Recursive CTEs (a preview)

`WITH RECURSIVE` lets a CTE refer to itself. It is used for hierarchies (an org chart, a category tree) and for generating series such as every date in a month:

```sql
WITH RECURSIVE days(d) AS (
  SELECT '2026-05-01'
  UNION ALL
  SELECT date(d, '+1 day') FROM days WHERE d < '2026-05-07'
)
SELECT d FROM days;
```

This returns seven dates, which is handy for filling gaps in a daily report by left-joining your data onto it. You do not need recursion for this course's activities, but it is good to recognise it.

## CTE, subquery or view?

- **CTE:** a step used inside one query, especially if used more than once or if it makes the query read top to bottom.
- **Subquery:** a short, one-off filter like `WHERE id IN (SELECT ...)`.
- **View:** a step many different queries and people need. It is saved in the database (you met views in SQL Fundamentals).

CTEs are supported in SQLite, PostgreSQL, MySQL 8+, SQL Server, Oracle and the major cloud warehouses. Performance is normally similar to the equivalent subquery; some engines can materialize a CTE once, others inline it.

## Common mistakes

- **Repeating `WITH`.** Write `WITH a AS (...), b AS (...)`, not `WITH a AS (...) WITH b AS (...)`.
- **A trailing comma** after the last CTE, right before the main `SELECT`. That is a syntax error.
- **Forgetting the main query.** A `WITH` block on its own does nothing; it must be followed by a `SELECT` (or `INSERT`, `UPDATE`, `DELETE`).
- **Using a CTE in a later, separate statement.** It only exists for the statement it is attached to.
- **Vague names** such as `t1`, `temp`. Name the step for what it contains: `completed_orders`, `customer_totals`.

## Quick recap

- `WITH name AS (SELECT ...)` defines a temporary named result for one statement.
- Chain steps with commas; later CTEs can use earlier ones.
- CTEs remove duplicated subqueries and make queries read top to bottom.
- Build and check one step at a time.

## Try it

Open the **Do** tab and finish a query that uses a `customer_totals` CTE twice: once for the rows and once for the average.


Rewrite nested queries as named, top-to-bottom steps with WITH, and chain several steps into one readable query.

CTEs

Advanced SQL Tutorial | CTE (Common Table Expression)

SQL CTEs (Common Table Expressions) - Why and How to Use Them

## Why it matters

Joins put tables **side by side**: they add columns. Set operations stack results **on top of each other**, or compare them row by row. Analysts use them constantly: combining this year's and last year's sales tables, building one list of contacts from several systems, finding people on one list but not another, or running several checks and returning all the answers in one result.

## The four operators

Take two queries, A and B, that return the same columns.

| Operator | Returns | Duplicates |
|---|---|---|
| `A UNION B` | rows in A or B | removed |
| `A UNION ALL B` | every row of A, then every row of B | kept |
| `A INTERSECT B` | rows in both A and B | removed |
| `A EXCEPT B` | rows in A that are not in B | removed |

Oracle traditionally calls `EXCEPT` **`MINUS`** (Oracle 21c and later accept both). SQLite, PostgreSQL and SQL Server support all four; MySQL added `INTERSECT` and `EXCEPT` in version 8.0.31.

## The rules

1. Both queries must return the **same number of columns**.
2. Columns are matched by **position**, not by name, and should have compatible types.
3. The output column names come from the **first** query.
4. `ORDER BY` goes once, at the very end, and sorts the combined result.

## Worked examples

Two tables of email addresses: `newsletter` (people who signed up) and `orders` (people who bought).

| newsletter.email | orders.email |
|---|---|
| ana@example.com | ana@example.com |
| ben@example.com | chen@example.com |
| chen@example.com | gus@example.com |
| dee@example.com | chen@example.com |

**Everyone we can contact** (either list, each email once):

```sql
SELECT email FROM newsletter
UNION
SELECT email FROM orders;
-- ana, ben, chen, dee, gus
```

**Subscribers who are also customers:**

```sql
SELECT email FROM newsletter
INTERSECT
SELECT email FROM orders;
-- ana, chen
```

**Subscribers who have never bought** (a target list for a first-order discount):

```sql
SELECT email FROM newsletter
EXCEPT
SELECT email FROM orders;
-- ben, dee
```

Order matters for `EXCEPT`: `orders EXCEPT newsletter` gives `gus`, a buyer who never subscribed.

## UNION vs UNION ALL

`UNION` has to sort or hash every row to remove duplicates, which costs time on big tables, and it can silently remove rows you meant to keep. If two stores each sold a 20.00 coffee on the same day, `UNION` would collapse those into one row and your revenue would be wrong.

```sql
SELECT 'north' AS store, sale_date, amount FROM north_sales
UNION ALL
SELECT 'south', sale_date, amount FROM south_sales;
```

Rule of thumb: use **`UNION ALL` when combining data** (stacking partitions, years, stores), and `UNION` only when you specifically want a distinct list. Adding a literal "source" column like `'north'` above is a good habit: it records where each row came from and also stops identical rows from different sources looking like duplicates.

## Set operations as a report builder

Because `UNION ALL` stacks rows, you can glue several one-row summaries into a tidy table:

```sql
SELECT 'orders' AS metric, COUNT(*) AS value FROM orders
UNION ALL
SELECT 'customers', COUNT(*) FROM customers
UNION ALL
SELECT 'products', COUNT(*) FROM products;
```

You will use exactly this pattern for data quality checks later in the course.

## Set operations vs joins

`EXCEPT` and `NOT EXISTS` can answer the same "in A but not in B" question. `EXCEPT` compares **whole rows** and returns only the selected columns, so it is short and clear for lists of keys. `NOT EXISTS` or a `LEFT JOIN ... WHERE b.id IS NULL` is better when you want other columns from A in the output, or when the match is on some columns but not all. Also note that set operations treat two `NULL`s as equal (so `NULL` rows are de-duplicated), while `=` in a join never matches `NULL`.

## Common mistakes

- **Different column counts** (`SELECT email, name ... UNION SELECT email ...`): an error.
- **Columns in a different order** in each query. The query runs, but `city` ends up stacked under `name`. Always list columns explicitly, never `SELECT *`.
- **`ORDER BY` inside each part.** Put one `ORDER BY` at the end. (To sort or limit a single part, wrap it in a subquery or CTE.)
- **Using `UNION` and losing legitimate duplicates** in sums and counts.
- **Case and whitespace differences.** `'Ana@Example.com'` and `'ana@example.com'` are different rows. Clean with `LOWER(TRIM(email))` in both queries first.

## Quick recap

- `UNION` / `UNION ALL` stack results; `INTERSECT` keeps the overlap; `EXCEPT` keeps what is only in the first.
- Same number of columns, matched by position; names come from the first query.
- Default to `UNION ALL` for combining data.
- One `ORDER BY`, at the end.

## Try it

Open the **Do** tab and build the "subscribed but never ordered" list with a set operator.


Stack and compare result sets with UNION, UNION ALL, INTERSECT and EXCEPT, and know when each one is right.

UNION and set operations

Combine SQL Queries With UNION, INTERSECT, EXCEPT

Answer multi-step questions with subqueries, organise them into readable CTEs, and combine result sets with UNION, INTERSECT and EXCEPT.