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

How to think about it

All three save you from writing several GROUP BY queries and UNION ALL-ing them. The difference is which combinations each produces — and naming that precisely is what the interviewer is checking.

• ROLLUP(A, B) — hierarchical subtotals, peeling one column from the right: (A,B), (A), (). Use it when the columns form a natural hierarchy (year → quarter, country → region).
• CUBE(A, B) — every combination, 2^N groupings: (A,B), (A), (B), (). Use it for cross-dimensional pivots with row totals, column totals, and a grand total.
• GROUPING SETS — the general form; you list exactly the groupings you want and nothing more.

They’re really the same machine at different settings:

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

A worked example — ROLLUP, emulated in SQLite

SQLite has no ROLLUP/CUBE/GROUPING SETS, so the portable equivalent is explicit UNION ALL — which is exactly what the engine does internally anyway. This produces the two-level output of GROUP BY ROLLUP(region):

SELECT region, SUM(amount) AS total, 'region subtotal' AS level
FROM sales
GROUP BY region
UNION ALL
SELECT NULL AS region, SUM(amount) AS total, 'grand total' AS level
FROM sales
ORDER BY region NULLS LAST;

region	total	level
East	330	region subtotal
West	440	region subtotal
NULL	770	grand total

The first two rows are the per-region subtotals (East 330, West 440); the last row collapses the region dimension entirely for the grand total of 770. On PostgreSQL or Snowflake you’d write that whole thing as GROUP BY ROLLUP(region) and get the same rows in one clause — the NULL in region being the rollup placeholder, not real data.

Telling rollup NULLs from real NULLs

That placeholder NULL is the catch: if your data also has genuine NULL regions, a NULL row is ambiguous — subtotal or real value? GROUPING(col) disambiguates, returning 1 for a rollup placeholder and 0 for a data NULL:

SELECT CASE WHEN GROUPING(region) = 1 THEN 'All Regions' ELSE region END AS region_label,
       SUM(amount)
FROM sales
GROUP BY ROLLUP(region);