Why this matters
As a BI Developer, you will rank products, detect top customers, and show trends like week-over-week growth. Window functions let you do this in one query without collapsing data. You can compute rolling averages, cumulative totals, and compare each row to prior periods to build reliable dashboards and KPIs.
- Leaderboards: top 10 products by revenue per month
- Trends: moving averages to smooth noisy daily data
- Comparisons: day-over-day or month-over-month change
Who this is for
BI Developers and analysts who need accurate rankings, trendlines, and comparisons directly in SQL for dashboards and reports.
Prerequisites
- Comfort with SELECT, WHERE, GROUP BY
- Understanding of ORDER BY and basic aggregates (SUM, COUNT, AVG)
- Familiarity with date fields and sorting
Learning path
- Understand the OVER clause: PARTITION BY and ORDER BY
- Ranking functions: ROW_NUMBER, RANK, DENSE_RANK
- Row comparisons: LAG, LEAD
- Windowed aggregates: moving averages, cumulative sums
- Frame clauses: ROWS vs RANGE, PRECEDING and FOLLOWING
Concept explained simply
A window function looks at the current row and a configurable "window" of related rows, without grouping them into a single row. You keep all rows and add new columns with running or comparative metrics.
Mental model
Imagine each row wearing glasses that can see certain neighboring rows, defined by a window:
- PARTITION BY = which crowd the row stands in (per product, per customer)
- ORDER BY = how the crowd lines up (by date, by revenue)
- FRAME = how wide the glasses see (last 6 days, whole partition, peers)
Key syntax cheatsheet
-- Ranking
ROW_NUMBER() OVER (PARTITION BY key ORDER BY metric DESC)
RANK() OVER (PARTITION BY key ORDER BY metric DESC)
DENSE_RANK() OVER (PARTITION BY key ORDER BY metric DESC)
-- Row-to-row comparison
LAG(value, 1) OVER (PARTITION BY key ORDER BY date)
LEAD(value, 1) OVER (PARTITION BY key ORDER BY date)
-- Windowed aggregates (trends)
SUM(value) OVER (
PARTITION BY key
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS last_7_day_sum
-- Cumulative total
SUM(value) OVER (PARTITION BY key ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Tip: ROWS vs RANGE
- ROWS counts physical rows (common for moving averages).
- RANGE groups rows with the same ORDER BY value as peers (can include ties).
- Default for ordered window aggregates often acts like RANGE UNBOUNDED PRECEDING TO CURRENT ROW; be explicit to avoid surprises.
Worked examples
Example 1: Top products per month (ties)
-- Goal: Rank products by revenue within each month, keep ties.
SELECT
DATE_TRUNC('month', order_date) AS month,
product_id,
SUM(revenue) AS month_rev,
RANK() OVER (
PARTITION BY DATE_TRUNC('month', order_date)
ORDER BY SUM(revenue) DESC
) AS rnk
FROM sales
GROUP BY 1, product_id;
Use RANK when ties are okay (1,1,3). Use DENSE_RANK to avoid gaps (1,1,2).
Example 2: Day-over-day change and trend label
SELECT
product_id,
sale_date,
revenue,
LAG(revenue) OVER (
PARTITION BY product_id ORDER BY sale_date
) AS prev_rev,
ROUND( (revenue - LAG(revenue) OVER (PARTITION BY product_id ORDER BY sale_date))
/ NULLIF(LAG(revenue) OVER (PARTITION BY product_id ORDER BY sale_date), 0)::numeric
, 4) AS pct_change,
CASE
WHEN LAG(revenue) OVER (PARTITION BY product_id ORDER BY sale_date) IS NULL THEN 'N/A'
WHEN revenue > LAG(revenue) OVER (PARTITION BY product_id ORDER BY sale_date) THEN 'Up'
WHEN revenue < LAG(revenue) OVER (PARTITION BY product_id ORDER BY sale_date) THEN 'Down'
ELSE 'Flat'
END AS trend
FROM sales_daily;
Null-safe division avoids errors when the previous value is zero or null.
Example 3: 7-day moving average
SELECT
product_id,
sale_date,
revenue,
AVG(revenue) OVER (
PARTITION BY product_id
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS ma7
FROM sales_daily;
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW includes up to 7 rows: the current day plus the prior 6 days.
Also useful: cumulative totals
SUM(revenue) OVER (
PARTITION BY region ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cum_rev
Common mistakes and self-check
- Missing ORDER BY in the window: rankings or LAG/LEAD become nondeterministic. Self-check: Is your order rule explicit and correct?
- Using RANK when you need dense ranks: gaps (1,1,3) may break top-N filters. Self-check: Do you want gaps? If not, use DENSE_RANK.
- Wrong frame for moving averages: using RANGE may include all peer rows, not a fixed count. Self-check: For fixed-size windows use ROWS.
- Filtering window results in WHERE: WHERE cannot reference window columns. Self-check: Use a subquery/CTE (or QUALIFY where supported).
- Partitioning incorrectly: forgetting PARTITION BY mixes unrelated groups. Self-check: Are you ranking within the right dimension (product, region, customer)?
Exercises you can run
Mirror of the exercises below. Use any SQL dialect that supports window functions (e.g., PostgreSQL, SQL Server, BigQuery, Snowflake). Adjust date functions/casts if needed.
Exercise 1 (ex1): Rank days by revenue per product
Table: sales_daily(sale_date DATE, product_id INT, product_name TEXT, revenue NUMERIC)
- For each product, assign ROW_NUMBER by most recent sale_date (newest first).
- Also compute RANK and DENSE_RANK by revenue (highest first) within each product.
- Return sale_date, product_name, revenue, rn_recent_day, rnk_rev, drnk_rev.
Exercise 2 (ex2): Day-over-day change and 7-day average
Using the same table:
- Compute previous day revenue with LAG and the difference and percent change.
- Add a trend label: Up, Down, or Flat.
- Compute a 7-day moving average per product using ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.
Exercise checklist
- [ ] Every window column uses an explicit PARTITION BY when needed
- [ ] ORDER BY aligns with the business question (date for time, metric for ranks)
- [ ] Moving averages use ROWS, not RANGE
- [ ] No window columns referenced directly in WHERE (use subquery/CTE if filtering)
- [ ] Null and zero cases handled safely in percent calculations
Practical projects
- Monthly leaderboard: Top 10 customers by revenue per month, with ties handled correctly.
- Trend dashboard: 7, 14, and 28-day moving averages and cumulative revenue per product.
- Churn/retention tracker: LAG to detect last activity date per customer and flag returning vs. churned.
Mini challenge
Create a report with columns: product_id, week_start, week_rev, week_rank_within_region, wow_change_pct, ma4_weeks. Rank weekly revenue within region, compute week-over-week change, and a 4-week moving average. Use clear PARTITION BY choices and explicit ROWS frames.
Hints
- Aggregate to week first in a CTE
- Rank by week_rev within region
- LAG(week_rev) for WoW
- ROWS BETWEEN 3 PRECEDING AND CURRENT ROW for MA4
Next steps
- Practice filtering top-N per group using a subquery around your windowed ranks
- Add more trend metrics: rolling sums, rolling distinct counts (approximate where supported)
- Document and template common patterns for your team
Quick test
The quick test below is available to everyone. Only logged-in users get saved progress.