SQL
Intermediate 15 min read SQL

Time-Series SQL

The patterns behind every trend chart

Time-series SQL covers date bucketing, period-over-period changes, running totals and comparisons — the patterns behind almost every line chart in BI.

datesbucketingperiod-over-periodrunning totalseasonalitycalendar
Prerequisites: SQL for Analytics
Before we start

What you will learn

  • Bucket events into fixed periods with date functions
  • Compute period-over-period and running totals
  • Handle seasonality and calendar gaps honestly
Introduction

What is this?

Time-series SQL shapes events over time: truncating timestamps into days/weeks/months, computing changes vs the prior period, running totals and year-over-year comparisons. Correct calendar handling (which week, which timezone, how to treat missing periods) distinguishes a trustworthy trend from a misleading one.

Motivation

Why do you need it?

  • Most BI dashboards are time series — their correctness is arguably the most important.
  • Period-over-period comparisons silently mislead without a defined 'previous' period.
  • Calendar gaps (no weekend data) can break naive running totals.
  • Seasonality means a month-over-month jump may be noise; SQL lets you compute YoY.
Think about it

Mental model

Imagine

A tide table

Think of

Time-series bucketing

You read high tides by the hour but plan by the week — the same data, bucketed to the decision's horizon. Bucketing choices (hour/day/week) exist to match the decision, not the raw data.

Mechanics

How it works

  • DATE_TRUNC/CAST normalize timestamps into period buckets.
  • GROUPS/partitions compute per-period aggregates at the chosen cadence.
  • LAG over an ordered partition yields the prior period's value for comparisons.
  • Running totals use SUM(...) OVER (ORDER BY period).
  • Joins to a date dimension expose gaps (days with zero data) so charts stay honest.

From events to a chronological report.

Events
Bucket
Aggregate
Compare
Chart
Hands-on

Examples

Monthly revenue with MoM and YoY

Monthly revenue with MoM and YoY
WITH monthly AS (
  SELECT DATE_TRUNC('month', ts) AS month, SUM(amount) AS revenue
  FROM orders WHERE ts >= '2025-01-01' GROUP BY 1
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       revenue * 1.0 / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) - 1 AS mom,
       LAG(revenue, 12) OVER (ORDER BY month) AS prev_year,
       revenue * 1.0 / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) - 1 AS yoy
FROM monthly ORDER BY month;

LAG(offset) reaches back one month or twelve, giving MoM and YoY deltas from the same ordered partition. NULLIF avoids divide-by-zero on a missing prior period.

Real world

In production

  • A subscription team charts MRR with MoM change bands and a 12-month YoY line to separate growth from seasonality.
  • A retail weekly report buckets sales by ISO week using a calendar dimension index.
  • An ops SLA report tracks rolling-7-day on-time % with a running window over the last N days.
Guidelines

Best practices

  • Bucketing in the report query — but keep a calendar dimension to fill gaps.
  • Define 'prior period' explicitly (last full calendar month, not last 30 days).
  • Use a consistent timezone for all event timestamps before bucketing.
  • For trends, show both value and its YoY; a green MoM spike in January may be seasonal.
Watch out

Common mistakes

  • Bucketing in the BI tool inconsistently with the SQL (week starts differ).
  • Comparing an incomplete current month to a full prior month.
  • Forgetting daylight-saving/gap days in daily aggregates.
  • Using 'last 30 days' where calendar periods are the decision's frame.
Root cause analysis

Common issues

Error
Monday totals appear half-empty
Root cause

Week bucketing starts Sunday, splitting the workweek

Fix

Force the week start via ISO week semantics or your date dimension.

Error
MoM spike every January
Root cause

Seasonality, not real growth

Fix

Add the YoY comparison to keep the report honest.

When things break

Debugging tips

  • If weekly totals seem low, check which day starts the week in your bucketing.
  • If YoY is empty, verify the dates cover 12 months of history.
  • If a running total jumps, look for a gap or duplicated date in the order by key.
Interviews

Interview checkpoint

Practice

Hands-on lab

Measure Revenue Trends in the Warehouse
40 min · intermediate
In progress
Objectives
  • Bucket events into days and months
  • Compute MoM and running total
  • Handle a weekend gap honestly

Measure Revenue Trends in the Warehouse

40 min · intermediate

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which SQL function pattern computes a period-over-period change?

2

Comparing a partial current month to a full previous month gives a fair MoM.

Go deeper

Official documentation

PostgreSQL date/time functions
docs