Top Products per Category & YoY Growth Analysis

1. Why Do We Need Advanced Window Functions in SQL?

Standard aggregate functions like GROUP BY compress rows together, returning a single summary row per group (e.g., total sales per category). However, business intelligence and analytical reporting frequently require row-level retention alongside aggregate metrics.

When analysts need to compute running totals, moving averages, or comparative performance metrics across time series without collapsing the underlying granularity, they turn to SQL Window Functions.

• The Limitation of GROUP BY: Collapses rows, making it impossible to see individual monthly transactions alongside cumulative figures.
• The Power of Window Functions: Perform calculations across a set of table rows that are somehow related to the current row, preserving the individual row identities in the output.
• Key Analytical Use Cases:
  • Running totals (cumulative sales over time).
  • Categorical partitioning (cumulative sales calculated independently within each product category).
  • Sliding or rolling windows (e.g., rolling 3-month sales figures).

2. Core Mental Model: The Running Total & The Ledger Analogy

To understand cumulative sums, imagine keeping a physical financial ledger of daily sales.

Real-Life Analogy: The Daily Financial Ledger

Think of a cash register tape at the end of the day or a savings account passbook. When you look at line item 3, you don't just want to know how much you deposited on day 3; you want to know your running balance (Day 1 + Day 2 + Day 3). As you move down the rows, every new row accumulates all previous rows up to that point.

In SQL, we instruct the database engine to maintain this running tally using the SUM() function paired with an OVER() clause, ordering records sequentially by time.

3. Implementing Overall Cumulative Sales (Naive vs. Window Approach)

When calculating a cumulative sum across a chronological timeline, we utilize the OVER (ORDER BY ...) clause.

Naive vs. Correct SQL Structure

• Incorrect: Placing ORDER BY inside a subquery or aggregating prematurely.
• Correct: Applying the window function directly over the sorted dataset using ORDER BY.

SELECT 
    year_order,
    SUM(sales) AS yearly_sales,
    SUM(SUM(sales)) OVER (ORDER BY year_order) AS cumulative_sales
FROM 
    orders_summary
GROUP BY 
    year_order;

Step-by-Step Execution Breakdown

When the database engine evaluates this query:

1. Sorting: It sorts the result set by year_order in ascending order.
2. First Year (e.g., 2017): The window contains only the first row. Cumulative sales equal the first year's sales (4,84,000).
3. Second Year (e.g., 2018): The window expands to include row 1 and row 2. Cumulative sales equal Row 1 + Row 2 (9,54,000).
4. Subsequent Years: Continues adding all previous rows up to the current row's boundary.

4. Handling Ties and Duplicate Order Values in Window Frames

A critical technical edge case in window functions involves handling identical values within the ORDER BY clause (e.g., duplicate years or identical timestamps).

Note: If two rows share the exact same value in the ORDER BY expression (for instance, if two transactions occur in the exact same year or month), standard cumulative window frames without an explicit frame clause include both rows in the evaluation simultaneously, resulting in identical cumulative outputs for those tied rows.

-- Conceptual duplicate year scenario
Year 2018 | Sales: 100 | Cumulative: 200
Year 2018 | Sales: 100 | Cumulative: 200

To prevent unexpected tie behavior in high-precision datasets (like timestamps), always include a secondary unique identifier (such as order_id or month_order_date) in your ORDER BY clause to guarantee a deterministic sort order.

5. Category-Wise Partitioning: Isolating Groups

In real-world retail analytics, calculating a total store-wide cumulative sum is rarely sufficient. Management wants to see cumulative sales per category (e.g., Furniture, Office Supplies, Technology) restarting independently for each group.

This is where the PARTITION BY clause becomes essential.

SELECT 
    category,
    year_order,
    SUM(sales) AS yearly_sales,
    SUM(SUM(sales)) OVER (PARTITION BY category ORDER BY year_order) AS category_cumulative_sales
FROM 
    orders_summary
GROUP BY 
    category,
    year_order;

How PARTITION BY Alters the Window

• Without PARTITION BY, the window encompasses the entire table from top to bottom.
• With PARTITION BY category, the database creates independent vertical partitions. When the rows switch from Furniture to Office Supplies, the cumulative sum resets to zero and restarts its accumulation for the new category.

6. Advanced Rolling Windows: Moving Beyond Full Cumulative Sums

Sometimes business questions require moving windows rather than lifetime cumulative totals—for example, calculating Rolling 3-Month Sales (the sum of the current month plus the preceding two months).

The Logic of Rolling Windows

Unlike a cumulative sum that grows indefinitely from day one, a rolling window maintains a fixed width (e.g., 3 rows) that slides forward as time progresses.

7. Implementing Rolling 3-Month Sales with SQL Frame Clauses

To restrict window functions to a specific sliding range of rows, we use the ROWS BETWEEN clause in SQL.

Granular Monthly Query Structure

First, extract data at the month level, grouping by both year and month order:

SELECT 
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS month_order_date,
    SUM(sales) AS monthly_sales
FROM 
    orders
GROUP BY 
    YEAR(order_date),
    MONTH(order_date)
ORDER BY 
    order_year, 
    month_order_date;

Applying the Sliding Frame Syntax

To calculate the rolling 3-month sum (current row plus the 2 preceding rows), we apply the framing clause:

SELECT 
    order_year,
    month_order_date,
    monthly_sales,
    SUM(monthly_sales) OVER (
        ORDER BY order_year, month_order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_3_month_sales
FROM (
    SELECT 
        YEAR(order_date) AS order_year,
        MONTH(order_date) AS month_order_date,
        SUM(sales) AS monthly_sales
    FROM orders
    GROUP BY YEAR(order_date), MONTH(order_date)
) monthly_data;

8. Summary and Key Takeaways

• Role of Window Functions: Window functions allow you to perform calculations across related table rows while retaining individual row granularity (unlike GROUP BY).
• Cumulative Sum Syntax: Use SUM(sales) OVER (ORDER BY sort_column) to generate running totals that accumulate values chronologically.
• Partitioning Data: Use PARTITION BY category to segment calculations, ensuring running totals reset independently for each distinct category.
• Tie-Breaker Best Practices: Always include secondary ordering columns in your ORDER BY clause to avoid non-deterministic behavior during ties.
• Rolling Windows: To calculate moving averages or rolling sums (e.g., trailing 3 months), explicitly define the window frame using ROWS BETWEEN N PRECEDING AND CURRENT ROW.
• Subquery Preparation: Rolling and cumulative time-series analyses frequently require pre-aggregating raw transaction data to a higher time grain (such as monthly or yearly summaries) before applying window functions.