Window Frames, Running Totals & Time Analysis — SQL Course

What you'll learn

Control ROWS and RANGE frames to build running totals, moving averages, deltas, and period comparisons correctly. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply Window frames to a realistic data question
Apply ROWS vs RANGE to a realistic data question
Apply Running totals to a realistic data question
Apply Moving averages to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Window frame	The subset of a partition visible to a frame-sensitive function	State ROWS or RANGE explicitly for running analytics
ROWS	A frame based on physical row positions	Use with deterministic order when each row advances the measure
LAG	A value from a prior row in window order	Use for period deltas after establishing one row per period

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the window-frame analysis question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Calculate running and prior-month revenue

ROWS gives a true row-by-row running total, while LAG compares the current monthly bucket with its predecessor.

revenue_trend.sql

SELECT
  month,
  revenue,
  sum(revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_revenue,
  revenue - lag(revenue) OVER (ORDER BY month) AS change_from_prior
FROM analytics.monthly_revenue
ORDER BY month;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

The default RANGE frame groups peers with equal ordering values, which can make a running total jump unexpectedly.

Independent workshop

Build a review-ready window-frame analysis lab against the course commerce dataset.

Your finished workshop must include:

Window frames
ROWS vs RANGE
Running totals
Moving averages
LAG and LEAD
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Window frame: State ROWS or RANGE explicitly for running analytics
ROWS: Use with deterministic order when each row advances the measure
LAG: Use for period deltas after establishing one row per period

Quick check

1. Which rule best applies to Window frame?

2. Which rule best applies to ROWS?

3. Which rule best applies to LAG?

Next: GROUPING SETS, ROLLUP & CUBE