Introduction: Are You Still Using Python Loops for Data Analysis?

Your operations team asks “Did sales increase or decrease today compared to yesterday?” — you write Python loops to compare day by day, the code is long and error-prone.

Your boss wants “the top 3 cities by sales in each region” — you write three nested subqueries and it still throws errors.

Your analyst needs “the time interval between user purchases” — you self-JOIN the table and get duplicate records everywhere.

The common solution to all these problems: Window Functions.

Today I’ll teach you how to use three core functions — LAG, LEAD, and ROW_NUMBER — to solve 80% of daily data analysis needs.

Core Principle: One Sentence to Understand Window Functions

Window Function = “On every row, you can see the data from surrounding rows”

Key syntax:

function_name(column) OVER (PARTITION BY group_column ORDER BY sort_column)

• PARTITION BY: Group by what (similar to GROUP BY, but doesn’t aggregate)
• ORDER BY: Sort within each group
• No GROUP BY needed — original rows are preserved

Scenario 1: Day-over-Day Analysis — Using LAG

Problem: An e-commerce backend needs to track daily sales trends by category, calculating today vs. yesterday’s change rate.

import duckdb

con = duckdb.connect(":memory:")

# Create sales data
con.execute("""
 CREATE TABLE sales AS SELECT * FROM (VALUES
 ('2024-09-01', 'Electronics', 150000),
 ('2024-09-02', 'Electronics', 162000),
 ('2024-09-03', 'Electronics', 158000),
 ('2024-09-01', 'Clothing', 89000),
 ('2024-09-02', 'Clothing', 95000),
 ('2024-09-03', 'Clothing', 102000)
 ) t(date, category, revenue)
""")

# Calculate day-over-day change
result = con.execute("""
 SELECT 
 date,
 category,
 revenue,
 LAG(revenue) OVER w AS prev_day_revenue,
 ROUND(
 (revenue - LAG(revenue) OVER w) * 100.0 / LAG(revenue) OVER w, 
 2
 ) AS mom_change_pct
 FROM sales
 WINDOW w AS (PARTITION BY category ORDER BY date)
 ORDER BY category, date
""").fetchdf()

print(result)

Output:

 date category revenue prev_day_revenue mom_change_pct
0 2024-09-01 Electronics 150000 NaN NaN
1 2024-09-02 Electronics 162000 150000.0 8.00
2 2024-09-03 Electronics 158000 162000.0 -2.47
3 2024-09-01 Clothing 89000 NaN NaN
4 2024-09-02 Clothing 95000 89000.0 6.74
5 2024-09-03 Clothing 102000 95000.0 7.37

💡 Key Tip: Use WINDOW w AS (...) to name the window, avoiding repetitive OVER clauses.

Scenario 2: TopN Query — Using ROW_NUMBER

Problem: Find the top 3 best-selling products in each city for promotional decisions.

# Create order data
con.execute("""
 CREATE TABLE orders AS SELECT * FROM (VALUES
 ('Beijing', 'iPhone 15', 12000, '2024-09-01'),
 ('Beijing', 'MacBook Pro', 14999, '2024-09-01'),
 ('Beijing', 'AirPods', 1899, '2024-09-02'),
 ('Beijing', 'iPad Air', 4799, '2024-09-02'),
 ('Beijing', 'Apple Watch', 2999, '2024-09-03'),
 ('Shanghai', 'iPhone 15', 11500, '2024-09-01'),
 ('Shanghai', 'MacBook Pro', 14500, '2024-09-02'),
 ('Shanghai', 'AirPods', 1899, '2024-09-03'),
 ('Guangzhou', 'iPhone 15', 11800, '2024-09-01'),
 ('Guangzhou', 'MacBook Pro', 14800, '2024-09-02'),
 ('Guangzhou', 'AirPods', 1799, '2024-09-03')
 ) t(city, product, amount, date)
""")

# TopN query: top 3 products by sales in each city
result = con.execute("""
 WITH ranked AS (
 SELECT 
 city,
 product,
 amount,
 date,
 ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS rn
 FROM orders
 )
 SELECT city, product, amount, date
 FROM ranked
 WHERE rn <= 3
 ORDER BY city, amount DESC
""").fetchdf()

print(result)

💡 Advanced Tips:

• ROW_NUMBER(): Strict ranking, no ties
• RANK(): Ties skip subsequent ranks (1,1,3)
• DENSE_RANK(): Ties don’t skip (1,1,2)

Scenario 3: User Behavior Analysis — Using LEAD to Calculate Intervals

Problem: Calculate the days between consecutive purchases for each user to identify churn risks.

# User purchase records
con.execute("""
 CREATE TABLE purchases AS SELECT * FROM (VALUES
 (1001, '2024-08-01'),
 (1001, '2024-08-05'),
 (1001, '2024-08-20'),
 (1001, '2024-09-01'),
 (1002, '2024-08-01'),
 (1002, '2024-08-10'),
 (1003, '2024-08-01'),
 (1003, '2024-08-03')
 ) t(user_id, purchase_date)
""")

# Calculate purchase intervals
result = con.execute("""
 SELECT 
 user_id,
 purchase_date,
 LEAD(purchase_date) OVER w AS next_purchase,
 DATEDIFF('day', purchase_date, LEAD(purchase_date) OVER w) AS days_gap
 FROM purchases
 WINDOW w AS (PARTITION BY user_id ORDER BY purchase_date)
 ORDER BY user_id, purchase_date
""").fetchdf()

print(result)

Output:

 user_id purchase_date next_purchase days_gap
0 1001 2024-08-01 2024-08-05 4
1 1001 2024-08-05 2024-08-20 15
2 1001 2024-08-20 2024-09-01 12
3 1001 2024-09-01 <NA> NULL
4 1002 2024-08-01 2024-08-10 9
5 1002 2024-08-10 <NA> 22
6 1003 2024-08-01 2024-08-03 2
7 1003 2024-08-03 <NA> NULL

Business Application: Users with gap > 30 days are flagged as “churn risk” and trigger retention campaigns.

Scenario 4: Moving Average — Smoothing Volatility to See Trends

Problem: Daily sales fluctuate greatly; you want a 7-day moving average to see the real trend.

result = con.execute("""
 SELECT 
 date,
 revenue,
 ROUND(AVG(revenue) OVER (
 ORDER BY date 
 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
 ), 2) AS ma_7d
 FROM daily_sales
 ORDER BY date
""").fetchdf()

Key Syntax: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW specifies the window frame — 6 rows before current row, totaling 7 rows for averaging.

Quick Reference: Window Functions Comparison

Three Practical Tips

Tip 1: WINDOW Clause Reuse

-- Wrong: Repeat OVER clause
SELECT ..., LAG(x) OVER (PARTITION BY a ORDER BY b),
 LEAD(x) OVER (PARTITION BY a ORDER BY b)
FROM t;

-- Correct: Use WINDOW to name and reuse
SELECT ..., LAG(x) OVER w, LEAD(x) OVER w
FROM t
WINDOW w AS (PARTITION BY a ORDER BY b);

Tip 2: Handle Boundary NULLs

LAG/LEAD returns NULL at boundaries. Use COALESCE for defaults:

COALESCE(LAG(revenue) OVER w, revenue) AS prev_or_current

Tip 3: Window Functions vs Self-Join

-- Self-join (slow, error-prone)
SELECT a.*, b.revenue AS prev_revenue
FROM sales a
LEFT JOIN sales b ON a.category = b.category AND a.date = b.date + INTERVAL '1 day';

-- Window function (fast, concise)
SELECT *, LAG(revenue) OVER (PARTITION BY category ORDER BY date) AS prev_revenue
FROM sales;

Window functions are typically 5-10x faster than self-joins because they only scan the data once.

DuckDB vs pandas Performance Comparison

Test scenario: 1 million rows of sales data, 8-core 16GB RAM

Unique Advantages of DuckDB Window Functions

1. Zero Configuration, Works Out of the Box

import duckdb
con = duckdb.connect(":memory:")
# No extra packages needed, execute window function queries directly

2. Lazy Execution, Extremely Memory Efficient

DuckDB uses columnar storage and lazy execution. Window function calculations are completed in a pipeline during data reading, without loading all data into memory.

3. Seamless Integration with SQL Ecosystem

Window functions work directly in SQL, perfectly compatible with CTEs, subqueries, and aggregate functions:

WITH ranked_sales AS (
 SELECT 
 category,
 product,
 revenue,
 ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn,
 LAG(revenue) OVER w AS prev_revenue
 FROM sales
 WINDOW w AS (PARTITION BY category ORDER BY revenue DESC)
)
SELECT category, product, revenue
FROM ranked_sales
WHERE rn <= 3;

When to Use Window Functions: Best Practices Guide

✅ Suitable Scenarios

1. Time series analysis: Day-over-day, week-over-week, year-over-year
2. Ranking needs: TopN, grouped ranking, percentile ranking
3. Interval calculation: User behavior intervals, event time differences
4. Moving statistics: Moving average, rolling sum
5. Fill missing values: Forward fill or backward fill

❌ Unsuitable Scenarios

1. Ultra-large datasets (> 100M rows) → Consider partitioning first
2. Need to modify data → Window functions are read-only
3. Cross-table join calculations → Use JOIN first, then apply window functions

Automated daily report script

import duckdb from datetime import datetime

con = duckdb.connect(“sales.db”)

Execute daily automatically

today = datetime.now().strftime(’%Y-%m-%d’) report = con.execute(f""" SELECT category, SUM(revenue) AS today_revenue, LAG(SUM(revenue)) OVER (ORDER BY date) AS yesterday_revenue, ROUND((SUM(revenue) - LAG(SUM(revenue)) OVER (ORDER BY date)) * 100.0 / LAG(SUM(revenue)) OVER (ORDER BY date), 2) AS change_pct FROM daily_sales WHERE date >= DATE_SUB(‘day’, 7, ‘{today}’) GROUP BY category, date ORDER BY date, category “”").fetchdf()

Generate HTML report and send

report.to_html(‘daily_report.html’)

**Expected Revenue**: 50 clients × = 

### Product 2: User Churn Prediction SaaS

**Target Customers**: Internet products, e-commerce platforms

**Services**:
- Automatically identify churn-risk users (purchase gap > 30 days)
- Generate user segmentation reports (high-value, medium, risk)
- Provide retention strategy recommendations

**Pricing**: (per user tier)

**Tech Stack**: DuckDB + LEAD function + FastAPI

```python
# Core SQL for churn identification
con.execute("""
 SELECT 
 user_id,
 purchase_date,
 LEAD(purchase_date) OVER (PARTITION BY user_id ORDER BY purchase_date) AS next_purchase,
 DATEDIFF('day', purchase_date, 
 LEAD(purchase_date) OVER (PARTITION BY user_id ORDER BY purchase_date)) AS gap_days
 FROM purchases
""")

Expected Revenue: 20 clients × =

Product 3: Data Analyst Training Course

Target Customers: Analysts wanting to improve SQL skills, career changers

Content:

• Window functions from basics to advanced
• 100+ real business scenario cases
• DuckDB practical projects

Pricing: /person (one-time purchase)

Expected Revenue: 100 students/month × $299 =

Summary

Window functions are the soul of SQL analysis. Those who master them write analysis queries like prose; those who don’t solve equations.

Key Takeaways:

1. LAG() gets previous values for MoM calculations
2. LEAD() gets next values for interval calculations
3. ROW_NUMBER() generates rankings for TopN problems
4. Use WINDOW clause for naming and reuse
5. DuckDB window functions are 5-10x faster than pandas

Tonight’s Action Items:

1. Create a test table with 30 days of sales data in DuckDB
2. Use LAG to calculate day-over-day changes, ROW_NUMBER to find daily Top 3 products
3. Use LEAD to calculate user purchase intervals, flag “churn risk” users with gaps > 30 days
4. Compare: How would you write the same requirements in Python pandas vs. window functions? Which is more concise?

Remember: Window functions are the soul of SQL analysis. Those who master them write analysis queries like prose; those who don’t solve equations.

📌 Bookmark this for your next data analysis project. 🔍 duckdblab.org for systematic DuckDB learning.

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 contact@duckdblab.org 反馈。