Advanced SQL 1

November 2 · Build a complicated answer in readable steps

MaDS Databases & SQL

One question will organize today

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

This course follows and adapts Alex Reinhart’s MADS Computing course. Today’s subquery concepts follow his Advanced SQL chapter; 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

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

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

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

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:

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

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

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

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

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

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

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

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

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

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.

Current behavior: PostgreSQL documentation on CTE materialization.

Step 1: calculate each station baseline

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

, 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

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

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

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

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

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

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.