Advanced SQL 2

November 4 · Analyze across rows and inspect the work

MaDS Databases & SQL

One question will organize today

For every station-day, how unusual was precipitation, how did it change, and which records rank highest?

We need comparisons across rows without losing the individual rows.

Course source and adaptation

This course follows and adapts Alex Reinhart’s MADS Computing course. Today’s window-function concepts follow his Advanced SQL chapter; ranking, frames, indexing, and plan-reading use current PostgreSQL behavior and the Fall 2026 weather database.

By the end of class

You should be able to:

• distinguish aggregation from window calculation,
• define a window’s partition, order, and frame,
• calculate anomalies, ranks, changes, and rolling means,
• filter window results through an outer query,
• use EXPLAIN to justify an index decision.

Aggregation collapses; windows annotate

GROUP BY station_id

one row per station

AVG(precip) OVER (PARTITION BY station_id)

one row per station-day, with the station mean attached

Choose based on the result grain you need.

OVER turns an aggregate into a window function

SELECT
  station_id,
  date,
  precip,
  AVG(precip) OVER (
    PARTITION BY station_id
  ) AS station_mean
FROM observations;

Every row remains visible.

A partition defines comparison peers

PARTITION BY station_id

means:

calculate separately inside each station

Without PARTITION BY, the window contains all result rows.

Window calculations see rows after WHERE

FROM observations
WHERE date >= DATE '2025-01-01'
  AND date < DATE '2026-01-01'

followed by:

AVG(precip) OVER (PARTITION BY station_id)

calculates each station’s 2025 mean, not its all-time mean.

Checkpoint 1: calculate an anomaly

For each known 2025 observation, attach its station’s 2025 mean precipitation and calculate precip - station_mean.

Return station ID, date, precipitation, mean, and anomaly. Keep every station-day.

Checkpoint 1: one solution

SELECT station_id, date, precip,
  AVG(precip) OVER (
    PARTITION BY station_id
  ) AS station_mean,
  precip - AVG(precip) OVER (
    PARTITION BY station_id
  ) AS precip_anomaly
FROM observations
WHERE date >= DATE '2025-01-01'
  AND date < DATE '2026-01-01'
  AND precip IS NOT NULL;

Window order is not display order

LAG(precip) OVER (
  PARTITION BY station_id
  ORDER BY date
)

orders rows for the calculation.

The final result still needs its own:

ORDER BY station_id, date

ROW_NUMBER creates a within-group order

ROW_NUMBER() OVER (
  PARTITION BY station_id
  ORDER BY precip DESC NULLS LAST, date
) AS wet_rank

The extra date term makes ties deterministic.

Ranking functions treat ties differently

Function	Tied values	Next rank
ROW_NUMBER()	different numbers	next integer
RANK()	same rank	leaves gaps
DENSE_RANK()	same rank	no gaps

Pick the meaning your question requires.

Window aliases cannot be filtered in WHERE

This is too early:

WHERE wet_rank <= 3

Create the rank in a CTE, then filter in the outer query.

The same evaluation-order issue appeared with CASE aliases.

Checkpoint 2: top three per station

Return the three wettest known 2025 days for every station. Break precipitation ties by earlier date.

Use ROW_NUMBER() inside a CTE and filter wet_rank outside it.

Checkpoint 2: one solution

WITH ranked AS (
  SELECT station_id, date, precip,
    ROW_NUMBER() OVER (
      PARTITION BY station_id
      ORDER BY precip DESC, date
    ) AS wet_rank
  FROM observations
  WHERE date >= DATE '2025-01-01'
    AND date < DATE '2026-01-01'
    AND precip IS NOT NULL
)
SELECT *
FROM ranked
WHERE wet_rank <= 3;

LAG looks backward in the ordered partition

SELECT station_id, date, precip,
  LAG(precip) OVER (
    PARTITION BY station_id
    ORDER BY date
  ) AS previous_precip
FROM observations;

The first row in each station has no previous value.

Daily change is a row-to-row calculation

precip - LAG(precip) OVER (
  PARTITION BY station_id
  ORDER BY date
) AS precip_change

Check the dates too. The previous row may not be the previous calendar day when observations are missing.

A frame limits the ordered window

AVG(precip) OVER (
  PARTITION BY station_id
  ORDER BY date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7row_mean

This is seven rows—not necessarily seven calendar days.

Always write the intended frame

An ordered aggregate has a default frame, but defaults are easy to misread.

Use an explicit frame for:

• rolling calculations,
• cumulative totals,
• first or last value logic.

The frame is part of the analytical definition.

Named windows remove repetition

SELECT station_id, date, precip,
  LAG(precip) OVER station_history AS previous_precip,
  AVG(precip) OVER station_history AS cumulative_mean
FROM observations
WINDOW station_history AS (
  PARTITION BY station_id
  ORDER BY date
);

One named specification keeps related calculations aligned.

A correct query can still be too slow

SELECT station_id, date, precip
FROM observations
WHERE station_id = 'USW00094823'
  AND date >= DATE '2025-01-01'
  AND date < DATE '2026-01-01';

On a large table, PostgreSQL must choose how to find those rows.

An index supports repeated search patterns

For equality by station followed by a date range:

CREATE INDEX observations_station_date_idx
ON observations (station_id, date);

But first inspect the schema: UNIQUE (station_id, date) already creates a supporting unique index.

Do not create a redundant index.

Indexes have costs

An index can reduce rows scanned, but it also:

• consumes storage,
• adds work to inserts and updates,
• requires maintenance,
• may not help queries that return much of the table.

“Add an index” is a hypothesis to test, not a ritual.

EXPLAIN shows the proposed plan

EXPLAIN
SELECT ...
FROM observations
WHERE station_id = 'USW00094823'
  AND date >= DATE '2025-01-01'
  AND date < DATE '2026-01-01';

Look for nodes such as Seq Scan, Index Scan, Sort, and WindowAgg.

EXPLAIN ANALYZE runs the query

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

It reports actual rows and timing—but executes the statement.

In class, use it only with safe SELECT queries in your team schema.

Estimates tell you what the planner believed

Compare estimated versus actual rows first. Then inspect the scan, rows removed by filters, repeated loops, and execution time.

Large estimate errors can lead to a poor plan and may indicate stale statistics or skewed data.

Checkpoint 3: justify an index decision

Run EXPLAIN on the station-and-date query. Determine whether the existing schema already supplies an index, then predict whether PostgreSQL should use it.

Record the scan node and estimated rows. If permitted, run EXPLAIN ANALYZE and compare actual rows.

Plans depend on the data and question

PostgreSQL may choose a sequential scan when:

• the table is small,
• the filter returns many rows,
• statistics predict the index will not help.

The same SQL can receive a different plan after the data or statistics change.

Project transfer

Add one window query and one plan note to the project query library.

Document the partition, order, frame, and result grain. Explain why a window is needed, identify the key plan node, and decide whether an index change is justified.

Homework starts here

Save today’s project-transfer query.

Add two checks:

1. verify the first row in each partition,
2. create a gap or tie and confirm the result matches your intended meaning.

Include the EXPLAIN output as evidence, not decoration.

The pattern to keep

preserve the row grain
  → define the partition
  → define calculation order
  → write the frame
  → test gaps and ties
  → inspect the plan
  → justify—not guess—an index

Next: send parameterized SQL from code and turn queries into a pipeline.