←Back to the bookChapter 154 · Notebook 4 · Window Functions

▶ Open in Colab⬇ Download notebookView on GitHub

Chapter 154 · Tools & Workflow · Notebook 4 of 5

Window Functions: Rank and Running Totals

A GROUP BY collapses each group to one row. A window function computes a group-wise number but keeps every row, so you can rank within a group, add a running total, or compare each row to the one before. This is where SQL starts to feel like a superpower.

import pandas as pd, sqlite3
BASE = "https://raw.githubusercontent.com/johnfisher-ai/Statistics-Data-Science-AI-Visual-Book/main/data/"
fn = "sql-and-databases--shop.xlsx"
try:
    xls = pd.ExcelFile("../../data/" + fn)
except FileNotFoundError:
    xls = pd.ExcelFile(BASE + fn)
# Load the four sheets into an in-memory SQLite database, then query it with real SQL.
con = sqlite3.connect(":memory:")
for table in ["customers", "products", "orders", "order_items"]:
    pd.read_excel(xls, sheet_name=table).to_sql(table, con, index=False, if_exists="replace")

def q(sql):
    """Run a SQL query against the shop database and return the result as a DataFrame."""
    return pd.read_sql_query(sql, con)

print("tables:", [r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type='table'")])
q("SELECT * FROM products LIMIT 3")

tables: ['customers', 'products', 'orders', 'order_items']

	product_id	product	category	unit_price
0	1	Cold Brew Kit	Equipment	39
1	2	Pour-Over Set	Equipment	28
2	3	Espresso Beans 1kg	Beans	22

DEMO 1 · The idea: aggregate without collapsing

SUM() OVER () totals a column but attaches the total to every row, so each line can show its own value and the whole at once. The OVER clause is what makes a function a window function.

q("""SELECT product_id, revenue,
              ROUND(SUM(revenue) OVER (), 2) AS grand_total,
              ROUND(100.0 * revenue / SUM(revenue) OVER (), 3) AS pct_of_total
       FROM order_items
       ORDER BY revenue DESC
       LIMIT 5""")

	product_id	revenue	grand_total	pct_of_total
0	8	595.0	114592.4	0.519
1	8	595.0	114592.4	0.519
2	8	595.0	114592.4	0.519
3	8	595.0	114592.4	0.519
4	8	595.0	114592.4	0.519

DEMO 2 · RANK and ROW_NUMBER

OVER (ORDER BY ...) ranks rows. Here we rank products by total revenue. First we aggregate to one row per product, then rank those. RANK ties share a number; ROW_NUMBER never ties.

q("""WITH prod AS (
         SELECT product_id, SUM(revenue) AS revenue
         FROM order_items GROUP BY product_id)
       SELECT product_id,
              ROUND(revenue, 2) AS revenue,
              RANK()       OVER (ORDER BY revenue DESC) AS rnk,
              ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num
       FROM prod
       ORDER BY revenue DESC
       LIMIT 6""")

	product_id	revenue	rnk	row_num
0	12	20722.50	1	1
1	1	18879.90	2	2
2	8	14833.35	3	3
3	3	12521.30	4	4
4	2	11198.60	5	5
5	5	7756.75	6	6

DEMO 3 · PARTITION BY: rank within each group

PARTITION BY restarts the window for each group, so you can rank customers within their own region. To keep only the top of each region, compute the rank in one step and filter it in the next (SQLite has no QUALIFY, so we wrap it in a subquery).

q("""WITH spend AS (
         SELECT c.region, c.name, SUM(oi.revenue) AS spend
         FROM order_items oi
         JOIN orders o    ON oi.order_id   = o.order_id
         JOIN customers c ON o.customer_id = c.customer_id
         GROUP BY c.customer_id),
       ranked AS (
         SELECT region, name, spend,
                RANK() OVER (PARTITION BY region ORDER BY spend DESC) AS rank_in_region
         FROM spend)
       SELECT region, name, ROUND(spend, 2) AS spend, rank_in_region
       FROM ranked
       WHERE rank_in_region = 1
       ORDER BY spend DESC""")

	region	name	spend	rank_in_region
0	Central	Customer 27	5495.55	1
1	West	Customer 07	5247.60	1
2	North	Customer 34	4030.05	1
3	South	Customer 25	3644.95	1
4	East	Customer 02	3428.00	1

DEMO 4 · Running total over time

A running (cumulative) total is a window ordered by time. Summarize revenue by month, then let SUM() OVER (ORDER BY month) accumulate it, the classic finance chart in one clause.

q("""WITH monthly AS (
         SELECT strftime('%Y-%m', o.order_date) AS month, SUM(oi.revenue) AS rev
         FROM order_items oi JOIN orders o ON oi.order_id = o.order_id
         GROUP BY month)
       SELECT month,
              ROUND(rev, 2) AS revenue,
              ROUND(SUM(rev) OVER (ORDER BY month), 2) AS running_total
       FROM monthly
       ORDER BY month""")

	month	revenue	running_total
0	2024-01	9643.20	9643.20
1	2024-02	6706.90	16350.10
2	2024-03	11262.10	27612.20
3	2024-04	7896.30	35508.50
4	2024-05	10216.55	45725.05
5	2024-06	9878.40	55603.45
6	2024-07	12909.45	68512.90
7	2024-08	11024.20	79537.10
8	2024-09	7910.25	87447.35
9	2024-10	9000.95	96448.30
10	2024-11	9311.60	105759.90
11	2024-12	8832.50	114592.40

DEMO 5 · LAG: compare each row to the previous

LAG reaches back to the prior row, so month-over-month growth is a single expression: this month's revenue minus last month's, over last month's.

q("""WITH monthly AS (
         SELECT strftime('%Y-%m', o.order_date) AS month, SUM(oi.revenue) AS rev
         FROM order_items oi JOIN orders o ON oi.order_id = o.order_id
         GROUP BY month)
       SELECT month,
              ROUND(rev, 2) AS revenue,
              ROUND(rev - LAG(rev) OVER (ORDER BY month), 2) AS change_vs_prev,
              ROUND(100.0 * (rev - LAG(rev) OVER (ORDER BY month)) / LAG(rev) OVER (ORDER BY month), 1) AS pct_change
       FROM monthly
       ORDER BY month""")

	month	revenue	change_vs_prev	pct_change
0	2024-01	9643.20	NaN	NaN
1	2024-02	6706.90	-2936.30	-30.4
2	2024-03	11262.10	4555.20	67.9
3	2024-04	7896.30	-3365.80	-29.9
4	2024-05	10216.55	2320.25	29.4
5	2024-06	9878.40	-338.15	-3.3
6	2024-07	12909.45	3031.05	30.7
7	2024-08	11024.20	-1885.25	-14.6
8	2024-09	7910.25	-3113.95	-28.2
9	2024-10	9000.95	1090.70	13.8
10	2024-11	9311.60	310.65	3.5
11	2024-12	8832.50	-479.10	-5.1

Wrap-up

A window function is an aggregate with an OVER clause that keeps every row: SUM() OVER for running totals and shares, RANK and ROW_NUMBER for rankings, PARTITION BY to rank within groups, and LAG or LEAD to compare across rows. These answer the ranking, cumulative, and period-over-period questions that a plain GROUP BY cannot. Last, we combine SQL with subqueries and hand the result to pandas.

←Back to the bookChapter 154 · Notebook 4 · Window Functions