Time-Series & Period-over-Period
Expert · 24 min read · ▶ live playground · ✦ checkpoint
Time-series SQL answers "what changed over time?" without hiding the days when nothing happened. The core pattern is a date spine: the complete list of dates or periods your report refuses to lose.
The café app stores event rows only when a user does something. A time-series report needs a different grain: one row per day, week, or month, even when the app had no purchases.
Build A Gapless Daily Series
DATE_TRUNC folds timestamps down to a period boundary. For day-grain reporting, date_trunc('day', event_ts) makes all events on the same calendar day share one bucket.
The trap is that grouping facts alone only returns days with facts. Build the date spine first, then LEFT JOIN purchases onto it:
WITH date_spine AS (
SELECT gs.day_start::date AS activity_day
FROM generate_series(
DATE '2026-01-01',
DATE '2026-01-10',
INTERVAL '1 day'
) AS gs(day_start)
),
daily_purchases AS (
SELECT CAST(date_trunc('day', e.event_ts) AS date) AS activity_day,
count(*) AS purchase_events,
sum(e.purchase_amount) AS revenue
FROM cafe_app_events e
WHERE e.event_type = 'purchase'
AND e.event_ts >= TIMESTAMP '2026-01-01'
AND e.event_ts < TIMESTAMP '2026-01-11'
GROUP BY CAST(date_trunc('day', e.event_ts) AS date)
)
SELECT ds.activity_day,
COALESCE(dp.purchase_events, 0) AS purchase_events,
COALESCE(dp.revenue, 0.00) AS revenue
FROM date_spine ds
LEFT JOIN daily_purchases dp ON dp.activity_day = ds.activity_day
ORDER BY ds.activity_day; activity_day | purchase_events | revenue
--------------+-----------------+---------
2026-01-01 | 0 | 0.00
2026-01-02 | 0 | 0.00
2026-01-03 | 1 | 14.50
2026-01-04 | 0 | 0.00
2026-01-05 | 0 | 0.00
2026-01-06 | 1 | 11.00
2026-01-07 | 0 | 0.00
2026-01-08 | 0 | 0.00
2026-01-09 | 0 | 0.00
2026-01-10 | 0 | 0.00
(10 rows)That grid is honest because the output grain is one calendar day. The zeros are not fake sales; they are the visible absence of matching purchase rows.
Running Totals And Moving Averages
A running total is the cumulative sum from the start of the ordered series through the current row. A moving average is the average over a sliding window of recent rows.
Both are window functions over the gapless daily series. The date spine matters here: without zero days, a 7-row average might mean seven purchase days, not seven calendar days.
WITH date_spine AS (
SELECT gs.day_start::date AS activity_day
FROM generate_series(
DATE '2026-01-01',
DATE '2026-01-14',
INTERVAL '1 day'
) AS gs(day_start)
),
daily_revenue AS (
SELECT CAST(date_trunc('day', e.event_ts) AS date) AS activity_day,
sum(e.purchase_amount) AS revenue
FROM cafe_app_events e
WHERE e.event_type = 'purchase'
AND e.event_ts >= TIMESTAMP '2026-01-01'
AND e.event_ts < TIMESTAMP '2026-01-15'
GROUP BY CAST(date_trunc('day', e.event_ts) AS date)
),
daily AS (
SELECT ds.activity_day,
COALESCE(dr.revenue, 0.00) AS revenue
FROM date_spine ds
LEFT JOIN daily_revenue dr ON dr.activity_day = ds.activity_day
)
SELECT d.activity_day,
d.revenue,
sum(d.revenue) OVER (
ORDER BY d.activity_day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_revenue,
round(
avg(d.revenue) OVER (
ORDER BY d.activity_day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
),
2
) AS seven_day_avg_revenue
FROM daily d
ORDER BY d.activity_day; activity_day | revenue | running_revenue | seven_day_avg_revenue
--------------+---------+-----------------+-----------------------
2026-01-01 | 0.00 | 0.00 | 0.00
2026-01-02 | 0.00 | 0.00 | 0.00
2026-01-03 | 14.50 | 14.50 | 4.83
2026-01-04 | 0.00 | 14.50 | 3.63
2026-01-05 | 0.00 | 14.50 | 2.90
2026-01-06 | 11.00 | 25.50 | 4.25
2026-01-07 | 0.00 | 25.50 | 3.64
2026-01-08 | 0.00 | 25.50 | 3.64
2026-01-09 | 0.00 | 25.50 | 3.64
2026-01-10 | 0.00 | 25.50 | 1.57
2026-01-11 | 0.00 | 25.50 | 1.57
2026-01-12 | 0.00 | 25.50 | 1.57
2026-01-13 | 0.00 | 25.50 | 0.00
2026-01-14 | 0.00 | 25.50 | 0.00
(14 rows)Read the frame out loud. UNBOUNDED PRECEDING means "from the beginning." 6 PRECEDING plus the current row makes a seven-row frame. At the start of the report, the frame is smaller because earlier days do not exist in the output yet.
Period-Over-Period With LAG
Period-over-period comparison means comparing each period to the matching previous period: week over week, month over month, or year over year. LAG gives each row the previous period's metric so ordinary subtraction can do the rest.
This query builds week and month spines, joins revenue to them, then computes WoW and MoM changes with the same shape. PostgreSQL labels weeks by Monday start, so January 1-3, 2026 belong to the week starting Monday 2025-12-29; that row label is the period start, not prior-year café activity.
WITH weekly_spine AS (
SELECT 1 AS period_order,
'WoW' AS comparison,
gs.week_start::date AS period_start
FROM generate_series(
DATE '2025-12-29',
DATE '2026-02-02',
INTERVAL '1 week'
) AS gs(week_start)
),
monthly_spine AS (
SELECT 2 AS period_order,
'MoM' AS comparison,
gs.month_start::date AS period_start
FROM generate_series(
DATE '2026-01-01',
DATE '2026-03-01',
INTERVAL '1 month'
) AS gs(month_start)
),
period_spine AS (
SELECT ws.period_order,
ws.comparison,
ws.period_start
FROM weekly_spine ws
UNION ALL
SELECT ms.period_order,
ms.comparison,
ms.period_start
FROM monthly_spine ms
),
purchase_periods AS (
SELECT 'WoW' AS comparison,
CAST(date_trunc('week', e.event_ts) AS date) AS period_start,
sum(e.purchase_amount) AS revenue
FROM cafe_app_events e
WHERE e.event_type = 'purchase'
GROUP BY CAST(date_trunc('week', e.event_ts) AS date)
UNION ALL
SELECT 'MoM' AS comparison,
CAST(date_trunc('month', e.event_ts) AS date) AS period_start,
sum(e.purchase_amount) AS revenue
FROM cafe_app_events e
WHERE e.event_type = 'purchase'
GROUP BY CAST(date_trunc('month', e.event_ts) AS date)
),
period_sales AS (
SELECT ps.period_order,
ps.comparison,
ps.period_start,
COALESCE(pp.revenue, 0.00) AS revenue
FROM period_spine ps
LEFT JOIN purchase_periods pp
ON pp.comparison = ps.comparison
AND pp.period_start = ps.period_start
),
with_previous AS (
SELECT ps.period_order,
ps.comparison,
ps.period_start,
ps.revenue,
lag(ps.revenue) OVER (
PARTITION BY ps.comparison
ORDER BY ps.period_start
) AS previous_revenue
FROM period_sales ps
)
SELECT wp.comparison,
wp.period_start,
wp.revenue,
wp.previous_revenue,
wp.revenue - wp.previous_revenue AS revenue_delta,
round(
100.0 * (wp.revenue - wp.previous_revenue) / NULLIF(wp.previous_revenue, 0),
1
) AS growth_pct
FROM with_previous wp
ORDER BY wp.period_order, wp.period_start; comparison | period_start | revenue | previous_revenue | revenue_delta | growth_pct
------------+--------------+---------+------------------+---------------+------------
WoW | 2025-12-29 | 14.50 | NULL | NULL | NULL
WoW | 2026-01-05 | 11.00 | 14.50 | -3.50 | -24.1
WoW | 2026-01-12 | 8.75 | 11.00 | -2.25 | -20.5
WoW | 2026-01-19 | 24.00 | 8.75 | 15.25 | 174.3
WoW | 2026-01-26 | 13.60 | 24.00 | -10.40 | -43.3
WoW | 2026-02-02 | 42.05 | 13.60 | 28.45 | 209.2
MoM | 2026-01-01 | 71.85 | NULL | NULL | NULL
MoM | 2026-02-01 | 71.40 | 71.85 | -0.45 | -0.6
MoM | 2026-03-01 | 75.15 | 71.40 | 3.75 | 5.3
(9 rows)The first row in each comparison has no previous period, so the change is NULL. If a previous period has zero revenue, NULLIF keeps the percent change undefined instead of pretending infinity is a useful dashboard number.
YoY Needs Twelve Prior Months
Year over year compares a period to the same period one year earlier. The café seed has January through March 2026 only, so it cannot honestly produce observed YoY results.
Here is the runnable shape on a tiny fixed example, not the café seed:
WITH monthly_sales AS (
SELECT v.month_start,
v.revenue
FROM (VALUES
(DATE '2025-01-01', 180.00::numeric),
(DATE '2025-02-01', 210.00::numeric),
(DATE '2025-03-01', 195.00::numeric),
(DATE '2025-04-01', 205.00::numeric),
(DATE '2025-05-01', 240.00::numeric),
(DATE '2025-06-01', 260.00::numeric),
(DATE '2025-07-01', 255.00::numeric),
(DATE '2025-08-01', 245.00::numeric),
(DATE '2025-09-01', 275.00::numeric),
(DATE '2025-10-01', 300.00::numeric),
(DATE '2025-11-01', 315.00::numeric),
(DATE '2025-12-01', 390.00::numeric),
(DATE '2026-01-01', 218.40::numeric),
(DATE '2026-02-01', 96.40::numeric),
(DATE '2026-03-01', 74.65::numeric)
) AS v(month_start, revenue)
),
compared AS (
SELECT ms.month_start,
ms.revenue,
lag(ms.revenue, 12) OVER (ORDER BY ms.month_start) AS revenue_last_year
FROM monthly_sales ms
)
SELECT c.month_start,
c.revenue,
c.revenue_last_year,
c.revenue - c.revenue_last_year AS yoy_delta,
round(
100.0 * (c.revenue - c.revenue_last_year) / NULLIF(c.revenue_last_year, 0),
1
) AS yoy_pct
FROM compared c
WHERE c.month_start >= DATE '2026-01-01'
ORDER BY c.month_start; month_start | revenue | revenue_last_year | yoy_delta | yoy_pct
-------------+---------+-------------------+-----------+---------
2026-01-01 | 218.40 | 180.00 | 38.40 | 21.3
2026-02-01 | 96.40 | 210.00 | -113.60 | -54.1
2026-03-01 | 74.65 | 195.00 | -120.35 | -61.7
(3 rows)The important part is lag(revenue, 12). Compute the window first, then filter to the displayed year one step later. If you filter to 2026 before the window runs, there are no 2025 rows left for LAG to see.
Unequal Periods Lie Quietly
Seasonality means a metric changes because of calendar patterns, not only product changes: weekdays versus weekends, holidays, payday, summer, exams near a campus branch. The first defense is making sure the periods are comparable.
Before trusting the MoM grid, check how much of each month the seed actually covers:
WITH bounds AS (
SELECT min(e.event_ts)::date AS first_event_day,
max(e.event_ts)::date AS last_event_day
FROM cafe_app_events e
),
month_spine AS (
SELECT gs.month_start::date AS month_start,
(gs.month_start + INTERVAL '1 month' - INTERVAL '1 day')::date AS month_end
FROM generate_series(
DATE '2026-01-01',
DATE '2026-03-01',
INTERVAL '1 month'
) AS gs(month_start)
),
monthly_revenue AS (
SELECT CAST(date_trunc('month', e.event_ts) AS date) AS month_start,
count(*) AS purchase_events,
sum(e.purchase_amount) AS revenue
FROM cafe_app_events e
WHERE e.event_type = 'purchase'
GROUP BY CAST(date_trunc('month', e.event_ts) AS date)
)
SELECT ms.month_start,
COALESCE(mr.purchase_events, 0) AS purchase_events,
COALESCE(mr.revenue, 0.00) AS revenue,
greatest(ms.month_start, b.first_event_day) AS observed_start,
least(ms.month_end, b.last_event_day) AS observed_end,
least(ms.month_end, b.last_event_day) - greatest(ms.month_start, b.first_event_day) + 1 AS observed_days,
CASE
WHEN b.first_event_day <= ms.month_start
AND b.last_event_day >= ms.month_end THEN 'complete'
ELSE 'partial'
END AS coverage
FROM month_spine ms
CROSS JOIN bounds b
LEFT JOIN monthly_revenue mr ON mr.month_start = ms.month_start
ORDER BY ms.month_start; month_start | purchase_events | revenue | observed_start | observed_end | observed_days | coverage
-------------+-----------------+---------+----------------+--------------+---------------+----------
2026-01-01 | 5 | 71.85 | 2026-01-03 | 2026-01-31 | 29 | partial
2026-02-01 | 5 | 71.40 | 2026-02-01 | 2026-02-28 | 28 | complete
2026-03-01 | 6 | 75.15 | 2026-03-01 | 2026-03-28 | 28 | partial
(3 rows)That last grid is why analytics work is more than writing LAG. A partial month can look like growth or decline for reasons that have nothing to do with customer behavior. Name the comparison period, prove the coverage, and only then interpret the percent.
You now have the analytics toolkit for shaping facts into metrics over time. Next, 17.4 zooms out from query patterns to the modern data stack: warehouses, dbt, dimensional models, and how SQL moves through production analytics systems.
Checkpoint
Answer all three to mark this lesson complete