Window functions

In b3 you learned GROUP BY - it collapses many rows into one summary per group. Powerful, but it throws the detail away. A window function does the opposite: it computes a group-level number and keeps every row, adding the result as a new column. That is how you rank products by price without losing the products, or show a running revenue total next to each month. The keyword is OVER, and once it clicks, you will use it constantly.

Here is the whole idea in one sentence: GROUP BY gives you one row per group; a window function gives you every row plus a group-level column. The OVER (...) clause defines the "window" - the set of rows the function looks at - while your row stays right where it is. That is why you can show each product's price and its rank in the same result.

SELECT name, price,
       RANK() OVER (ORDER BY price DESC) AS price_rank
FROM products;

SELECT name, price,
       SUM(price) OVER () AS catalog_total
FROM products;

Three ranking functions, one small but important difference. ROW_NUMBER gives a unique number to every row (1,2,3,4). RANK gives ties the same number then skips (1,1,3). DENSE_RANK gives ties the same number without skipping (1,1,2). And PARTITION BY restarts the ranking within each group - "rank customers within each plan", "top product per category".

WITH spend AS (
  SELECT o.customer_id, SUM(oi.quantity*oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON o.order_id=oi.order_id
  WHERE o.status='completed'
  GROUP BY o.customer_id
)
SELECT c.name, ROUND(s.total,2) AS total,
       RANK() OVER (ORDER BY s.total DESC) AS rnk
FROM spend s
JOIN customers c ON c.customer_id=s.customer_id
LIMIT 5;

SELECT name, price,
       ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num,
       RANK()       OVER (ORDER BY price DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY price DESC) AS dense
FROM products;

The last two tools are the ones analysts reach for weekly. SUM(x) OVER (ORDER BY ...) builds a running total - each row adds itself to everything before it. LAG(x) reaches back to the previous row's value (and LEAD forward), which is exactly what month-over-month change needs: this month minus last month.

WITH m AS (
  SELECT substr(o.order_date,1,7) AS mo,
         SUM(oi.quantity*oi.unit_price) AS rev
  FROM orders o
  JOIN order_items oi ON o.order_id=oi.order_id
  WHERE o.status='completed'
  GROUP BY mo
)
SELECT mo, rev,
       SUM(rev) OVER (ORDER BY mo) AS running
FROM m
ORDER BY mo;

WITH m AS (
  SELECT substr(o.order_date,1,7) AS mo,
         SUM(oi.quantity*oi.unit_price) AS rev
  FROM orders o
  JOIN order_items oi ON o.order_id=oi.order_id
  WHERE o.status='completed'
  GROUP BY mo
)
SELECT mo, rev,
       rev - LAG(rev) OVER (ORDER BY mo) AS change
FROM m
ORDER BY mo;

Let us put the two Part 3 queries side by side and actually read them like an analyst. The verified monthly completed revenue is Jan 209, Feb 271, Mar 150, Apr 239, May 183, Jun 168. Your job: run both, then narrate what the change column is telling you - because that March dip is the mystery session b9 investigates end to end.

WITH m AS (
  SELECT substr(o.order_date,1,7) AS mo,
         SUM(oi.quantity*oi.unit_price) AS rev
  FROM orders o
  JOIN order_items oi ON o.order_id=oi.order_id
  WHERE o.status='completed'
  GROUP BY mo
)
SELECT mo, rev,
       SUM(rev) OVER (ORDER BY mo) AS running,
       rev - LAG(rev) OVER (ORDER BY mo) AS change
FROM m
ORDER BY mo;

• Rank products within each category: add PARTITION BY category to RANK() OVER (...) and read how the rank restarts per category.
• Add a LEAD(rev) column to the monthly query to show next month's revenue next to the current one.
• Build a running count of orders over time: COUNT(*) OVER (ORDER BY order_date) against the orders table.
• Wrap the spend-rank query in an outer query and keep only rnk <= 3 - your first top-N result powered by a window.
• Bring the March dip to session b9. You found it with LAG; next you will use joins and filters to explain why it happened.

This session teaches the window-function core of the major free SQL curricula, run on live Daybreak data instead of screenshots. Graded problem sets and certificates stay on the official sites (linked below). This page covers: