--- title: "Window Functions and Running Calculations" description: "A window function calculates for each row using rows from its partition, without collapsing those rows. Partitioning and ordering support ranks, running totals and previous-row comparisons. Aggregate " canonical: https://lightmysky.com/learn/computing/window-functions-and-running-calculations-mt_aaVxFPjWQv source: https://lightmysky.com/learn/computing/window-functions-and-running-calculations-mt_aaVxFPjWQv.md retrieved: 2026-11-12 --- > **Agent view.** This is the Markdown twin of the page, for tools and assistants. > When to use this site, and the call that answers each job: https://lightmysky.com/agent-instructions.md > API description (OpenAPI 3.1): https://lightmysky.com/openapi.json · Authentication: https://lightmysky.com/auth.md > Pricing: https://lightmysky.com/pricing.md · Catalog: https://lightmysky.com/llms.txt · Full catalog: https://lightmysky.com/llms-full.txt > Every machine-readable file on this domain: https://lightmysky.com/.well-known/ai-catalog.json > Ask for Markdown with `Accept: text/markdown`, a `.md` address, or `?mode=agent`. # Window Functions and Running Calculations A window function calculates for each row using rows from its partition, without collapsing those rows. Partitioning and ordering support ranks, running totals and previous-row comparisons. Aggregate frames define which rows contribute to each sum. Subject: Computing · Area: Data & Databases · Ages 19 to 20 Page: https://lightmysky.com/learn/computing/window-functions-and-running-calculations-mt_aaVxFPjWQv ## Ready when they can - Compute a repeatable running total per customer using a stated order and an explicit row frame - Rank rows within a partition and handle ties deliberately - Explain how a window keeps individual orders while grouping only by customer produces customer summaries ## Lesson: Totals that keep every row You have used queries to choose which rows appear. A window function adds a calculation to each row that reaches the window stage; it does not collapse those rows. GROUP BY customer instead produces one summary row per customer. OVER defines the window. PARTITION BY c separates customers. SUM(a) OVER(PARTITION BY c), without window ordering, gives each customer's full total beside each of their rows. **Example.** Sales(c,id,a) stores customer, unique non-null order id and amount. Here id follows order time. SELECT c,id,a,SUM(a) OVER(PARTITION BY c ORDER BY id ROWS UNBOUNDED PRECEDING) FROM Sales adds a running total. A's rows (1,4) and (2,7) get totals 4 and 11. B's row (3,6) gets 6. Each customer has a separate sum. *(drawing: Each cell shows a customer, order id, amount and running total. B's total excludes A's amounts.)* These examples use PostgreSQL. ROWS UNBOUNDED PRECEDING means a frame from the partition's first row through the current row, in window order. With ORDER BY but no frame, the default aggregate frame also includes all rows tied with the current row. A unique window ordering key makes row-by-row totals repeatable. ORDER BY outside OVER sorts the output; it does not set the window order. RANK() OVER(PARTITION BY c ORDER BY score DESC) ranks each customer's scores, highest first. Scores 90,90,75 get 1,1,3: ties share rank and leave gaps. DENSE_RANK() gives 1,1,2. ROW_NUMBER() gives distinct numbers 1,2,3. Add a unique id to its ORDER BY for repeatable numbering within tied scores. Do not add id to RANK or DENSE_RANK when equal scores must share a rank. *(drawing: Within one customer partition, rank means RANK and dense means DENSE_RANK. Equal scores share a rank; only RANK skips a number.)* LAG(a) OVER(PARTITION BY c ORDER BY id) returns the previous row's amount within each customer partition. For A's amounts 4 then 7, the previous amounts are NULL then 4. B's only amount, 6, has NULL. With no default supplied, LAG returns NULL when there is no previous row. It looks one row back in window order, not for an id exactly one less. **Recap.** Windows keep detail rows. Partitions separate calculations, row frames define running sums, and ranking ties need a stated rule. *The lesson, questions and flashcards were written for this dataset. The SQL examples use PostgreSQL conventions.* ## Practice 8 questions on this page, each with its working shown. ## Needs first - [SQL at Depth: Subqueries, Set Operations and Views](https://lightmysky.com/learn/computing/sql-at-depth-subqueries-set-operations-and-views-mt_SLKUlC-7or) ## Optional connections - [SQL: Joining Tables and Summarising Groups](https://lightmysky.com/learn/computing/sql-joining-tables-and-summarising-groups-mt_0QPZDE2KuS) ## Opens up - [Indexes: B-Trees and the Cost of a Lookup](https://lightmysky.com/learn/computing/indexes-b-trees-and-the-cost-of-a-lookup-mt_1Z5EQTqooI)