SQL Hard Asked at MicrosoftAsked at OracleAsked at Snowflake

What are ROLLUP, CUBE, and GROUPING SETS, and when would you choose each?

The short answer

All three are extensions to GROUP BY that produce multiple levels of aggregation in a single query. ROLLUP produces hierarchical subtotals, CUBE produces all possible subtotal combinations, and GROUPING SETS lets you specify exactly which grouping combinations you want.

How to think about it

The honest answer starts here: all three save you from writing several GROUP BY queries and UNION ALL-ing them together. The difference is in which combinations they produce — and understanding that precisely is what the interviewer is checking.

ROLLUP — hierarchical subtotals, left to right

Use this when your columns form a natural hierarchy (year → quarter → month, or country → region → city). It produces subtotals by “peeling” one column at a time from the right.

-- In PostgreSQL / Snowflake / BigQuery:
SELECT year, quarter, SUM(revenue)
FROM sales
GROUP BY ROLLUP(year, quarter);

What rows you get:

 year  | quarter | sum
-------+---------+--------
 2024  |   1     |  120    ← (year, quarter)
 2024  |   2     |  200    ← (year, quarter)
 2024  |  NULL   |  320    ← (year subtotal)
  NULL |  NULL   |  320    ← grand total

The NULL in quarter is the rollup placeholder — not a real NULL in your data.

CUBE — every possible combination

N columns → 2^N groupings. Use this for cross-dimensional analysis like a pivot table where you want row totals, column totals, and a grand total.

SELECT country, category, SUM(revenue)
FROM sales
GROUP BY CUBE(country, category);
-- Produces: (country, category), (country), (category), ()

GROUPING SETS — you specify exactly what you want

ROLLUP and CUBE are shorthands. GROUPING SETS is the general form — list precisely which groupings you want, nothing more:

SELECT country, category, SUM(revenue)
FROM sales
GROUP BY GROUPING SETS (
    (country, category),   -- detail rows
    (country),             -- country subtotal
    ()                     -- grand total
    -- notice: no (category) alone — unlike CUBE
);

How they relate

ROLLUP(A, B)      ≡   GROUPING SETS ((A,B), (A), ())
CUBE(A, B)        ≡   GROUPING SETS ((A,B), (A), (B), ())
GROUPING SETS     →   you write the full list explicitly

Distinguishing rollup NULLs from real NULLs

GROUPING(col) returns 1 when the NULL is a rollup placeholder, 0 when it’s a genuine data NULL:

SELECT
    CASE WHEN GROUPING(country) = 1 THEN 'All Countries'
         ELSE country END AS country_label,
    SUM(revenue)
FROM sales
GROUP BY ROLLUP(country);

Emulating ROLLUP with UNION ALL (SQLite-compatible)

SQLite does not support ROLLUP/CUBE/GROUPING SETS. The portable equivalent is explicit UNION ALL — which is exactly what the engine is doing internally anyway. This playground shows the two-level output you’d get from GROUP BY ROLLUP(region):

Learn it properly Aggregates & GROUP BY