Difficulty: Advanced
Window functions are one of the most powerful features in SQL for analytics, and they fundamentally change how you can analyze data. Unlike regular aggregation functions that collapse rows into groups, window functions perform calculations across a set of rows (the 'window') while preserving every individual row in the output. This makes them perfect for tasks like ranking, running totals, moving averages, and comparing each row to a group aggregate.
The OVER clause defines the window for the function. PARTITION BY divides rows into groups (similar to GROUP BY but without collapsing), and ORDER BY determines the sequence within each partition. For example, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) assigns a rank within each department based on salary, with every row retaining its original detail.
The ranking functions ROW_NUMBER, RANK, and DENSE_RANK differ in how they handle ties. ROW_NUMBER always assigns unique sequential numbers regardless of ties. RANK assigns the same number to ties but leaves gaps (1, 2, 2, 4). DENSE_RANK assigns the same number to ties without gaps (1, 2, 2, 3). Choosing the right function depends on your business requirements.
LAG and LEAD are offset functions that access values from previous or subsequent rows respectively. LAG(column, n) looks back n rows, while LEAD(column, n) looks ahead n rows. These are invaluable for time-series analysis: calculating day-over-day changes, identifying trends, and comparing current values to historical ones. Combined with PARTITION BY, they enable sophisticated per-group temporal analysis.
# Simulating window ranking functions
scores = [
{'name': 'Alice', 'score': 95},
{'name': 'Bob', 'score': 88},
{'name': 'Charlie', 'score': 95},
{'name': 'Diana', 'score': 82},
{'name': 'Eve', 'score': 88},
{'name': 'Frank', 'score': 78},
]
# Sort by score descending
sorted_scores = sorted(scores, key=lambda x: x['score'], reverse=True)
print(f"{'Name':<10} {'Score':>6} {'ROW_NUM':>8} {'RANK':>6} {'D_RANK':>7}")
print("-" * 39)
row_num = 0
prev_score = None
rank_val = 0
dense_val = 0
for i, s in enumerate(sorted_scores):
row_num = i + 1
if s['score'] != prev_score:
rank_val = i + 1
dense_val += 1
prev_score = s['score']
print(f"{s['name']:<10} {s['score']:>6} {row_num:>8} {rank_val:>6} {dense_val:>7}")ROW_NUMBER always increments (1,2,3,4,5,6). RANK gives ties the same value but skips numbers (1,1,3,3,5,6 -- no 2 or 4). DENSE_RANK gives ties the same value without gaps (1,1,2,2,3,4). Alice and Charlie tie at 95, getting the same RANK (1) and DENSE_RANK (1) but different ROW_NUMBERs.
# SQL: ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC)
employees = [
{'name': 'Alice', 'dept': 'Engineering', 'salary': 105000},
{'name': 'Bob', 'dept': 'Engineering', 'salary': 95000},
{'name': 'Charlie', 'dept': 'Engineering', 'salary': 88000},
{'name': 'Diana', 'dept': 'Marketing', 'salary': 82000},
{'name': 'Eve', 'dept': 'Marketing', 'salary': 78000},
{'name': 'Frank', 'dept': 'HR', 'salary': 72000},
]
# Group by department
depts = {}
for e in employees:
depts.setdefault(e['dept'], []).append(e)
print("Ranked within each department:")
print(f"{'Name':<10} {'Dept':<15} {'Salary':>8} {'Rank':>5}")
print("-" * 40)
for dept in sorted(depts):
dept_emps = sorted(depts[dept], key=lambda x: x['salary'], reverse=True)
for rank, e in enumerate(dept_emps, 1):
print(f"{e['name']:<10} {e['dept']:<15} ${e['salary']:>7,} {rank:>5}")PARTITION BY restarts the ranking within each group. Alice is #1 in Engineering, Diana is #1 in Marketing, and Frank is #1 in HR. This is the standard pattern for finding the top-N within each category, such as 'top 3 products per region' or 'highest scorer per class'.
# Simulating LAG and LEAD for month-over-month analysis
monthly_revenue = [
{'month': 'Jan', 'revenue': 10000},
{'month': 'Feb', 'revenue': 12000},
{'month': 'Mar', 'revenue': 11500},
{'month': 'Apr', 'revenue': 15000},
{'month': 'May', 'revenue': 14000},
{'month': 'Jun', 'revenue': 18000},
]
print(f"{'Month':<6} {'Revenue':>8} {'Prev':>8} {'Next':>8} {'MoM Change':>11}")
print("-" * 45)
for i, row in enumerate(monthly_revenue):
prev_rev = monthly_revenue[i-1]['revenue'] if i > 0 else None
next_rev = monthly_revenue[i+1]['revenue'] if i < len(monthly_revenue)-1 else None
if prev_rev is not None:
change = (row['revenue'] - prev_rev) / prev_rev * 100
change_str = f"{change:>+.1f}%"
else:
change_str = "N/A"
prev_str = f"${prev_rev:,}" if prev_rev else "NULL"
next_str = f"${next_rev:,}" if next_rev else "NULL"
print(f"{row['month']:<6} ${row['revenue']:>7,} {prev_str:>8} {next_str:>8} {change_str:>11}")LAG gives the previous row's value (Prev column), LEAD gives the next row's value (Next column). The first row has no LAG (NULL) and the last has no LEAD (NULL). The MoM (month-over-month) change uses LAG to compute percentage growth, which is one of the most common analytics calculations.
# SQL: SUM(revenue) OVER (ORDER BY month) as running_total
daily_sales = [
{'day': 'Mon', 'sales': 120},
{'day': 'Tue', 'sales': 95},
{'day': 'Wed', 'sales': 180},
{'day': 'Thu', 'sales': 60},
{'day': 'Fri', 'sales': 210},
]
print(f"{'Day':<5} {'Sales':>6} {'Running Total':>14} {'% of Week':>10}")
print("-" * 37)
total_week = sum(d['sales'] for d in daily_sales)
running_total = 0
for d in daily_sales:
running_total += d['sales']
pct = running_total / total_week * 100
print(f"{d['day']:<5} ${d['sales']:>5} ${running_total:>13} {pct:>9.1f}%")
print(f"\nWeek Total: ${total_week}")The running total accumulates sales as we move through the days. In SQL, SUM(sales) OVER (ORDER BY day_order) creates this cumulative sum. The percentage column shows what fraction of the weekly total has been achieved by each day -- useful for tracking progress toward targets.
ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, OVER, PARTITION BY