Advanced Aggregations & Pivot/Unpivot

-- GROUPING SETS: multiple GROUP BY levels in one query
-- Instead of running 3 separate queries and UNIONing them:

-- Traditional approach (3 queries):
SELECT department, NULL AS job_title, SUM(salary) FROM employees GROUP BY department
UNION ALL
SELECT NULL, job_title, SUM(salary) FROM employees GROUP BY job_title
UNION ALL
SELECT NULL, NULL, SUM(salary) FROM employees;

-- GROUPING SETS (one query, one table scan!):
SELECT department, job_title, SUM(salary) AS total_salary,
       COUNT(*) A
...

-- ROLLUP: hierarchical subtotals (most common in reports)
-- Generates subtotals from right to left + grand total

SELECT
    region,
    country,
    city,
    SUM(revenue) AS total_revenue,
    COUNT(*) AS store_count
FROM stores
GROUP BY ROLLUP (region, country, city)
ORDER BY region, country, city;

-- This produces:
-- region | country | city     | revenue    (detail)
-- NA     | US      | NYC      | 500,000
-- NA     | US      | LA       | 400,000
-- NA     | US      | NULL     | 900,000 
...

-- CUBE: every possible combination of subtotals
-- Like ROLLUP but crosses ALL dimensions

SELECT
    COALESCE(category, 'ALL CATEGORIES') AS category,
    COALESCE(region, 'ALL REGIONS') AS region,
    COALESCE(quarter, 'ALL QUARTERS') AS quarter,
    SUM(revenue) AS total_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM sales
GROUP BY CUBE (category, region, quarter)
ORDER BY category, region, quarter;

-- CUBE(a, b, c) generates ALL combinations:
-- (a,b,c), (a,b), (a,c), (b
...

-- PIVOT: turn row values into columns
-- (Standard SQL uses CASE WHEN; some DBs have PIVOT syntax)

-- Raw data: rows per month
-- | product | month | sales |
-- | Widget  | Jan   | 100   |
-- | Widget  | Feb   | 150   |
-- | Gadget  | Jan   | 200   |

-- PIVOT with CASE WHEN (works everywhere):
SELECT
    product,
    SUM(CASE WHEN month = 'Jan' THEN sales END) AS jan,
    SUM(CASE WHEN month = 'Feb' THEN sales END) AS feb,
    SUM(CASE WHEN month = 'Mar' THEN sales END) AS mar,
    SUM(CASE W
...

-- FILTER clause: conditional aggregation (PostgreSQL)
-- Cleaner than CASE WHEN inside aggregates

-- Traditional CASE WHEN approach:
SELECT
    department,
    COUNT(*) AS total_employees,
    COUNT(CASE WHEN salary > 100000 THEN 1 END) AS high_earners,
    SUM(CASE WHEN status = 'active' THEN salary ELSE 0 END) AS active_payroll
FROM employees
GROUP BY department;

-- FILTER clause (PostgreSQL 9.4+) — much cleaner:
SELECT
    department,
    COUNT(*) AS total_employees,
    COUNT(*) FILTER (W
...