---
title: "Advanced SQL 2"
subtitle: "November 4 · Analyze across rows and inspect the work"
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}
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

::: {.source-note}
This course follows and adapts [Alex Reinhart's MADS Computing course](https://www.refsmmat.com/courses/msp-computing/). Today's window-function concepts follow his [Advanced SQL chapter](https://www.refsmmat.com/courses/msp-computing/data-engineering/advanced-sql.html); 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`

```text
one row per station
```

`AVG(precip) OVER (PARTITION BY station_id)`

```text
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

```sql
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

```sql
PARTITION BY station_id
```

means:

```text
calculate separately inside each station
```

Without `PARTITION BY`, the window contains all result rows.

## Window calculations see rows after `WHERE`

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

followed by:

```sql
AVG(precip) OVER (PARTITION BY station_id)
```

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

## Checkpoint 1: calculate an anomaly

::: {.checkpoint}
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

```sql
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

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

orders rows for the calculation.

The final result still needs its own:

```sql
ORDER BY station_id, date
```

## `ROW_NUMBER` creates a within-group order

```sql
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:

```sql
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

::: {.checkpoint}
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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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:

```sql
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

```sql
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

```sql
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

::: {.checkpoint}
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

::: {.project-prompt}
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

```text
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.