📋 Interview Question

📊 Setup: Sales Data

CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    product_id INT,
    product_name VARCHAR(100),
    region VARCHAR(50),
    quarter VARCHAR(10),
    revenue DECIMAL(12, 2),
    quantity INT
);

INSERT INTO sales (product_id, product_name, region, quarter, revenue, quantity) VALUES
(1, 'Laptop', 'North America', 'Q1', 150000.00, 500),
(1, 'Laptop', 'North America', 'Q2', 180000.00, 600),
(1, 'Laptop', 'North America', 'Q3', 165000.00, 550),
(1, 'Laptop', 'North America', 'Q4', 200000.00, 667),
(1, 'Laptop', 'Europe', 'Q1', 120000.00, 400),
(1, 'Laptop', 'Europe', 'Q2', 140000.00, 467),
(1, 'Laptop', 'Europe', 'Q3', 130000.00, 433),
(1, 'Laptop', 'Europe', 'Q4', 170000.00, 567),
(2, 'Phone', 'North America', 'Q1', 250000.00, 2500),
(2, 'Phone', 'North America', 'Q2', 280000.00, 2800),
(2, 'Phone', 'North America', 'Q3', 260000.00, 2600),
(2, 'Phone', 'North America', 'Q4', 300000.00, 3000),
(2, 'Phone', 'Europe', 'Q1', 200000.00, 2000),
(2, 'Phone', 'Europe', 'Q2', 220000.00, 2200),
(2, 'Phone', 'Europe', 'Q3', 210000.00, 2100),
(2, 'Phone', 'Europe', 'Q4', 240000.00, 2400),
(3, 'Tablet', 'North America', 'Q1', 80000.00, 800),
(3, 'Tablet', 'North America', 'Q2', 95000.00, 950),
(3, 'Tablet', 'North America', 'Q3', 90000.00, 900),
(3, 'Tablet', 'North America', 'Q4', 110000.00, 1100),
(3, 'Tablet', 'Europe', 'Q1', 60000.00, 600),
(3, 'Tablet', 'Europe', 'Q2', 75000.00, 750),
(3, 'Tablet', 'Europe', 'Q3', 70000.00, 700),
(3, 'Tablet', 'Europe', 'Q4', 85000.00, 850);

🔄 Part 1: Basic Pivoting with CASE WHEN

-- Pivot: Convert quarters from rows to columns
SELECT
    product_id,
    product_name,
    region,
    SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS Q1_Revenue,
    SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS Q2_Revenue,
    SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS Q3_Revenue,
    SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS Q4_Revenue,
    SUM(revenue) AS Total_Revenue,
    SUM(CASE WHEN quarter = 'Q1' THEN quantity ELSE 0 END) AS Q1_Quantity,
    SUM(CASE WHEN quarter = 'Q2' THEN quantity ELSE 0 END) AS Q2_Quantity,
    SUM(CASE WHEN quarter = 'Q3' THEN quantity ELSE 0 END) AS Q3_Quantity,
    SUM(CASE WHEN quarter = 'Q4' THEN quantity ELSE 0 END) AS Q4_Quantity,
    SUM(quantity) AS Total_Quantity
FROM sales
GROUP BY product_id, product_name, region
ORDER BY product_id, region;

Output:

Pivot with Aggregation

-- Pivot with multiple aggregations
SELECT
    product_name,
    region,
    SUM(CASE WHEN quarter = 'Q1' THEN revenue END) AS Q1,
    SUM(CASE WHEN quarter = 'Q2' THEN revenue END) AS Q2,
    SUM(CASE WHEN quarter = 'Q3' THEN revenue END) AS Q3,
    SUM(CASE WHEN quarter = 'Q4' THEN revenue END) AS Q4,
    ROUND(AVG(revenue), 2) AS Avg_Quarterly_Revenue,
    MAX(revenue) AS Peak_Quarter_Revenue,
    MIN(revenue) AS Lowest_Quarter_Revenue,
    MAX(revenue) - MIN(revenue) AS Revenue_Variance
FROM sales
GROUP BY product_name, region;

🔄 Part 2: Unpivoting with CROSS JOIN

-- Unpivot: Convert columns back to rows
-- Original pivoted data
WITH pivoted AS (
    SELECT
        product_id,
        product_name,
        region,
        SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS Q1,
        SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS Q2,
        SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS Q3,
        SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS Q4
    FROM sales
    GROUP BY product_id, product_name, region
)
-- Unpivot using CROSS JOIN with values
SELECT
    p.product_id,
    p.product_name,
    p.region,
    q.quarter_name AS quarter,
    q.revenue
FROM pivoted p
CROSS JOIN (VALUES
    ('Q1', p.Q1),
    ('Q2', p.Q2),
    ('Q3', p.Q3),
    ('Q4', p.Q4)
) AS q(quarter_name, revenue)
ORDER BY p.product_id, p.region, q.quarter_name;

Unpivot Using UNION ALL

-- Alternative unpivot using UNION ALL
SELECT product_id, product_name, region, 'Q1' AS quarter, Q1 AS revenue
FROM pivoted

UNION ALL

SELECT product_id, product_name, region, 'Q2', Q2
FROM pivoted

UNION ALL

SELECT product_id, product_name, region, 'Q3', Q3
FROM pivoted

UNION ALL

SELECT product_id, product_name, region, 'Q4', Q4
FROM pivoted

ORDER BY product_id, region, quarter;

🔄 Part 3: SQL Server PIVOT/UNPIVOT Syntax

-- SQL Server PIVOT syntax
SELECT
    product_name,
    region,
    Q1, Q2, Q3, Q4
FROM (
    SELECT product_name, region, quarter, revenue
    FROM sales
) AS source_table
PIVOT (
    SUM(revenue)
    FOR quarter IN (Q1, Q2, Q3, Q4)
) AS pivot_table;

-- SQL Server UNPIVOT syntax
SELECT
    product_name,
    region,
    quarter,
    revenue
FROM (
    SELECT product_name, region, Q1, Q2, Q3, Q4
    FROM pivoted_table
) AS source_table
UNPIVOT (
    revenue FOR quarter IN (Q1, Q2, Q3, Q4)
) AS unpivot_table;

PostgreSQL crosstab Function

-- Enable tablefunc extension
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- Use crosstab for pivoting
SELECT *
FROM crosstab(
    'SELECT product_name, quarter, revenue::TEXT
     FROM sales
     ORDER BY product_name, quarter',
    $$VALUES ('Q1'), ('Q2'), ('Q3'), ('Q4')$$
) AS ct(
    product_name VARCHAR,
    Q1 DECIMAL(12,2),
    Q2 DECIMAL(12,2),
    Q3 DECIMAL(12,2),
    Q4 DECIMAL(12,2)
);

🔄 Part 4: Dynamic Pivoting

PostgreSQL Dynamic Pivot

-- Dynamic pivot in PostgreSQL using dynamic SQL
DO $$
DECLARE
    quarter_list TEXT;
    query TEXT;
BEGIN
    -- Get distinct quarters
    SELECT STRING_AGG(DISTINCT quarter, ', ')
    INTO quarter_list
    FROM sales;

    -- Build dynamic query
    query := FORMAT(
        'SELECT product_name, region, %s, SUM(revenue) AS total
         FROM sales
         GROUP BY product_name, region
         ORDER BY product_name, region',
        STRING_AGG(
            FORMAT('SUM(CASE WHEN quarter = %L THEN revenue ELSE 0 END) AS %s',
                   quarter, quarter),
            ', '
        )
    );

    -- Execute dynamic query
    RAISE NOTICE '%', query;
    EXECUTE query;
END $$;

SQL Server Dynamic Pivot

-- Dynamic pivot in SQL Server
DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- Get distinct quarter values
SELECT @columns = STRING_AGG(QUOTENAME(quarter), ', ')
FROM (SELECT DISTINCT quarter FROM sales) AS q;

-- Build dynamic query
SET @sql = N'
SELECT product_name, region, ' + @columns + N'
FROM (
    SELECT product_name, region, quarter, revenue
    FROM sales
) AS source
PIVOT (
    SUM(revenue)
    FOR quarter IN (' + @columns + N')
) AS pivot_table;';

EXEC sp_executesql @sql;

📊 Part 5: Multi-Dimensional Pivoting

-- Pivot with multiple value columns
SELECT
    product_name,
    region,
    SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS Q1_Revenue,
    SUM(CASE WHEN quarter = 'Q1' THEN quantity ELSE 0 END) AS Q1_Quantity,
    SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS Q2_Revenue,
    SUM(CASE WHEN quarter = 'Q2' THEN quantity ELSE 0 END) AS Q2_Quantity,
    SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS Q3_Revenue,
    SUM(CASE WHEN quarter = 'Q3' THEN quantity ELSE 0 END) AS Q3_Quantity,
    SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS Q4_Revenue,
    SUM(CASE WHEN quarter = 'Q4' THEN quantity ELSE 0 END) AS Q4_Quantity
FROM sales
GROUP BY product_name, region;

Pivot with Calculated Fields

-- Pivot with calculated fields
SELECT
    product_name,
    region,
    SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS Q1,
    SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS Q2,
    SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS Q3,
    SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS Q4,
    -- Quarter-over-quarter growth
    ROUND(
        (SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) -
         SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END)) * 100.0 /
        NULLIF(SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END), 0),
        2
    ) AS Q1_to_Q2_Growth_Pct,
    -- Best quarter
    GREATEST(
        SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END),
        SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END),
        SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END),
        SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END)
    ) AS Best_Quarter_Revenue
FROM sales
GROUP BY product_name, region;

📈 Part 6: Pivot for Reporting

Monthly Calendar Pivot

-- Create a monthly sales calendar
WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', sale_date)::DATE AS month,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY DATE_TRUNC('month', sale_date)
)
SELECT
    EXTRACT(YEAR FROM month) AS year,
    SUM(CASE WHEN EXTRACT(MONTH FROM month) = 1 THEN total_sales END) AS Jan,
    SUM(CASE WHEN EXTRACT(MONTH FROM month) = 2 THEN total_sales END) AS Feb,
    SUM(CASE WHEN EXTRACT(MONTH FROM month) = 3 THEN total_sales END) AS Mar,
    SUM(CASE WHEN EXTRACT(MONTH FROM month) = 4 THEN total_sales END) AS Apr,
    SUM(CASE WHEN EXTRACT(MONTH FROM month) = 5 THEN total_sales END) AS May,
    SUM(CASE WHEN EXTRACT(MONTH FROM month) = 6 THEN total_sales END) AS Jun,
    SUM(CASE WHEN EXTRACT(MONTH FROM month) = 7 THEN total_sales END) AS Jul,
    SUM(CASE WHEN EXTRACT(MONTH FROM month) = 8 THEN total_sales END) AS Aug,
    SUM(CASE WHEN EXTRACT(MONTH FROM month) = 9 THEN total_sales END) AS Sep,
    SUM(CASE WHEN EXTRACT(MONTH FROM month) = 10 THEN total_sales END) AS Oct,
    SUM(CASE WHEN EXTRACT(MONTH FROM month) = 11 THEN total_sales END) AS Nov,
    SUM(CASE WHEN EXTRACT(MONTH FROM month) = 12 THEN total_sales END) AS Dec,
    SUM(total_sales) AS Annual_Total
FROM monthly_sales
GROUP BY EXTRACT(YEAR FROM month)
ORDER BY year;

Cross-Tabulation

-- Create a cross-tabulation matrix
SELECT
    product_name,
    region,
    SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS Q1,
    SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS Q2,
    SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS Q3,
    SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS Q4,
    SUM(revenue) AS Total,
    ROUND(SUM(revenue) * 100.0 / SUM(SUM(revenue)) OVER(), 2) AS Pct_of_Total
FROM sales
GROUP BY ROLLUP (product_name, region)
HAVING GROUPING(product_name) = 0 OR GROUPING(region) = 0
ORDER BY product_name, region NULLS LAST;

🎯 Quiz Section

🏆 Best Practices for Interviews

-- Break complex pivots into steps
WITH base_data AS (...),
pivoted AS (
    SELECT
        id,
        SUM(CASE WHEN category = 'A' THEN value END) AS cat_a,
        SUM(CASE WHEN category = 'B' THEN value END) AS cat_b
    FROM base_data
    GROUP BY id
)
SELECT * FROM pivoted;

-- Use COALESCE for clean output
SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS Q1

-- Or use NULLS in output and handle in application
MAX(CASE WHEN quarter = 'Q1' THEN revenue END) AS Q1

-- Pivot on indexed columns
CREATE INDEX idx_sales_quarter ON sales(quarter);
CREATE INDEX idx_sales_product ON sales(product_id, quarter);

-- Verify pivot totals match original data
SELECT
    SUM(Q1 + Q2 + Q3 + Q4) AS pivoted_total,
    (SELECT SUM(revenue) FROM sales) AS original_total
FROM pivoted_table;