Part 174: Advanced Aggregations: QUALIFY, ROLLUP, CUBE & GROUPING SETS

Producing multi-dimensional sub-totals, cubes, and clean window filtering in standard SQL.

1. The QUALIFY Clause

In standard SQL, you cannot filter window functions directly in the WHERE clause without wrapping the query in a subquery or CTE. The QUALIFY clause (supported in Snowflake, BigQuery, Databricks, DuckDB, Teradata) filters window results directly!

-- Without QUALIFY (Requires CTE):
WITH ranked AS (
    SELECT id, department, salary,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT * FROM ranked WHERE rnk <= 3;

-- With QUALIFY (Clean, elegant, 1 step):
SELECT id, department, salary
FROM employees
QUALIFY DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) <= 3;

2. Multi-Dimensional Totals with ROLLUP

Calculates hierarchical subtotals and grand totals in a single query pass:

SELECT 
    COALESCE(region, 'Grand Total') AS region,
    COALESCE(country, 'Subtotal') AS country,
    SUM(sales_amount) AS revenue
FROM regional_sales
GROUP BY ROLLUP (region, country);

3. CUBE and GROUPING SETS

• CUBE: Computes all $2^N$ possible permutations of dimensions.
• GROUPING SETS: Explicitly declares exact combinations to compute, avoiding expensive cartesian aggregation.

SELECT brand, category, SUM(units_sold)
FROM retail_sales
GROUP BY GROUPING SETS (
    (brand, category), -- By brand and category
    (brand),           -- Subtotal per brand
    ()                 -- Overall grand total
);

Key Takeaways

• Use QUALIFY to eliminate noisy subqueries when filtering top-N records.
• Use GROUPING SETS to generate full BI dashboard rollups in a single scan.

In Part 175, we explore Sessionization Analysis in SQL: Window Lag & Inactivity Limits!