Window Functions Deep Dive

Master advanced SQL analytics with window functions for sophisticated data analysis.

Window functions perform calculations across sets of rows related to the current row, enabling powerful analytics capabilities without the complexity of self-joins or subqueries. Understanding window functions is crucial for advanced data analysis and reporting.

Basic Syntax

Window functions follow a specific syntax pattern that includes partitioning and ordering:

-- Basic window function syntax
SELECT 
    product_name,
    category_id,
    price,
    AVG(price) OVER (
        PARTITION BY category_id
        ORDER BY price
        ROWS BETWEEN UNBOUNDED PRECEDING 
            AND CURRENT ROW
    ) as running_avg
FROM products;

Key Components

•PARTITION BY: Groups rows into windows
•ORDER BY: Defines row ordering within windows
•ROWS/RANGE: Specifies window frame boundaries

ROW_NUMBER Function

Assign unique numbers to rows within partitions:

-- ROW_NUMBER example
SELECT 
    order_id,
    customer_id,
    order_date,
    order_total,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id 
        ORDER BY order_date DESC
    ) as order_rank
FROM orders;

-- Find most recent order per customer
WITH RankedOrders AS (
    SELECT 
        order_id,
        customer_id,
        order_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY order_date DESC
        ) as order_rank
    FROM orders
)
SELECT * FROM RankedOrders 
WHERE order_rank = 1;

RANK vs DENSE_RANK

Compare different ranking functions:

-- Compare RANK vs DENSE_RANK
SELECT 
    product_name,
    category_id,
    price,
    RANK() OVER (
        PARTITION BY category_id 
        ORDER BY price DESC
    ) as price_rank,
    DENSE_RANK() OVER (
        PARTITION BY category_id 
        ORDER BY price DESC
    ) as dense_price_rank
FROM products;

RANK()

•Leaves gaps in sequence
•Same values get same rank
•Shows relative positions

DENSE_RANK()

•No gaps in sequence
•Consecutive ranking
•Better for dense lists

LEAD and LAG Functions

Access previous and next row values:

-- LEAD and LAG functions
SELECT 
    order_date,
    order_total,
    LAG(order_total) OVER (
        ORDER BY order_date
    ) as previous_order_total,
    LEAD(order_total) OVER (
        ORDER BY order_date
    ) as next_order_total,
    order_total - LAG(order_total) OVER (
        ORDER BY order_date
    ) as order_difference
FROM orders;

Running Totals and Moving Averages

Calculate cumulative values and moving averages:

-- Calculate running totals
SELECT 
    order_date,
    order_total,
    SUM(order_total) OVER (
        ORDER BY order_date
        ROWS UNBOUNDED PRECEDING
    ) as running_total,
    AVG(order_total) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_average_7day
FROM orders;

Advanced Window Functions

Use NTILE and percentile functions for advanced analysis:

-- NTILE for segmentation
SELECT 
    product_name,
    price,
    NTILE(4) OVER (
        ORDER BY price
    ) as price_quartile
FROM products
WHERE category_id = 1;

-- Percentile calculations
SELECT 
    product_name,
    price,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY price
    ) OVER (
        PARTITION BY category_id
    ) as median_price
FROM products;

Practice Examples

Try these examples to practice window functions:

Customer Purchase Analysis

SELECT customer_id, order_date, order_total, SUM(order_total) OVER ( PARTITION BY customer_id ORDER BY order_date ) as customer_running_total FROM orders;

Product Price Ranking

SELECT category_name, product_name, price, DENSE_RANK() OVER ( PARTITION BY category_name ORDER BY price DESC ) as price_rank FROM products JOIN categories ON products.category_id = categories.id;

Monthly Sales Comparison

SELECT DATE_TRUNC('month', order_date) as month, SUM(order_total) as monthly_total, LAG(SUM(order_total)) OVER ( ORDER BY DATE_TRUNC('month', order_date) ) as prev_month_total FROM orders GROUP BY DATE_TRUNC('month', order_date);

Next Steps

Continue your SQL mastery with these related topics:

Aggregate Functions →

Master basic data aggregation techniques

Working with JOINs →

Combine window functions with complex joins