SQL Window Functions You Actually Need for Data Science

1. Why Window Functions Matter: The Problem They Solve

Calculating a running total in SQL? A standard GROUP BY won’t get you there. It works like a trash compactor: many rows go in, one row comes out. You get the total, but the individual transaction details are gone.

Here’s what happens when we try to show each purchase alongside the total spend using a naive approach.

import pandas as pd
import sqlite3

# Let's set up a quick in-memory database to simulate SQL
conn = sqlite3.connect(':memory:')

data = pd.DataFrame({
    'user_id': [1, 1, 1, 2, 2],
    'amount': [10, 20, 30, 50, 10]
})
data.to_sql('sales', conn, index=False)

# The Naive Approach: This fails to show row-level detail
query_naive = """
SELECT user_id, SUM(amount) as total_spend
FROM sales
GROUP BY user_id
"""
print("Naive Output (Rows are collapsed):")
print(pd.read_sql(query_naive, conn))

# The Window Function Approach: Aggregation without collapsing
query_window = """
SELECT user_id, amount, 
       SUM(amount) OVER(PARTITION BY user_id) as total_user_spend
FROM sales
"""
print("\nWindow Function Output (Row detail preserved):")
print(pd.read_sql(query_window, conn))

What this actually means: The first result gives us only two rows. We’ve lost the context of when the money was spent. The second result is different — the window function SUM(...) OVER(...) calculated the total but kept all five original rows. The naive approach couldn’t show us both the tree and the forest at once.

2. The Core Idea: Partitioning and Ordering

Window functions create a “window” of rows for each record. For every row in your table, the database looks at a specific subset of other rows to perform a calculation.

• PARTITION BY: Think GROUP BY, but for windows. It tells SQL to only look at rows sharing the same value as the current row (e.g., the same user_id).
• ORDER BY: This defines the sequence. For a running total, the order matters.

# Let's look at how ordering changes the calculation
query_partition = """
SELECT user_id, amount, 
       SUM(amount) OVER(PARTITION BY user_id ORDER BY amount) as running_total
FROM sales
"""
print("Partitioned and Ordered Running Total:")
print(pd.read_sql(query_partition, conn))

In this output, the running_total column resets for User 2. Within User 1’s partition, the numbers add up sequentially (10, 30, 60). The window “slides” row by row.

3. ROW_NUMBER, RANK, and DENSE_RANK: Numbering Rows

This trips up a lot of candidates. The three functions look identical until a tie shows up. Say three products each sold exactly 100 units.

• ROW_NUMBER(): A strict counter. Ties don’t matter — it gives 1, 2, 3.
• RANK(): Ties get the same number, but the next one skips. So 1, 1, 3.
• DENSE_RANK(): Ties get the same number, no skipping. So 1, 1, 2.

ties_data = pd.DataFrame({
    'product': ['A', 'B', 'C', 'D'],
    'sales': [100, 100, 50, 10]
})
ties_data.to_sql('products', conn, index=False, if_exists='replace')

query_ranks = """
SELECT product, sales,
       ROW_NUMBER() OVER(ORDER BY sales DESC) as row_num,
       RANK() OVER(ORDER BY sales DESC) as rank_num,
       DENSE_RANK() OVER(ORDER BY sales DESC) as dense_rank_num
FROM products
"""
print(pd.read_sql(query_ranks, conn))

Interpretation: Look at product ‘C’. RANK calls it the 3rd item because two items tied for 1st. DENSE_RANK calls it 2nd because it only counts unique values above it. If an interviewer asks for the “top 3,” it’s worth asking how they want ties handled.

4. LAG and LEAD: Looking at Previous and Next Rows

LAG pulls the row before the current one. LEAD pulls the row after it. These two are the backbone of time-series analysis.

# Calculate day-over-day change
daily_data = pd.DataFrame({
    'day': [1, 2, 3, 4],
    'revenue': [100, 150, 130, 200]
})
daily_data.to_sql('daily_rev', conn, index=False)

query_lag = """
SELECT day, revenue,
       LAG(revenue) OVER(ORDER BY day) as prev_rev,
       revenue - LAG(revenue) OVER(ORDER BY day) as diff
FROM daily_rev
"""
print(pd.read_sql(query_lag, conn))

What’s going on: On Day 2, LAG grabs the 100 from Day 1, and the diff column shows 50. On Day 1, prev_rev is None (or NULL) — there’s no yesterday to pull from. That’s how you tell whether a user’s spending is going up or down over time.

5. Running Aggregates: SUM, AVG, COUNT Over a Window

You can also control the window size with ROWS BETWEEN.

• UNBOUNDED PRECEDING: From the very start.
• CURRENT ROW: Stop at the current row.
• 7 PRECEDING: Look back exactly 7 rows — useful for a 7-day moving average.

query_moving_avg = """
SELECT day, revenue,
       SUM(revenue) OVER(ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total,
       AVG(revenue) OVER(ORDER BY day ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) as two_day_avg
FROM daily_rev
"""
print(pd.read_sql(query_moving_avg, conn))

Interpretation: The two_day_avg for Day 2 is 125 (average of 100 and 150). This smoothing cuts through daily noise so the underlying trend is easier to read.

6. FIRST_VALUE and LAST_VALUE: Anchoring to the Start or End

Sometimes you need to compare every row against a baseline. What was the user’s first purchase price?

query_first = """
SELECT user_id, amount,
       FIRST_VALUE(amount) OVER(PARTITION BY user_id ORDER BY amount ASC) as cheapest_ever
FROM sales
"""
print(pd.read_sql(query_first, conn))

Now every row for User 1 shows ‘10’ in the cheapest_ever column. That makes it easy to calculate things like “percentage increase since the first purchase.”

7. NTILE: Bucketing into Quantiles

NTILE(n) splits your data into n buckets. Want quartiles? Use NTILE(4).

# Segment users into two halves based on spending
query_ntile = """
SELECT user_id, SUM(amount) as total,
       NTILE(2) OVER(ORDER BY SUM(amount) DESC) as spend_tier
FROM sales
GROUP BY user_id
"""
print(pd.read_sql(query_ntile, conn))

Interpretation: User 1 and User 2 both spent 60 total, but NTILE(2) still has to split 2 rows into 2 equal-sized buckets. So one user lands in tier 1, the other in tier 2 — which one gets tier 1 depends on how the database breaks the tie, since ORDER BY SUM(amount) DESC alone doesn’t fully determine row order when values are equal. This is great for “High Value” vs “Low Value” segments. Just add a tiebreaker column to ORDER BY if you need reproducible bucket assignment.

8. Putting It Together: A Real Interview Question

Question: “For each daily revenue record, show the running total, the change from yesterday, and the rank of that day’s revenue compared to all other days.”

full_query = """
SELECT 
    day, 
    revenue,
    SUM(revenue) OVER(ORDER BY day) as running_total,
    revenue - LAG(revenue) OVER(ORDER BY day) as dod_change,
    RANK() OVER(ORDER BY revenue DESC) as rev_rank
FROM daily_rev
"""
print(pd.read_sql(full_query, conn))

In an interview, you’d frame it plainly: “I’m using SUM with an ORDER BY for the running total, LAG to grab the previous row for the daily change, and RANK to find our top-earning days.”

9. Common Pitfalls and How to Avoid Them

1. Forgetting ORDER BY: If you write SUM(amount) OVER(PARTITION BY user_id), you get the total for that user on every row, not a running total. You need ORDER BY to make it run.
2. Mixing with GROUP BY: You can’t use a window function on a column that isn’t in your GROUP BY unless you wrap it correctly. I’d lean toward aggregating first in a CTE, then applying window functions — that’s usually the safer approach.
3. NULLs in LAG: The first row will always have a NULL LAG. Use COALESCE(LAG(revenue), 0) to handle it.

10. Practice: Three Interview-Style Questions

1. Rank products by category: RANK() OVER(PARTITION BY category ORDER BY sales DESC)
2. Identify ‘Power Users’: NTILE(10) divides users into deciles — the top bucket marks the top 10% of spenders.
3. Find inactive gaps: LAG(purchase_date) grabs the previous purchase date, so you can measure the days between a user’s purchases.

11. Next Steps

You’ve moved from “SQL is for squashing data” to “SQL is for analyzing sequences.” Window functions are faster than self-joins, and the code reads cleaner.

• Recap: PARTITION to group, ORDER to sequence, LAG/LEAD to travel through time.
• Next Part: We’ll combine these with Common Table Expressions (CTEs) to build complex data pipelines.

Check Your Understanding

Questions below move from simple recall up to open-ended design, roughly following Bloom’s Taxonomy.

Remember What is the key difference between RANK() and DENSE_RANK() when there’s a tie?

Understand In your own words, explain why SUM(amount) OVER(PARTITION BY user_id) without an ORDER BY gives the same total on every row for that user, instead of a running total.

Apply Using the article’s ROWS BETWEEN syntax, what window frame definition would you write to calculate a 3-day moving average (today plus the two days before)?

Analyze The article’s Pitfall #3 warns that LAG produces NULL for the first row in each partition. Walk through what would happen to a dod_change calculation (revenue - LAG(revenue)) for that first row if you didn’t use COALESCE, and why silently getting NULL there is safer than silently getting a wrong number.

Evaluate The article’s NTILE example shows that a tie between two equal-total users gets arbitrarily broken by the database. Critique using NTILE for a “High Value” vs. “Low Value” customer segmentation without an explicit tiebreaker column: what business risk does this arbitrary tie-breaking create if segment assignment drives different treatment (like different discount offers)?

Create Design a window-function query (following the article’s Section 8 “real interview question” pattern) for a new scenario: for each customer, show their order amount, their rank among their own orders (not all customers’), and the number of days since their previous order. Sketch the SQL using PARTITION BY, RANK(), and LAG() together.

---

Apply What You Learned

**Brief**: You're shipping a feature extraction service for a user-churn model. The service reads from a `sales` table (`user_id`, `amount`, `day`) and must produce per-transaction ML features using SQL window functions — running totals, day-over-day deltas, in-user transaction ranks, and spend-tier buckets. Three pitfalls from the article will bite you in production if you don't handle them: (1) omitting `ORDER BY` from `SUM ... OVER(PARTITION BY ...)` gives a static total instead of a running total, (2) `LAG` returns `NULL` on the first row of each partition and silently corrupts `dod_change` unless you `COALESCE`, and (3) `NTILE` without a tiebreaker column assigns arbitrary buckets when totals tie — non-reproducible segments mean different discount offers for identical-value users.

Deliverable: Complete all five TODO functions in the stub file. Each function has a PASS CRITERION comment describing what correct output looks like. Run python window_feature_service.py to verify all five queries execute and produce the expected row counts and values.

Starter: projects/sql-window-functions-you-actually-need-for-data-science/window_feature_service.py

Rubric:

• compute_running_total uses SUM(amount) OVER(PARTITION BY user_id ORDER BY day) — the ORDER BY day is what makes it “run”; without it every row shows 60 for User 1 (article pitfall #1)
• compute_dod_change wraps LAG(amount) in COALESCE(..., 0) so the first row per user shows 0, not NULL (article pitfall #3)
• compute_user_rank uses RANK() not ROW_NUMBER() — User 3’s two amount=25 transactions must share rank 2, and amount=15 gets rank 4 (article Section 3: ties)
• compute_spend_tiers adds user_id as a tiebreaker in NTILE(3) OVER(ORDER BY total_spend DESC, user_id) so bucket assignment is reproducible when User 1 and User 2 both total 60 (article Section 7)
• build_feature_table combines all four window functions in one query returning 9 rows (one per transaction, no GROUP BY collapse) with columns: user_id, day, amount, running_total, dod_change, txn_rank, cheapest_ever