Last Minute SQL Interview Prep Kit: Data Pivoting and Unpivoting Techniques

This chapter focuses on advanced data reshaping, aggregation, and analytical window functions in SQL. Specifically, we will master the patterns required to solve two of the most frequently asked SQL interview questions: Top-N queries within categories (requiring careful granularity analysis and ranking functions) and Year-over-Year (YoY) Growth calculations (utilizing advanced LAG and LEAD operations).

1. The Core Challenge: Granularity & Pre-Aggregation

When approaching complex analytical queries, the most common pitfall is misidentifying the granularity of the source data. Granularity refers to what a single row in a table represents.

The Granularity Contrast

• Employee Table: Typically has a granularity of one row per employee ID. Finding the top-paid employee within each department is straightforward because no initial aggregation is needed.
• Orders Table: Typically has a granularity of one row per transaction line item. If an interviewer asks for the "top 5 products within each category," you cannot simply apply a window function directly to the raw table. A single product can appear in multiple order rows.

The Two-Step Execution Pattern

To solve "Top-N" problems on transactional tables, you must execute a two-step workflow:

Raw Transactional Data (Orders Table)
   |
   | Step 1: Group By (Product ID, Category) & SUM(Sales)
   v
Aggregated Dataset (One row per Product)
   |
   | Step 2: Apply Window Function (PARTITION BY Category ORDER BY SUM(Sales) DESC)
   v
Ranked Dataset (Filter where Rank <= 5)

2. Step-by-Step Implementation: Top-N Products Within Each Category

Let's write the clean, decoupled SQL code to solve this problem. We will use a Common Table Expression (CTE) to handle the aggregation before applying our ranking window function.

Step 1: Aggregate Transactional Data to the Product Level

First, we aggregate the raw order lines to find the total sales for each unique product within its respective category.

SELECT 
    category,
    product_id,
    SUM(sales) AS total_sales
FROM orders
GROUP BY 
    category, 
    product_id;

Step 2: Apply the Window Function and Filter

Next, we wrap the aggregation in a CTE, apply a ranking function partitioned by the category, and filter for the top 5 products.

WITH aggregated_sales AS (
    SELECT 
        category,
        product_id,
        SUM(sales) AS total_sales
    FROM orders
    GROUP BY 
        category, 
        product_id
),
ranked_products AS (
    SELECT 
        category,
        product_id,
        total_sales,
        ROW_NUMBER() OVER (
            PARTITION BY category 
            ORDER BY total_sales DESC
        ) AS sales_rank
    FROM aggregated_sales
)
SELECT 
    category,
    product_id,
    total_sales,
    sales_rank
FROM ranked_products
WHERE sales_rank <= 5;

3. Choosing the Right Ranking Function: Ties & Edge Cases

During interviews, a common follow-up question is: "Why did you choose ROW_NUMBER() over RANK() or DENSE_RANK()?" Your choice of window function determines how the system handles tie values (e.g., if two products have identical sales figures).

Note: Always clarify with your interviewer how they want ties handled. If they want exactly 5 rows returned regardless of ties, use ROW_NUMBER(). If they want to include all tied items in the top 5, use DENSE_RANK().

4. Year-over-Year (YoY) Growth & Lead/Lag Functions

Calculating Year-over-Year (YoY) growth is a classic interview pattern designed to test your mastery of sequential data analysis using LAG() and LEAD().

Real-Life Analogy: The Odometer & The Ledger

Imagine you are tracking your business's annual revenue. To calculate how much you grew this year, you must constantly look over your shoulder at last year's ledger.

• LAG() acts like a rearview mirror: it pulls data from a previous row into your current row's perspective.
• LEAD() acts like a windshield looking forward: it pulls data from a future row into your current row's perspective.

The Mathematical Formula

To calculate YoY growth as a percentage, we use the following formula:

5. Step-by-Step Evolution of the YoY Growth Query

Let's construct the YoY growth query incrementally, starting with raw transactional orders.

Step 1: Aggregate Sales by Year

We must first aggregate our transactional order data to the year level.

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

Step 2: Retrieve Previous Year's Sales Using LAG()

Now, we use a CTE to capture the annual sales, and apply the LAG() window function to bring the previous year's sales into the current row.

WITH yearly_sales 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,
    LAG(annual_sales, 1) OVER (ORDER BY order_year) AS previous_year_sales
FROM yearly_sales;

Output Representation:

6. Handling Edge Cases: The First-Year Null Problem

In the output above, the first year (2020) has no previous year data, resulting in a NULL value for previous_year_sales. If we attempt to calculate growth using a NULL, our result will be NULL.

The Default Value Trick

The LAG() function accepts an optional third argument: LAG(expression, offset, default_value).

If we set the default value to the current year's sales, the growth formula evaluates to:

This elegantly handles the first-year edge case without requiring complex CASE WHEN statements.

-- Applying the default value trick
LAG(annual_sales, 1, annual_sales) OVER (ORDER BY order_year)

7. The Complete YoY Growth Solution

Combining all these concepts, here is the production-grade SQL query to calculate YoY growth:

WITH yearly_sales AS (
    -- Step 1: Aggregate sales at the year level
    SELECT 
        EXTRACT(YEAR FROM order_date) AS order_year,
        SUM(sales) AS annual_sales
    FROM orders
    GROUP BY 
        EXTRACT(YEAR FROM order_date)
),
lagged_sales AS (
    -- Step 2: Fetch previous year's sales, defaulting to current sales if NULL
    SELECT 
        order_year,
        annual_sales,
        LAG(annual_sales, 1, annual_sales) OVER (
            ORDER BY order_year
        ) AS previous_year_sales
    FROM yearly_sales
)
-- Step 3: Calculate YoY growth percentage
SELECT 
    order_year,
    annual_sales,
    previous_year_sales,
    ROUND(
        ((annual_sales - previous_year_sales) / NULLIF(previous_year_sales, 0)) * 100, 
        2
    ) AS yoy_growth_percentage
FROM lagged_sales;

Note on Production Safety: We wrapped the denominator in NULLIF(previous_year_sales, 0). This prevents a runtime Division by Zero error if a business had zero sales in a previous year.

8. Real-World Pitfalls & Debugging Workflows

When implementing these patterns in production or interviews, watch out for these common issues:

1. Missing Years in the Dataset

If your business has sales in 2020 and 2022, but zero sales in 2021, LAG(annual_sales, 1) will fetch the 2020 data for the 2022 row. This is technically a 2-year gap, not a 1-year gap.

• The Fix: If you need strict consecutive years, generate a complete time-series spine using a recursive CTE or a calendar table, and LEFT JOIN your sales data to it before applying LAG().

2. Partitioning Gaps

If you are asked to calculate YoY growth within each product category, you must add a PARTITION BY clause to your window function.

• The Bug: Forgetting PARTITION BY category will cause the first year of Category B to lag its sales from the last year of Category A.
• The Fix:
  SQL

  Copy

  LAG(annual_sales, 1) OVER (PARTITION BY category ORDER BY order_year)
  

Summary and Key Takeaways

• Granularity First: Always check if the source table needs to be aggregated (e.g., using SUM and GROUP BY) before applying window ranking or lag functions.
• Top-N Pattern: Use a CTE to aggregate data, apply a ranking function (ROW_NUMBER(), RANK(), or DENSE_RANK()) in a second CTE, and filter the rank in the outer query.
• Tie-Breaking: Be prepared to explain the difference between ROW_NUMBER() (sequential), RANK() (skips ranks on ties), and DENSE_RANK() (no gaps on ties).
• LAG & LEAD: These functions allow you to query data from adjacent rows without performing self-joins.
• The Default Argument: Use the third parameter of LAG(column, offset, default) to handle boundary conditions (like the first year of data) cleanly.
• Zero-Division Protection: Always wrap your division denominators in NULLIF(column, 0) to make your analytical queries resilient to database errors.