Explanation:
The correct option is B.
The result table shows 9 rows with the following pattern:
group_1 and group_2 (A/Y, A/Z, B/Y, B/Z, and the two rows where one is NULL).group_1 (rows where group_2 is NULL: A/null = 50, B/null = 50).group_2 (rows where group_1 is NULL: null/Y = 70, null/Z = 30).This exact set of rows (detailed groups + hierarchical subtotals by first column + subtotals by second column + grand total) is produced by GROUP BY ... WITH ROLLUP.
Option B uses:
GROUP BY group_1, group_2 WITH ROLLUP;
GROUP BY group_1, group_2 WITH ROLLUP;
ROLLUP generates hierarchical super-aggregates from right to left.(group_1, group_2) → detailed rows(group_1) → subtotals with group_2 = NULL() → grand total with both NULLA: INCLUDING NULL is not standard SQL syntax for grouping (it may be a distractor or specific to very few dialects). Normal GROUP BY ignores or treats NULLs as a group, but does not add extra subtotal/grand total rows.
C: Plain GROUP BY group_1, group_2 only returns the detailed combinations (A/Y, A/Z, B/Y, B/Z, etc.). No subtotal or grand total rows → only ~6–7 rows, not 9.
D: GROUP BY group_1, group_2, (group_1, group_2) is invalid or redundant syntax in most databases. It does not produce rollup-style subtotals.
E: WITH CUBE generates all possible combinations of groupings (including the symmetric ones like (group_2) separately). For two columns it would add extra rows you don’t see in the given result (e.g., more permutations of NULLs). CUBE is useful for full cross-tabulation, but over-produces here.
The given result matches the hierarchical behavior of ROLLUP exactly.
So the analyst ran option B.
Ultimate access to all questions.
A data analyst is processing a complex aggregation on a table with zero null values and their query returns the following result:
| group_1 | group_2 | sum |
|---|---|---|
| null | null | 100 |
| null | Y | 70 |
| null | Z | 30 |
| A | null | 50 |
| A | Y | 30 |
| A | Z | 20 |
| B | null | 50 |
| B | Y | 40 |
| B | Z | 10 |
Which of the following queries did the analyst run to obtain the above result?
A
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2 INCLUDING NULL;
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2 INCLUDING NULL;
B
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2 WITH ROLLUP;
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2 WITH ROLLUP;
C
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2;
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2;
D
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2, (group_1, group_2);
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2, (group_1, group_2);
E
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2 WITH CUBE;
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2 WITH CUBE;