---
title: "Advanced SQL 1"
subtitle: "November 2 · Build a complicated answer in readable steps"
author: "MaDS Databases & SQL"
format:
  revealjs:
    theme: [default, mads-sql-reveal.scss]
    slide-number: c/t
    chalkboard: true
    code-line-numbers: true
    transition: fade
    footer: "Adapted from Alex Reinhart · MADS Computing"
---

## One question will organize today

::: {.question}
Which station-days deserve review because their records are missing, contradictory, or unusually wet?
:::

The challenge is not one new keyword. It is composing a trustworthy answer.

## Course source and adaptation

::: {.source-note}
This course follows and adapts [Alex Reinhart's MADS Computing course](https://www.refsmmat.com/courses/msp-computing/). Today's subquery concepts follow his [Advanced SQL chapter](https://www.refsmmat.com/courses/msp-computing/data-engineering/advanced-sql.html); `CASE`, CTE, and set-operation material extends that backbone using current PostgreSQL behavior and the Fall 2026 weather database.
:::

## By the end of class

You should be able to:

- encode decision rules with `CASE`,
- use scalar, `IN`, and `EXISTS` subqueries,
- break a query into named CTEs,
- combine compatible results with set operations,
- test every step before composing the final query.

## Complex queries contain smaller questions

Our review queue requires:

1. Which rows violate simple quality rules?
2. What is normal precipitation for each station?
3. Which days are far above that baseline?
4. How do we combine the reasons without hiding duplicates?

Write and test those questions separately first.

## `CASE` turns rules into values

```sql
SELECT station_id, date, precip, tmax, tmin,
  CASE
    WHEN precip IS NULL THEN 'missing precipitation'
    WHEN tmax < tmin THEN 'temperature conflict'
    WHEN precip = 0 THEN 'no precipitation'
    ELSE 'measured'
  END AS review_band
FROM observations;
```

The result is a derived column, not stored data.

## The first true branch wins

```sql
CASE
  WHEN precip IS NULL THEN 'missing'
  WHEN precip = 0 THEN 'zero'
  ELSE 'positive'
END
```

Order rules from most specific or urgent to least.

Always include an `ELSE` unless `NULL` is the intended fallback.

## Checkpoint 1: classify records

::: {.checkpoint}
For observations on September 1, label each row as missing precipitation, temperature conflict, no precipitation, or measured. Put quality problems first.
:::

Return station ID, date, precipitation, temperatures, and `review_band`.

## Checkpoint 1: one solution

```sql
SELECT station_id, date, precip, tmax, tmin,
  CASE
    WHEN precip IS NULL THEN 'missing precipitation'
    WHEN tmax < tmin THEN 'temperature conflict'
    WHEN precip = 0 THEN 'no precipitation'
    ELSE 'measured'
  END AS review_band
FROM observations
WHERE date = DATE '2025-09-01';
```

## An alias is not available in `WHERE`

This does not work in the same query level:

```sql
SELECT ..., CASE ... END AS review_band
FROM observations
WHERE review_band <> 'measured';
```

`WHERE` is evaluated before the `SELECT` alias exists.

Use an outer query or CTE.

## A subquery can become a table

```sql
SELECT *
FROM (
  SELECT station_id, date,
    CASE ... END AS review_band
  FROM observations
) AS classified
WHERE review_band <> 'measured';
```

The outer query can filter the inner query's result columns.

Every `FROM` subquery needs an alias.

## A scalar subquery returns one value

```sql
SELECT station_id, date, precip
FROM observations
WHERE precip > (
  SELECT AVG(precip)
  FROM observations
  WHERE date >= DATE '2025-01-01'
    AND date < DATE '2026-01-01'
);
```

The comparison fails if the subquery returns more than one row.

## Checkpoint 2: compare with a baseline

::: {.checkpoint}
Return 2025 station-days whose known precipitation exceeds the overall 2025 mean. Show the largest values first.
:::

Write and run the mean subquery alone before inserting it into the outer query.

## Checkpoint 2: one solution

```sql
SELECT station_id, date, precip
FROM observations
WHERE date >= DATE '2025-01-01'
  AND date < DATE '2026-01-01'
  AND precip IS NOT NULL
  AND precip > (
    SELECT AVG(precip)
    FROM observations
    WHERE date >= DATE '2025-01-01'
      AND date < DATE '2026-01-01'
  )
ORDER BY precip DESC;
```

## `EXISTS` asks whether a match exists

```sql
SELECT s.station_id, s.name
FROM stations AS s
WHERE EXISTS (
  SELECT 1
  FROM observations AS o
  WHERE o.station_id = s.station_id
    AND o.date >= DATE '2025-01-01'
    AND o.date < DATE '2026-01-01'
);
```

The inner query is correlated with the current station row.

## `NOT EXISTS` expresses missing relationships

```sql
SELECT s.station_id, s.name
FROM stations AS s
WHERE NOT EXISTS (
  SELECT 1
  FROM observations AS o
  WHERE o.station_id = s.station_id
);
```

This directly asks for stations with no matching observation.

## Be careful with `NOT IN`

```sql
WHERE station_id NOT IN (
  SELECT station_id FROM observations
)
```

If the subquery can contain `NULL`, the comparison may become unknown for every row.

Prefer `NOT EXISTS` for an anti-join unless null behavior is proven safe.

## A CTE gives a step a name

```sql
WITH station_baseline AS (
  SELECT station_id, AVG(precip) AS mean_precip
  FROM observations
  WHERE date >= DATE '2025-01-01'
    AND date < DATE '2026-01-01'
    AND precip IS NOT NULL
  GROUP BY station_id
)
SELECT *
FROM station_baseline;
```

A CTE exists only for this statement.

## Multiple CTEs create a readable pipeline

```text
station_baseline
      ↓
scored_days
      ↓
join station names
      ↓
final review queue
```

Each step should have a clear grain and be runnable on its own while developing.

## CTEs are not automatic performance fences

Current PostgreSQL can fold a side-effect-free, one-use CTE into the parent query.

It may materialize a CTE used multiple times.

Use CTEs first for clarity. Use `EXPLAIN` before making performance claims.

::: {.source-line}
Current behavior: [PostgreSQL documentation on CTE materialization](https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-CTE-MATERIALIZATION).
:::

## Step 1: calculate each station baseline

```sql
WITH station_baseline AS (
  SELECT station_id, AVG(precip) AS mean_precip
  FROM observations
  WHERE date >= DATE '2025-01-01'
    AND date < DATE '2026-01-01'
    AND precip IS NOT NULL
  GROUP BY station_id
)
SELECT * FROM station_baseline;
```

Result grain: one row per station.

## Step 2: score individual days

```sql
, scored_days AS (
  SELECT o.station_id, o.date, o.precip, b.mean_precip,
    CASE
      WHEN b.mean_precip > 0
       AND o.precip >= 3 * b.mean_precip THEN '3x baseline'
      ELSE 'not flagged'
    END AS review_reason
  FROM observations AS o
  JOIN station_baseline AS b USING (station_id)
  WHERE o.precip IS NOT NULL
)
```

Result grain returns to one row per station-day.

## Checkpoint 3: assemble the queue

::: {.checkpoint}
Finish the two-CTE query. Attach station names, retain only days at least three times their station baseline, and show the largest precipitation values first.
:::

Before running the whole query, run each CTE's body separately.

## Checkpoint 3: final query

```sql
WITH station_baseline AS (...),
scored_days AS (...)
SELECT
  s.station_id, s.name,
  d.date, d.precip,
  ROUND(d.mean_precip::numeric, 1) AS mean_precip,
  d.review_reason
FROM scored_days AS d
JOIN stations AS s USING (station_id)
WHERE d.review_reason = '3x baseline'
ORDER BY d.precip DESC
LIMIT 25;
```

## Set operations stack compatible results

```sql
query_a
UNION ALL
query_b
```

Both queries need the same number and order of columns, with compatible types.

`UNION ALL` keeps duplicates; `UNION` removes identical result rows.

## Keep separate reasons with `UNION ALL`

```sql
SELECT station_id, date, 'missing precip' AS reason
FROM observations
WHERE precip IS NULL
UNION ALL
SELECT station_id, date, 'temperature conflict' AS reason
FROM observations
WHERE tmax < tmin;
```

One station-day can appear twice when it needs two kinds of review.

## Other set questions have direct operators

| Question | Operator |
|---|---|
| either result | `UNION` |
| both results | `INTERSECT` |
| in the first but not second | `EXCEPT` |

Use `ALL` when multiplicity matters and deduplication is not intended.

## Project transfer

::: {.project-prompt}
Rewrite one complicated project query as two or three named steps.
:::

For every step, note its purpose, row grain, a standalone test, and the assumption most likely to fail.

## Homework starts here

Save the project-transfer query as the first draft of your query library entry.

Add one adversarial test:

- a missing value,
- a zero baseline,
- a station with no matching row, or
- a row satisfying multiple review reasons.

## The pattern to keep

```text
split the question
  → test each piece
  → name intermediate results
  → state every grain
  → compose the pieces
  → preserve or remove duplicates deliberately
  → test an edge case
```

Next: calculate across related rows without collapsing them.