Last Minute SQL Interview Prep Kit: Running Averages and Filtering with WHERE vs. HAVING

This chapter focuses on mastering advanced SQL analytical patterns frequently tested in technical interviews. We will explore how to calculate Year-over-Year (YoY) growth within specific categories, manage data granularity, avoid partitioning pitfalls, and construct cumulative (running) sums using window functions.

1. The Problem of Granularity in YoY Growth

When analyzing business performance, calculating overall Year-over-Year (YoY) sales growth provides a high-level view of company health. However, this macro-level perspective often hides underlying trends. To gain actionable insights, businesses must analyze growth at a finer level of granularity—such as by product category, region, or individual product.

To transition from a global table-level calculation to a category-specific calculation, we must adjust the query's granularity. This requires modifying both our aggregation (GROUP BY) and our analytical window functions (PARTITION BY).

2. The Mental Model of Partitioning

To understand how SQL processes window functions with and without partitioning, consider this real-world analogy:

Real-Life Analogy: The Departmental Ledger

Imagine you are an accountant auditing a department store. The store has three main sections: Furniture, Office Supplies, and Technology.

• Without Partitioning (Single Ledger): If you write all sales chronologically in a single notebook and try to calculate growth by comparing each line to the previous one, you will inevitably compare the first year of Office Supplies to the last year of Furniture. This comparison is meaningless because the products and markets are completely different.
• With Partitioning (Separate Ledgers): To get accurate results, you must give each department its own ledger book. You only compare a year's sales to the previous year within that specific book. When you finish the Furniture book, you open a brand-new book for Office Supplies and start your comparisons fresh, ignoring the furniture data entirely.

In SQL, PARTITION BY acts as the mechanism that splits your data into these separate ledger books before applying window functions like LAG() or LEAD().

3. Step-by-Step Implementation of Category-Wise YoY Growth

To calculate YoY growth within each category, we must first aggregate our raw order data by category and year, and then apply a partitioned window function.

Step 1: Aggregate Sales by Category and Year

First, we group the raw transactional data to find the total sales for each category in each year.

SELECT 
    category,
    EXTRACT(YEAR FROM order_date) AS order_year,
    SUM(sales) AS total_sales
FROM orders
GROUP BY 
    category, 
    EXTRACT(YEAR FROM order_date);

Step 2: Apply LAG() with PARTITION BY

Next, we use a Common Table Expression (CTE) to calculate the previous year's sales for each category. We use PARTITION BY category to ensure the window function resets for each category.

WITH CategorySales AS (
    SELECT 
        category,
        EXTRACT(YEAR FROM order_date) AS order_year,
        SUM(sales) AS current_year_sales
    FROM orders
    GROUP BY 
        category, 
        EXTRACT(YEAR FROM order_date)
),
LaggedSales AS (
    SELECT 
        category,
        order_year,
        current_year_sales,
        LAG(current_year_sales, 1) OVER (
            PARTITION BY category 
            ORDER BY order_year
        ) AS previous_year_sales
    FROM CategorySales
)
SELECT * FROM LaggedSales;

Step 3: Calculate the YoY Growth Percentage

Finally, we apply the growth formula:

WITH CategorySales AS (
    SELECT 
        category,
        EXTRACT(YEAR FROM order_date) AS order_year,
        SUM(sales) AS current_year_sales
    FROM orders
    GROUP BY category, EXTRACT(YEAR FROM order_date)
),
LaggedSales AS (
    SELECT 
        category,
        order_year,
        current_year_sales,
        LAG(current_year_sales, 1) OVER (
            PARTITION BY category 
            ORDER BY order_year
        ) AS previous_year_sales
    FROM CategorySales
)
SELECT 
    category,
    order_year,
    current_year_sales,
    COALESCE(previous_year_sales, 0) AS previous_year_sales,
    CASE 
        WHEN previous_year_sales IS NULL THEN 0
        ELSE ROUND(((current_year_sales - previous_year_sales) / previous_year_sales) * 100, 2)
    END AS yoy_growth_percentage
FROM LaggedSales
ORDER BY category, order_year;

4. Why Omitting PARTITION BY Breaks the Logic

If you omit the PARTITION BY clause, the LAG() function treats the entire dataset as a single sequence. This causes data to leak across category boundaries.

The Data Leakage Flow

Comparison of Query Behaviors

5. Variation: Product-Level Month-over-Month (MoM) Growth

A common interview variation asks you to find products whose current month's sales are greater than their previous month's sales.

The underlying logic is identical to our category-wise YoY calculation, but the granularity changes:

• Granularity: product_id + month_year (instead of category + year).
• Partitioning: PARTITION BY product_id (instead of category).
• Ordering: ORDER BY month_year (instead of order_year).

SQL Implementation

Because SQL does not allow window functions directly in the WHERE clause, we must use a CTE to calculate the previous month's sales before filtering.

WITH MonthlyProductSales AS (
    SELECT 
        product_id,
        DATE_TRUNC('month', order_date) AS sales_month,
        SUM(sales) AS current_month_sales
    FROM orders
    GROUP BY 
        product_id, 
        DATE_TRUNC('month', order_date)
),
LaggedMonthlySales AS (
    SELECT 
        product_id,
        sales_month,
        current_month_sales,
        LAG(current_month_sales, 1) OVER (
            PARTITION BY product_id 
            ORDER BY sales_month
        ) AS previous_month_sales
    FROM MonthlyProductSales
)
SELECT 
    product_id,
    sales_month,
    current_month_sales,
    previous_month_sales
FROM LaggedMonthlySales
WHERE current_month_sales > previous_month_sales
ORDER BY product_id, sales_month;

6. Introduction to Cumulative Sums (Running Totals)

A cumulative sum (or running total) is a running parallel aggregate of a column over a sequence of rows. For any given row, the cumulative sum represents the sum of the current row's value plus all preceding values in that partition.

Mathematical Representation

Let $S_t$ be the sales at time $t$, and $C_t$ be the cumulative sales at time $t$. The cumulative sum is defined as:

Alternatively, it can be defined recursively as:

Step-by-Step Numerical Example

7. Step-by-Step Implementation of Cumulative Sums

To calculate a cumulative sum, we first aggregate our raw data to the desired level of granularity (e.g., yearly sales), and then apply the SUM() window function with an ORDER BY clause.

Step 1: Aggregate Sales by Year

First, we calculate the total sales for each year.

SELECT 
    EXTRACT(YEAR FROM order_date) AS order_year,
    SUM(sales) AS annual_sales
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date);

Step 2: Apply the Cumulative Window Function

To calculate a running total, we use the SUM() function with an OVER (ORDER BY ...) clause.

Note: When you include an ORDER BY clause inside a window function without specifying a frame, SQL defaults to using RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This automatically calculates the cumulative sum up to the current row.

WITH YearlySales AS (
    SELECT 
        EXTRACT(YEAR FROM order_date) AS order_year,
        SUM(sales) AS annual_sales
    FROM orders
    GROUP BY EXTRACT(YEAR FROM order_date)
)
SELECT 
    order_year,
    annual_sales,
    SUM(annual_sales) OVER (
        ORDER BY order_year
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sales
FROM YearlySales
ORDER BY order_year;

8. Common Interview Pitfalls & Debugging Workflows

When writing analytical queries under pressure, candidates often make two common mistakes:

Pitfall 1: Filtering Window Functions in the WHERE Clause

A common mistake is trying to filter the results of a window function directly in the WHERE clause:

-- INVALID SQL: Will throw an error
SELECT 
    product_id,
    sales,
    LAG(sales) OVER (PARTITION BY product_id ORDER BY order_date) AS prev_sales
FROM product_sales
WHERE sales > LAG(sales) OVER (PARTITION BY product_id ORDER BY order_date);

• Why it fails: In the SQL order of execution, the WHERE clause runs before window functions are evaluated. At the time of filtering, the window function values do not exist yet.
• The Solution: Wrap the query in a CTE or subquery, and then filter the output of that CTE.

-- CORRECT SQL
WITH LaggedData AS (
    SELECT 
        product_id,
        sales,
        LAG(sales) OVER (PARTITION BY product_id ORDER BY order_date) AS prev_sales
    FROM product_sales
)
SELECT * 
FROM LaggedData
WHERE sales > prev_sales;

Pitfall 2: Forgetting to Handle NULL Values in the First Row

The first row of any partition has no preceding row, so LAG() returns NULL. If you perform direct arithmetic on a NULL value (e.g., sales - prev_sales), the result will be NULL.

• The Solution: Use COALESCE(prev_sales, 0) or a CASE statement to handle the first row of each partition gracefully.

9. Summary and Key Takeaways

• Granularity is Key: Always identify the target granularity of your query (e.g., category-year or product-month) before writing your window functions.
• Partitioning Prevents Leakage: Use PARTITION BY to isolate calculations within specific groups. Omitting it causes data to leak across category boundaries, leading to incorrect calculations.
• Window Function Execution Order: Remember that window functions run after the WHERE, GROUP BY, and HAVING clauses. You must use a CTE or subquery to filter on the results of a window function.
• Cumulative Sum Syntax: A running total is calculated using SUM(column) OVER (ORDER BY column). This defaults to summing all rows from the start of the partition up to the current row.
• Handle First-Row Nulls: Always use COALESCE or CASE WHEN to handle the NULL values returned by LAG() on the first row of a partition.
• MoM and YoY Patterns are Identical: The SQL pattern for calculating growth remains the same whether you are looking at Year-over-Year, Month-over-Month, or Day-over-Day changes; only the date truncation and partitioning columns change.