Databricks Certified Data Engineer Professional free question 3 / 10 · Domain 3: Data Manipulation
GROUP BY CUBE(region, product, channel) and GROUP BY ROLLUP(region, product, channel) are each equivalent to how many grouping sets?
- CUBE gives 7 and ROLLUP gives 3
- CUBE gives 8 and ROLLUP gives 3
- CUBE gives 7 and ROLLUP gives 4
- CUBE gives 8 and ROLLUP gives 4
Show the answer and explanation
Answer: D
Why this is correct
CUBE aggregates over every combination of the listed columns, so N elements give 2^N grouping sets; with 3 columns that is 2^3 = 8, including the empty grouping set () for the grand total. ROLLUP aggregates over a hierarchy by dropping one column at a time from the right, so N elements give N+1 grouping sets. With 3 columns they are (region, product, channel), (region, product), (region), and (), which makes 4.
Why the other options are wrong
"CUBE gives 7 and ROLLUP gives 4" … This count for CUBE leaves out the empty grouping set. The 2^N of CUBE already includes the grand total ().
"CUBE gives 8 and ROLLUP gives 3" … The +1 in the ROLLUP count of N+1 is the empty grouping set that represents the grand total. This answer drops it.
"CUBE gives 7 and ROLLUP gives 3" … This answer leaves out the empty grouping set, which stands for the grand total, in both CUBE and ROLLUP.
Key point
CUBE and ROLLUP are shorthand for GROUPING SETS. When a GROUP BY lists several GROUPING SETS (including CUBE and ROLLUP), Databricks SQL takes their cross product and treats it as a single GROUPING SETS.