Window Functions Over Partition By Order By

Why this matters

Window functions let you compute running totals, rankings, moving averages, and period-over-period changes without collapsing rows. As a Data Analyst, this is how you build metrics like “sales by week with cumulative sum,” “top 3 items per store,” and “month-over-month growth” in one query.

• Prioritize customers with the highest recent spend using ROW_NUMBER over orders per customer.
• Report running totals for campaigns using SUM OVER with PARTITION BY campaign and ORDER BY date.
• Compare each day to the previous using LAG to get day-over-day changes.

Who this is for

• Analysts who can already write SELECTs with JOINs and GROUP BY and want to produce richer time-based metrics.
• Career switchers preparing for analytics SQL interviews, where these queries are common.

Prerequisites

• Comfort with SELECT, WHERE, GROUP BY, HAVING, ORDER BY.
• Basic understanding of aggregate functions (SUM, COUNT, AVG, MIN, MAX).
• Familiarity with JOINs and date columns.

Concept explained simply

A window function computes a value for each row by looking at that row and its neighbors. The OVER clause defines the "window" of rows available to the function.

• PARTITION BY: splits rows into groups (windows). The function restarts for each partition.
• ORDER BY: defines the order within each partition (needed for running totals, rankings, LAG/LEAD, moving averages).
• Frame (optional but important): with ORDER BY, you can specify ROWS/RANGE BETWEEN ... to control how many rows are considered. For running totals, use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

-- Ranking within a partition
ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY sales DESC)

-- Running total
SUM(amount) OVER (
  PARTITION BY customer_id
  ORDER BY order_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

-- Previous row and next row values
LAG(daily_sales, 1) OVER (PARTITION BY product_id ORDER BY sale_date)
LEAD(daily_sales, 1) OVER (PARTITION BY product_id ORDER BY sale_date)

-- Moving average (3-row window)
AVG(metric) OVER (
  PARTITION BY product_id
  ORDER BY event_time
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

Mental model

Imagine your data sorted inside numbered trays for each group (partition). ORDER BY aligns the rows chronologically or by value. The window frame is a ruler you slide across those trays to decide which rows are “in view” for each calculation. The function reads only what the frame shows, for every row.

Worked examples

1) Rank top products per category

-- Table: product_sales(category, product, sales)
SELECT
  category,
  product,
  sales,
  ROW_NUMBER() OVER (
    PARTITION BY category
    ORDER BY sales DESC
  ) AS category_rank
FROM product_sales;
-- Filter top 3 per category in an outer query if needed

Why it works: ROW_NUMBER restarts at 1 for each category and orders by sales.

2) Running total per customer

-- Table: orders(order_id, customer_id, order_date, amount)
SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_amount
FROM orders;

Why it works: The frame includes all prior rows up to the current row per customer, producing a cumulative sum.

3) 3-row moving average

-- Table: metrics(product_id, event_time, metric)
SELECT
  product_id,
  event_time,
  metric,
  AVG(metric) OVER (
    PARTITION BY product_id
    ORDER BY event_time
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS ma_3
FROM metrics;

Why it works: For each row, the frame looks at the current row and the prior 2 rows of that product.

4) Day-over-day change

-- Table: daily_sales(store_id, sale_date, amount)
SELECT
  store_id,
  sale_date,
  amount,
  LAG(amount) OVER (
    PARTITION BY store_id
    ORDER BY sale_date
  ) AS prev_amount,
  amount - LAG(amount) OVER (
    PARTITION BY store_id
    ORDER BY sale_date
  ) AS delta,
  CASE WHEN LAG(amount) OVER (
    PARTITION BY store_id
    ORDER BY sale_date
  ) > 0 THEN (amount - LAG(amount) OVER (
    PARTITION BY store_id
    ORDER BY sale_date
  )) * 1.0 / LAG(amount) OVER (
    PARTITION BY store_id
    ORDER BY sale_date
  ) END AS pct_change
FROM daily_sales;

Why it works: LAG exposes the previous row's value within each store's ordered series.

Common mistakes and self-check

• Forgetting ORDER BY when expecting a running total. Self-check: If rows are not ordered, cumulative numbers will look random.
• Confusing GROUP BY with PARTITION BY. Self-check: Window functions keep all rows; GROUP BY collapses rows.
• Using RANGE instead of ROWS for numeric or dated data. Self-check: For exact row counts (e.g., last 7 rows), use ROWS BETWEEN. RANGE groups peers with equal ORDER BY values; results may jump.
• Filtering by a window-function alias in WHERE. Self-check: Use a subquery/CTE and filter in the outer query (WHERE cannot see window aliases).
• Ranking ties incorrectly. Self-check: Use RANK/DENSE_RANK for ties; ROW_NUMBER forces unique sequence.

Exercises

Practice on small, focused tasks. Solutions are hidden—try first, then open.

What you'll do

• ex1: Rank each customer's orders by date.
• ex2: Compute a running total per product.
• ex3: Compare each day's sales to the previous day per store.

Exercise 1 — Rank orders per customer

Table: orders(order_id, customer_id, order_date, amount)

Task: Add a column order_rank that numbers each customer's orders from oldest to newest.

SELECT
  order_id,
  customer_id,
  order_date,
  amount,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS order_rank
FROM orders;

Exercise 2 — Running total per product

Table: sales(product_id, sale_date, qty)

Task: Output product_id, sale_date, qty, and running_qty which is the cumulative qty per product ordered by sale_date.

SELECT
  product_id,
  sale_date,
  qty,
  SUM(qty) OVER (
    PARTITION BY product_id
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_qty
FROM sales;

Exercise 3 — Previous-day comparison

Table: daily_sales(store_id, sale_date, amount)

Task: Add prev_amount and delta columns to compare each day to the previous day within each store.

SELECT
  store_id,
  sale_date,
  amount,
  LAG(amount, 1) OVER (
    PARTITION BY store_id
    ORDER BY sale_date
  ) AS prev_amount,
  amount - LAG(amount, 1) OVER (
    PARTITION BY store_id
    ORDER BY sale_date
  ) AS delta
FROM daily_sales;

Readiness checklist

• I can explain the difference between GROUP BY and PARTITION BY.
• I know why ORDER BY is necessary for running totals and LAG/LEAD.
• I can choose ROWS vs RANGE frames appropriately.
• I can compute ranks, running totals, moving averages, and period-over-period changes.

Practical projects

• Top-N per group report: For each store, list the top 3 products by last 30 days sales using ROW_NUMBER and a filter on rank.
• Customer lifecycle dashboard: Running total of orders per customer and days since previous order (LAG) to identify churn risk.
• Rolling KPI: 7-day moving average of daily active users per product with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.

Learning path

• Before this: SELECT basics → Aggregations → JOINs → GROUP BY vs window functions.
• Now: OVER (PARTITION BY ... ORDER BY ...) with ROWS frames.
• Next: Advanced frames (UNBOUNDED FOLLOWING, BETWEEN x PRECEDING AND y FOLLOWING), percentiles (PERCENT_RANK, NTILE), performance considerations.

Mini challenge

Table: orders(order_id, customer_id, order_date, amount)

• Task A: Output each customer's running spend and flag rows where running spend first exceeds 500.
• Task B: Compute month-over-month amount change per customer using LAG on a monthly aggregated subquery.

Next steps

• Practice writing queries that combine JOINs with multiple window columns in the same SELECT.
• Experiment with ROWS vs RANGE frames and observe differences with tied ORDER BY values.
• Add filters in an outer query to select top-k rows per partition.

Quick Test

Short quiz to check understanding. Note: The test is available to everyone; only logged-in users get saved progress.