Skip to content
Tutorials Center
My learning

Topics

Lesson 56 of 125 ยท Window Functions

Time Series and Calendar Tables

Analyze time series in SQL. Build a calendar table, fill missing days, bucket by week and month, compare year over year and smooth data with moving averages.

A time series is a set of measurements ordered by time: daily sales, hourly temperature, monthly signups. Almost every business report is one, and most of them hide the same three problems. Days with no activity simply have no rows, so charts have holes. Timestamps must be grouped into weeks or months, and the definition of "week" varies. And the interesting numbers are comparisons: this month against last month, this year against last year, today against the 7-day average.

This lesson gives you a toolkit for all of it: a calendar table that supplies every date, bucketing with date functions, LAG for year-over-year comparisons, and window frames for moving averages. It builds on running totals and the date functions lesson; the examples use SQLite syntax, with notes on other databases where they differ.

flowchart LR
  C["Calendar table<br/>one row per day"] --> J["LEFT JOIN<br/>daily facts"]
  F["Fact table<br/>missing days"] --> J
  J --> Z["COALESCE(total, 0)<br/>complete series"]
  Z --> W["Window functions<br/>bucket, compare, smooth"]

The usual pipeline: start from a complete list of dates, attach the facts, replace missing values, then analyze.

The problem: days with no rows

Suppose a small shop records sales per day, but only on days when it sold something. Counting rows or averaging the table gives wrong answers, because the quiet days are invisible:

SQL A sales table with missing days

Press Run (or Ctrl + Enter) to see the result here.

Six rows cover nine calendar days (May 1 to May 9), so the real daily average is 750 / 9 = 83.3, not the 125 you get from AVG(amount). The fix is a list of every date in the range.

Generating a series of dates

A calendar table (also called a date dimension) has one row per day. You can generate it on the fly with a recursive CTE and keep it as a permanent table later. PostgreSQL has generate_series(start, end, interval) built in, and SQL Server and MySQL use the same recursive pattern shown here:

SQL Fill the gaps with a calendar

Press Run (or Ctrl + Enter) to see the result here.

The LEFT JOIN keeps every calendar day, and COALESCE turns the missing amounts into zeros. The same technique answers "which days had no sales?" (filter on s.sale_date IS NULL) and "how many customers signed up on each day, including days with none?".

A permanent calendar table is worth having in a real database. Give it a date primary key plus ready-made attributes, so reports never repeat date logic:

SQL
CREATE TABLE calendar (
  day          DATE PRIMARY KEY,
  year         INTEGER NOT NULL,
  quarter      INTEGER NOT NULL,
  month        INTEGER NOT NULL,
  month_name   VARCHAR(10) NOT NULL,
  iso_week     INTEGER NOT NULL,
  weekday      INTEGER NOT NULL,    -- 1 = Monday ... 7 = Sunday
  is_weekend   BOOLEAN NOT NULL,
  is_holiday   BOOLEAN NOT NULL DEFAULT FALSE
);

Holidays, fiscal periods and business-day flags are exactly the things you can't compute from the date alone, which is why this table pays for itself.

Bucketing into weeks and months

To summarize at a coarser level, map every date to a bucket key and GROUP BY it. For months, strftime('%Y-%m', day) works in SQLite (DATE_TRUNC('month', day) in PostgreSQL, DATE_FORMAT in MySQL). Weeks need a decision: which day starts the week? This version snaps each date back to the Monday of its week:

SQL Weekly and monthly totals

Press Run (or Ctrl + Enter) to see the result here.

strftime('%w', ...) returns 0 for Sunday through 6 for Saturday. Adding 6 and taking the remainder by 7 turns that into "days since Monday", and date(d, '-N days') goes back that far. Grouping by the resulting date, rather than by a week number, avoids two classic bugs: week 52 and week 1 getting mixed up around New Year, and weeks from different years sharing a number.

Combine a weekly bucket with the calendar table and you also get empty weeks. Remember that COUNT(*) after a join to a calendar counts the calendar rows; count the fact column (COUNT(s.amount)) when you want only the days that had data.

Comparing periods: month over month and year over year

Comparing a period with an earlier one is a job for LAG. First aggregate to one row per period, then ask each row for the value that came n rows earlier. For monthly data, one row back is last month and twelve rows back is the same month last year. Here is a tempting first attempt that goes wrong, because it filters to the recent months in the same query:

SQL The trap: WHERE removes the history

Press Run (or Ctrl + Enter) to see the result here.

The WHERE clause runs before window functions, so filtering to November 2024 and later removes the older rows that LAG needs. Look at the result: prev_month is NULL for the first row and the year-ago column is NULL everywhere. Move the filter to an outer query so the window sees the whole history first:

SQL Filter after the window, not before

Press Run (or Ctrl + Enter) to see the result here.

The first query filters to 2024-11 onward, so LAG(revenue, 12) can never reach back a year. The second one calculates the comparison over all 18 months in a CTE, and only then filters. This is the general rule for window functions: filter after, not before, whenever the window needs the filtered-out rows.

Using LAG(revenue, 12) assumes the series has no missing months. If months can be missing, join to a calendar first, or compare by key instead: a self join on month = date(prev_year) is slower but never lands on the wrong period.

Smoothing with a moving average

Daily numbers are noisy, and a moving average smooths them by averaging each value with its neighbors. With a complete daily series (so each row really is one day), a frame of ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is a 7-day average:

SQL Seven-day moving average

Press Run (or Ctrl + Enter) to see the result here.

The days_in_window column shows that the first six rows average over fewer than seven days; many analysts blank those out with a CASE WHEN days_in_window < 7 THEN NULL. This is exactly why the calendar join matters: without it, 6 PRECEDING would reach back six rows, which may span two weeks. If you can't build a full calendar, use a RANGE frame over a numeric day count where your database supports it.

Time zones and boundaries

Two last traps. First, an end-of-day timestamp such as 2025-05-09 23:30 is not less than or equal to '2025-05-09', so for ranges prefer a half-open interval: ts >= '2025-05-01' AND ts < '2025-05-10'. Second, store timestamps in UTC and convert to the user's time zone only when bucketing into days, because the same instant falls on different calendar days in Tokyo and Los Angeles.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why do you LEFT JOIN a calendar table to your fact table when building a daily report?

    Days without activity have no rows in the fact table. The calendar supplies every date, and the LEFT JOIN keeps those dates even when nothing matches.
  2. 2. In a monthly series with no missing months, which expression gives the same month last year?

    The row 12 positions earlier is the same month one year before. LAG with an offset of 12 looks back 12 rows.
  3. 3. Why can a WHERE filter on recent months break a year-over-year LAG in the same SELECT?

    Window functions run after WHERE, GROUP BY and HAVING. Compute the comparison in a CTE or subquery over the full history, then filter in the outer query.
  4. 4. What does ROWS BETWEEN 6 PRECEDING AND CURRENT ROW average on a complete daily series?

    Six preceding rows plus the current row make a 7-day window ending today. It is a trailing average.
  5. 5. Which week bucketing avoids mixing up week 1 and week 52 around New Year?

    A week number repeats every year and can belong to two different years at the turn of the year. The start date of the week is unique.

Summary

  • Fact tables only contain days that had activity; a calendar table (generated with a recursive CTE or generate_series) supplies every date.
  • LEFT JOIN the facts onto the calendar and use COALESCE for the empty days; count the fact column, not COUNT(*), to count real events.
  • Bucket by month with a formatted date and by week with the date the week starts on, not a week number.
  • LAG(value, 12) over monthly data gives year-over-year; compute it in a CTE and filter afterwards.
  • A moving average is AVG(...) OVER (ORDER BY day ROWS BETWEEN n PRECEDING AND CURRENT ROW) on a gap-free series.
  • Use half-open date ranges and store timestamps in UTC.

Next: INSERT, where we start changing data. For streaks and missing ranges in a series, revisit Gaps and Islands.