Advanced SQL: Window Functions, CTEs & Analytical Queries

Window functions represent the pinnacle of analytical SQL. They allow data scientists to compute running totals, rolling averages, rankings, and lead/lag differences across partitions without messy self-joins or row collapsing.

Anatomy of a Window Function

• OVER(): Signals that the function operates as a window calculation.
• PARTITION BY: Divides the dataset into independent calculation buckets (similar to GROUP BY, but without collapsing rows).
• ORDER BY: Dictates the sequence of row evaluation inside each partition.
• FRAME: Specifies the subset of partition rows included (e.g., sliding window).

Hands-on Query: MoM Growth & Top 3 Products per Category

-- Query: Identify the Top 3 revenue-generating products per category and compute MoM growth
WITH MonthlyProductRevenue AS (
    SELECT 
        p.category_name,
        p.product_name,
        DATE_TRUNC('month', o.order_date) AS order_month,
        SUM(oi.quantity * oi.unit_price) AS monthly_sales
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    JOIN orders o ON oi.order_id = o.order_id
    GROUP BY p.category_name, p.product_name, DATE_TRUNC('month', o.order_date)
),
RankedProducts AS (
    SELECT 
        category_name,
        product_name,
        order_month,
        monthly_sales,
        -- Rank within category partition
        DENSE_RANK() OVER (
            PARTITION BY category_name, order_month 
            ORDER BY monthly_sales DESC
        ) AS sales_rank,
        -- Previous month's sales via LAG
        LAG(monthly_sales, 1) OVER (
            PARTITION BY category_name, product_name 
            ORDER BY order_month
        ) AS prev_month_sales
    FROM MonthlyProductRevenue
)
SELECT 
    category_name,
    product_name,
    order_month,
    monthly_sales,
    sales_rank,
    ROUND(
        (monthly_sales - prev_month_sales) / NULLIF(prev_month_sales, 0) * 100.0, 2
    ) AS mom_growth_pct
FROM RankedProducts
WHERE sales_rank <= 3
ORDER BY category_name, order_month, sales_rank;