Time-series — moving averages, PoP, gaps — SQL Intermediate Module 6

Time-series SQL is the bread and butter of finance, ops, and growth dashboards. Every chart you've ever built that has 'date' on the x-axis came from a time-series query.

Moving averages and rolling sums

These use window functions over an ORDER BY date with an explicit ROWS or RANGE frame. The 7-day moving average is the canonical example:

sql

SELECT date, daily_signups,
  AVG(daily_signups) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma_7d,
  SUM(daily_signups) OVER (
    ORDER BY date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ) AS trailing_30d_signups
FROM daily_signups
ORDER BY date;

ROWS vs RANGE for time

ROWS BETWEEN 6 PRECEDING uses the previous 6 physical rows — which is wrong if there are missing days. RANGE BETWEEN INTERVAL '6 days' PRECEDING uses calendar days. Use RANGE when missing days matter. Use ROWS when you've already filled the date spine.

Period-over-period change

MoM, YoY, WoW — all the same shape. LAG with a parameter that skips ahead by the right number of rows (or use a date arithmetic JOIN if rows are irregular).

sql

SELECT month, revenue,
  LAG(revenue, 12) OVER (ORDER BY month) AS yoy_baseline,
  revenue - LAG(revenue, 12) OVER (ORDER BY month) AS yoy_abs_change,
  (revenue * 1.0 / LAG(revenue, 12) OVER (ORDER BY month) - 1) AS yoy_pct_change
FROM monthly_revenue
ORDER BY month;

Date spines: filling missing periods

If your underlying table has no rows on days with zero activity, a naive query will skip those days. For a calendar-faithful chart, generate a date spine and LEFT JOIN to it.

sql

-- Postgres / DuckDB
WITH date_spine AS (
  SELECT generate_series(
    '2025-01-01'::date,
    '2025-12-31'::date,
    INTERVAL '1 day'
  )::date AS d
)
SELECT s.d, COALESCE(SUM(o.amount), 0) AS daily_revenue
FROM date_spine s
LEFT JOIN orders o ON DATE(o.order_date) = s.d
GROUP BY s.d
ORDER BY s.d;

For MySQL pre-8 or older engines without generate_series, build the spine from a recursive CTE or a permanent calendar table.

Gap-and-island problems

A classic SQL puzzle: given a table of dates a user was active, find their continuous streaks (islands) and the gaps between them. The standard technique uses a DIFF between two ROW_NUMBERs:

sql

WITH labelled AS (
  SELECT user_id, activity_date,
    activity_date - INTERVAL '1 day' *
      (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) - 1)
      AS streak_anchor
  FROM user_activity
)
SELECT user_id, streak_anchor,
  MIN(activity_date) AS streak_start,
  MAX(activity_date) AS streak_end,
  COUNT(*) AS streak_length
FROM labelled
GROUP BY user_id, streak_anchor
ORDER BY user_id, streak_start;

The streak_anchor trick

If a user is active on consecutive days, activity_date increments by 1 per row and so does ROW_NUMBER — their difference stays constant within a streak and changes when there's a gap. GROUP BY that difference and you've identified each streak.

Exercise

You manage a savings product. Each customer has a savings_deposits(customer_id, deposit_date, amount) table. Write a query that, for each customer, returns: their longest streak of consecutive months in which they made at least one deposit, the start and end of that streak, and the total saved during it.