insideout Get Pro

09 · Window functions, indexes and safe queries

Pro

Engineering Toolkit, lesson 9

Window functions, indexes and safe queries

Running totals and ranks, why a query is slow, and SQL from Python.

"SQL queries: filter, join and group": SELECT, joins, GROUP BY and running SQL from Python.

Part 1. Window functions: a value for every row

GROUP BY answers "how much did each customer spend?" with one row per customer, and the single orders disappear. Some questions need both: each order, and alongside it the customer's running total so far, or whether it's their latest. A window function does that. It computes over a group of rows, called a window, and writes the answer onto every row.

Your turn

The orders table has 7 rows from 4 customers. SELECT customer_id, SUM(total) OVER (PARTITION BY customer_id) FROM orders returns how many rows?

A window function never merges rows. Each of the 7 orders comes back, carrying its customer's total. GROUP BY customer_id would return 4.
A running total, and each customer's latest order
import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, placed_on TEXT, total REAL)")
db.executemany("INSERT INTO orders VALUES (?, ?, ?, ?)", [
    (101, 1, "2026-10-01", 40.0), (102, 2, "2026-10-02", 120.0), (103, 1, "2026-10-05", 25.5),
    (104, 3, "2026-10-06", 80.0), (105, 2, "2026-10-09", 60.0), (106, 4, "2026-10-12", 15.0),
    (107, 1, "2026-10-20", 99.0)])
running = """
SELECT customer_id, placed_on, total,
       SUM(total) OVER (PARTITION BY customer_id ORDER BY placed_on) AS so_far
FROM orders WHERE customer_id = 1
"""
for row in db.execute(running):
    print(row)
latest = """
SELECT customer_id, id, placed_on FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_on DESC) AS n
    FROM orders)
WHERE n = 1
"""
print(db.execute(latest).fetchall())
# (1, '2026-10-01', 40.0, 40.0)
# (1, '2026-10-05', 25.5, 65.5)
# (1, '2026-10-20', 99.0, 164.5)
# [(1, 107, '2026-10-20'), (2, 105, '2026-10-09'), (3, 104, '2026-10-06'), (4, 106, '2026-10-12')]

PARTITION BY splits the rows into groups, here one per customer, and ORDER BY inside OVER sets the order within each group. With an order, SUM becomes a running total. ROW_NUMBER numbers each customer's orders newest first, so n = 1 keeps the latest one: a pattern you'll write often, and a favourite in interviews.

LAG reaches back to the previous row in the window, which is how daily returns are worked out from closing prices, in SQL.

Daily returns with LAG
import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE prices (day TEXT, ticker TEXT, close REAL)")
db.executemany("INSERT INTO prices VALUES (?, ?, ?)", [
    ("2026-11-02", "KITE", 100.0), ("2026-11-03", "KITE", 102.0), ("2026-11-04", "KITE", 99.96),
    ("2026-11-02", "GULL", 50.0), ("2026-11-03", "GULL", 49.5), ("2026-11-04", "GULL", 50.49)])
query = """
SELECT ticker, day, close,
       ROUND(close / LAG(close) OVER (PARTITION BY ticker ORDER BY day) - 1, 4) AS ret
FROM prices
ORDER BY ticker, day
"""
for row in db.execute(query):
    print(row)
# ('GULL', '2026-11-02', 50.0, None)
# ('GULL', '2026-11-03', 49.5, -0.01)
# ('GULL', '2026-11-04', 50.49, 0.02)
# ('KITE', '2026-11-02', 100.0, None)
# ('KITE', '2026-11-03', 102.0, 0.02)
# ('KITE', '2026-11-04', 99.96, -0.02)

Window function

A function such as SUM, ROW_NUMBER, RANK or LAG followed by OVER (...). It computes across a group of related rows and returns a value for every row, without merging them.

Part 2. Why a query is slow

Keep going with Pro

You've done the first part. Pro opens the rest of this lesson, and every other one.

Still to come
  1. Why a query is slow
  2. Which index helps which query
  3. Querying from Python, safely
See Pro plansI have Pro: sign in
Next lessonHTTP APIs with FastAPIHow programs talk over HTTP, and a small API that checks its input. Start lesson 10

Engineering Toolkit overviewSpotted a mistake? Tell us.