Pivots and period comparisons

01The problem

A board pack draft has this line: "Shipments fell 33% in 2026: 1,075 against 1,608 in 2025."

Both numbers are correct. The conclusion is wrong. 2025 is a full year, while 2026 runs only to the end of August. Compare January to August with January to August, and bookings went up: 1,052 in 2025 against 1,075 in 2026, a rise of 2.2%.

Period comparisons are where analysts most often embarrass themselves, and the board slide is where it's most costly. This lesson covers the two tools for them: pivots, which put periods side by side as columns, and like-for-like comparisons.

02The concept

Pivoting with conditional aggregation

A pivot turns values in a column (years, modes, statuses) into separate columns. The portable way works in every database: an aggregate wrapped around a CASE.

SELECT
  r.mode,
  SUM(CASE WHEN s.booking_date < '2026-01-01' THEN 1 ELSE 0 END) AS y2025,
  SUM(CASE WHEN s.booking_date >= '2026-01-01' THEN 1 ELSE 0 END) AS y2026
FROM …
GROUP BY r.mode;

Shorter spellings exist. PostgreSQL and SQLite allow COUNT(*) FILTER (WHERE …), and SQL Server, Oracle and Snowflake have a PIVOT operator. But SUM(CASE …) works everywhere and is the one you'll read most in other people's code.

Like for like

Compare periods of the same length and the same season:

• Year to date (YTD): January to the last complete month, in both years.
• Same month last year: August 2026 against August 2025, not against July 2026, if the business is seasonal.
• Only complete periods. A month that's half over always looks like a collapse.

Growth without errors

Growth is (this − last) / last. If last is 0, SQL Server and PostgreSQL raise an error, and SQLite and MySQL return NULL. Make the intention explicit with NULLIF(last, 0), which turns a zero into NULL so the result is NULL, meaning "no comparison possible".

03Example

Year to date by mode, side by side, with the change:

WITH ytd AS (
  SELECT
    r.mode,
    SUM(CASE WHEN s.booking_date BETWEEN '2025-01-01' AND '2025-08-31' THEN 1 ELSE 0 END) AS ytd_2025,
    SUM(CASE WHEN s.booking_date BETWEEN '2026-01-01' AND '2026-08-31' THEN 1 ELSE 0 END) AS ytd_2026
  FROM shipments AS s
  JOIN routes AS r ON r.route_id = s.route_id
  GROUP BY r.mode
)
SELECT
  mode,
  ytd_2025,
  ytd_2026,
  ytd_2026 - ytd_2025 AS change,
  ROUND(100.0 * (ytd_2026 - ytd_2025) / NULLIF(ytd_2025, 0), 1) AS pct_change
FROM ytd
ORDER BY ytd_2026 DESC;

Sea, the bulk of the business, is slightly down (2.6%), and the smaller air and road businesses grew by 14% to 17%. There was no 33% fall: that was the calendar, not the business.

04Walkthrough

1. Run the example. Then change both date ranges to whole years ('2025-01-01' AND '2025-12-31', and the same for 2026) and see misleading falls of 23% to 36% appear.
2. Put it back to January to August. That's the version to show.
3. Run this month-by-month comparison, which shows the seasonal pattern behind the totals:

SELECT
  strftime('%m', booking_date) AS month,
  SUM(booking_date < '2026-01-01') AS y2025,
  SUM(booking_date >= '2026-01-01') AS y2026
FROM shipments
WHERE strftime('%m', booking_date) <= '08'
GROUP BY month
ORDER BY month;

4. Find June: 106 in 2025, 138 in 2026. Most of the year-to-date rise comes from one weak month in 2025, so the honest summary is "flat, with a weak June last year", not "growing".

05Practice

06Challenge

07More practice

08Check your understanding