SQL lesson
Running Totals, Frames, Rolling Metrics, and Percent of Total
Practice Running Totals, Frames, Rolling Metrics, and Percent of Total with a worked query, common mistakes, and a checked next step. Use explicit frames for cumulative and rolling calculations.
What you will learn
Use explicit frames for cumulative and rolling calculations.
Before writing SQL, name what one result row should mean for Running Totals, Frames, Rolling Metrics, and Percent of Total.
Learning objectives and prerequisites
Each objective names an observable SQL behavior, with prerequisite topics linked in the order you need them.
Plan about 20 minutes for the explanation, worked example, prediction, and first hands-on attempt. Delayed review adds practice later.
- Objective: Use explicit frames for cumulative and rolling calculations.
- Skill focus: running-metrics
- Prerequisite: review LAG, LEAD, and Period Change before this lesson.
Example query and result
Study the compact worked query first, then open the editor when you are ready to change and run it.
- Starting rows: one staged analytical row per entity/period.
- Result rows: one analytical result row with explicit ordering and frame behavior.
SELECT customer_id, order_id, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at, order_id) AS order_rank FROM orders ORDER BY customer_id, order_rank;| Example output | Meaning |
|---|---|
| C001 | O1001 | 1 | first order for the customer |
| C001 | O1005 | 2 | next order after deterministic tie handling |
Syntax pattern
Use the syntax pattern as a shape, not as a memorized answer. Replace table, column, condition, grouping, and ordering names according to the stated grain.
WITH staged AS (SELECT ... ) SELECT ... FROM staged;| Input grain | Output grain | Validation focus |
|---|---|---|
| one staged analytical row per entity/period | one analytical result row with explicit ordering and frame behavior | Rows, order, duplicates, nulls, and edge cases |
Common mistakes and why they fail
A common mistake is matching the visible rows while ignoring ordering, duplicate policy, null behavior, tie behavior, or the stated result grain.
Why it fails: Running Totals, Frames, Rolling Metrics, and Percent of Total checks changed examples, so a query that only copies visible rows can break when row counts, labels, nulls, or ties change.
PostgreSQL dialect notes stay explicit when syntax, date handling, transaction behavior, or comparison semantics matter.
Predict the result before you run it
Before opening the app, predict the output grain, the first column, and one edge case that could change the answer.
This is a reading prompt only; the app opens only when you choose to practice.
- Prediction: name one row that should appear or one row that should be excluded.
- Check: explain whether nulls, duplicates, ties, or missing relationships affect the result.
- Transfer: say what would change on a second dataset with different labels and counts.
Practice and related resources
Move to the app when you want the editor, checks, hints, and solution review.
Page highlights
- Clear task requirements
- Learning objectives and prerequisite links
- Original worked query and readable result example
- What each result row means
- Misconception and edge-case notes
- Predict the result before running the query
- Previous and next lesson navigation