Window Functions

OVER Clause

The OVER clause defines the window over which a window function operates. It specifies how rows are partitioned and ordered for calculation. Unlike GROUP BY, window functions do not collapse rows.

-- Basic OVER clause: calculate running total
SELECT 
    order_id,
    customer_id,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

The OVER clause can contain two optional clauses:

• PARTITION BY: Divides rows into groups (like GROUP BY but without collapsing)
• ORDER BY: Orders rows within each partition

-- PARTITION BY: calculate total per customer without collapsing
SELECT 
    order_id,
    customer_id,
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

-- Both PARTITION BY and ORDER BY
SELECT 
    order_id,
    customer_id,
    order_date,
    total_amount,
    SUM(total_amount) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) AS running_customer_total
FROM orders;

The key distinction from GROUP BY: window functions add calculations to each row without reducing the number of rows returned. GROUP BY collapses rows into groups. Use window functions when you need both detail and aggregate values in the same query.

ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential integer to each row within a partition. Rows are numbered starting from 1, and no two rows receive the same number even if they have tied values.

-- Number orders per customer by date
SELECT 
    order_id,
    customer_id,
    order_date,
    total_amount,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) AS order_sequence
FROM orders;

ROW_NUMBER is essential for:

• Removing duplicates (keeping the first or last)
• Paginating results
• Assigning unique identifiers within groups

-- Find the most recent order per customer (remove duplicates)
SELECT *
FROM (
    SELECT 
        order_id,
        customer_id,
        order_date,
        total_amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY order_date DESC
        ) AS rn
    FROM orders
) ranked
WHERE rn = 1;

ROW_NUMBER is deterministic only when the ORDER BY uniquely identifies rows. If there are ties, the database may assign arbitrary numbers. To make it deterministic, add a unique column to the ORDER BY.

RANK() and DENSE_RANK()

Both RANK() and DENSE_RANK() handle ties, but they differ in how they assign numbers after ties.

RANK() leaves gaps after ties. If two rows tie for rank 2, the next row gets rank 4 (skipping 3).

DENSE_RANK() has no gaps. If two rows tie for rank 2, the next row gets rank 3.

-- Compare RANK, DENSE_RANK, and ROW_NUMBER
SELECT 
    product_name,
    category,
    price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS row_num,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank_val
FROM products;

Example output:

product_name	price	row_num	rank_val	dense_rank_val
Phone A	999	1	1	1
Phone B	999	2	1	1
Phone C	799	3	3	2

-- Find top 3 products per category by price
SELECT *
FROM (
    SELECT 
        product_name,
        category,
        price,
        DENSE_RANK() OVER (
            PARTITION BY category 
            ORDER BY price DESC
        ) AS price_rank
    FROM products
) ranked
WHERE price_rank <= 3;

Use RANK when you want gaps after ties (standard competition ranking). Use DENSE_RANK when you want consecutive ranks (no gaps). Use ROW_NUMBER when you need unique numbers regardless of ties.

LAG() and LEAD()

LAG() accesses a value from a previous row, and LEAD() accesses a value from a next row within the same partition. They are essential for comparing consecutive rows.

-- Compare each order to the previous order for the same customer
SELECT 
    order_id,
    customer_id,
    order_date,
    total_amount,
    LAG(total_amount, 1) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) AS prev_order_amount,
    total_amount - LAG(total_amount, 1) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) AS amount_change
FROM orders;

Both functions accept three parameters:

1. column: The column value to access
2. offset (optional, default 1): How many rows back/forward to look
3. default (optional): Value to return if no row exists at that position

-- LEAD: compare to the next order
SELECT 
    order_id,
    customer_id,
    order_date,
    LEAD(order_date, 1) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) AS next_order_date
FROM orders;

-- With default value for first/last rows
SELECT 
    product_name,
    price,
    LAG(price, 1, 0) OVER (ORDER BY price) AS prev_price,
    LEAD(price, 1, 0) OVER (ORDER BY price) AS next_price
FROM products;

LAG and LEAD are invaluable for time-series analysis, calculating differences between consecutive periods, and identifying gaps in data.

NTILE()

NTILE(n) divides the result set into approximately equal buckets (groups) and assigns a bucket number to each row. This is useful for creating percentiles, quartiles, or distributing data evenly.

-- Divide customers into 4 groups (quartiles) by total spending
SELECT 
    customer_id,
    total_spent,
    NTILE(4) OVER (ORDER BY total_spent DESC) AS spending_quartile
FROM (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
) customer_totals;

Quartile 1 contains the top 25% of customers, quartile 4 contains the bottom 25%.

-- Divide products into 3 price tiers
SELECT 
    product_name,
    price,
    NTILE(3) OVER (ORDER BY price DESC) AS price_tier
FROM products;

NTILE guarantees that:

• Each bucket gets either floor(n/rows) or ceiling(n/rows) rows
• Buckets differ in size by at most 1 row
• Bucket numbers start at 1

-- Split employees into 5 salary bands for analysis
SELECT 
    employee_name,
    salary,
    NTILE(5) OVER (ORDER BY salary DESC) AS salary_band
FROM employees;

NTILE is commonly used in reporting to segment data into equal groups for comparison. It is also used in A/B testing to evenly distribute users across test groups.

Common Window Function Patterns

Window functions enable several powerful analytical patterns that would be complex with traditional SQL.

Ranking top N per group:

-- Top 3 orders per customer
SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY customer_id ORDER BY total_amount DESC
    ) AS rn
    FROM orders
) ranked
WHERE rn <= 3;

Running totals and cumulative calculations:

-- Running total per customer
SELECT 
    order_id,
    customer_id,
    order_date,
    total_amount,
    SUM(total_amount) OVER (
        PARTITION BY customer_id ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

Period-over-period comparison:

-- Month-over-month revenue growth
SELECT 
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / 
          LAG(revenue) OVER (ORDER BY month) * 100, 2) AS growth_pct
FROM monthly_revenue;

Deduplication:

-- Remove duplicates, keeping the latest record
SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY customer_id ORDER BY updated_at DESC
    ) AS rn
    FROM customers
) deduped
WHERE rn = 1;

Moving averages:

-- 7-day moving average of daily sales
SELECT 
    sale_date,
    daily_sales,
    AVG(daily_sales) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales;

Window functions are one of the most powerful features in SQL. Mastering them enables complex analytics in a single query without self-joins or correlated subqueries.