Calculate a Monthly Trend in SQL With Missing Months Included

The practice task

Build a fictional three-month seat-booking trend. January has three seats, February has no bookings and March has five. The exercise treats the booking log as complete for this stated period.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE bookings(day TEXT, seats INTEGER);
INSERT INTO bookings VALUES ('2026-01-05',3),('2026-03-02',2),('2026-03-24',3);

WITH months(month) AS (VALUES ('2026-01-01'),('2026-02-01'),('2026-03-01')),
 totals AS (
  SELECT STRFTIME('%Y-%m-01',day) AS month, SUM(seats) AS seats
  FROM bookings GROUP BY STRFTIME('%Y-%m-01',day)
 ), filled AS (
  SELECT m.month, COALESCE(t.seats,0) AS seats
  FROM months AS m LEFT JOIN totals AS t ON t.month = m.month
 )
SELECT month, seats, seats - LAG(seats) OVER (ORDER BY month) AS seat_change
FROM filled ORDER BY month;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The calendar retains February even though the booking table has no February row. Filling that known period with zero gives a fall of three from January and a rise of five into March. January has no earlier month in the displayed period, so its change stays NULL.

Skipping the calendar step would compare March directly with January and report a change of two. That is a change between recorded months, not the month-over-month sequence requested here. The intermediate filled result makes the intended periods inspectable.

Zero filling relies on source completeness. A missing data import is not proof of zero activity. State the observed period, reporting unit and completeness assumption before choosing a fill policy. A percentage trend would also require a separate rule for February as a zero denominator.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Aggregate to a consistent month key before comparing periods.
• Distinguish no recorded activity from missing source coverage.
• Keep absolute change separate from percentage growth and its denominator policy.