ROLLUP and CUBE for hierarchical totals in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate hierarchical totals using ROLLUP.

PostgreSQL

SELECT department, product, SUM(sales) FROM sales_data GROUP BY [1];

Drag options to blanks, or click blank then click option'

ACUBE(department, product)

BGROUPING SETS(department, product)

CROLLUP(department, product)

DGROUP BY department, product

Attempts:

3 left

💡 Hint

Common Mistakes

Using CUBE instead of ROLLUP when hierarchical totals are needed.

Forgetting to use an aggregate function like SUM.

The ROLLUP function creates hierarchical totals by grouping first by department, then product, and finally a grand total.

2fill in blank

medium

Complete the code to calculate all combinations of totals using CUBE.

PostgreSQL

SELECT region, category, SUM(amount) FROM sales GROUP BY [1];

Drag options to blanks, or click blank then click option'

ACUBE(region, category)

BGROUPING SETS((region), (category))

CROLLUP(region, category)

DGROUP BY region, category

Attempts:

3 left

💡 Hint

Common Mistakes

Using ROLLUP instead of CUBE when all combinations are needed.

Not including all grouping columns inside the CUBE function.

The CUBE function generates all possible combinations of grouping columns, giving totals for each combination.

3fill in blank

hard

Fix the error in the GROUP BY clause to correctly use ROLLUP.

PostgreSQL

SELECT category, subcategory, SUM(revenue) FROM sales_data GROUP BY [1];

Drag options to blanks, or click blank then click option'

AGROUPING SETS(category, subcategory)

BCUBE(category, subcategory)

Ccategory, subcategory

DROLLUP(category, subcategory)

Attempts:

3 left

💡 Hint

Common Mistakes

Listing columns without ROLLUP or CUBE for hierarchical totals.

Using GROUPING SETS incorrectly without parentheses.

To get hierarchical totals, the GROUP BY clause must use ROLLUP(category, subcategory) instead of just listing columns.

4fill in blank

hard

Fill both blanks to create a query that uses CUBE and filters out grand totals.

PostgreSQL

SELECT region, product, SUM(sales) FROM sales_data GROUP BY [1] HAVING GROUPING([2]) = 0;

Drag options to blanks, or click blank then click option'

ACUBE(region, product)

Bregion

Cproduct

DROLLUP(region, product)

Attempts:

3 left

💡 Hint

Common Mistakes

Using ROLLUP instead of CUBE when all combinations are needed.

Filtering on the wrong column in GROUPING.

Using CUBE(region, product) groups all combinations. The GROUPING(region) = 0 condition filters out rows where region is a subtotal or grand total.

5fill in blank

hard

Fill all three blanks to write a query that uses ROLLUP and shows which rows are subtotals or totals.

PostgreSQL

SELECT department, team, SUM(profit), GROUPING([1]) AS dept_grouping, GROUPING([2]) AS team_grouping FROM profits GROUP BY [3];

Drag options to blanks, or click blank then click option'

Adepartment

Bteam

CROLLUP(department, team)

DCUBE(department, team)

Attempts:

3 left

💡 Hint

Common Mistakes

Using CUBE instead of ROLLUP when hierarchical totals are needed.

Mismatching columns inside GROUPING and GROUP BY.

This query uses ROLLUP(department, team) to get hierarchical totals. The GROUPING() function shows if a row is a subtotal or total for each column.