ROLLUP and CUBE for hierarchical totals in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of ROLLUP aggregation

Given a sales table with columns region, product, and amount, what is the output of this query?

SELECT region, product, SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP(region, product)
ORDER BY region NULLS LAST, product NULLS LAST;

PostgreSQL

CREATE TABLE sales (region TEXT, product TEXT, amount INT);
INSERT INTO sales VALUES
('East', 'Pen', 10),
('East', 'Pencil', 20),
('West', 'Pen', 15),
('West', 'Pencil', 25);

A [{"region":"East","product":null,"total_sales":30},{"region":"West","product":null,"total_sales":40},{"region":null,"product":null,"total_sales":70}]

B [{"region":"East","product":"Pen","total_sales":10},{"region":"East","product":"Pencil","total_sales":20},{"region":"West","product":"Pen","total_sales":15},{"region":"West","product":"Pencil","total_sales":25},{"region":null,"product":null,"total_sales":70}]

C [{"region":"East","product":"Pen","total_sales":10},{"region":"East","product":"Pencil","total_sales":20},{"region":"East","product":null,"total_sales":30},{"region":"West","product":"Pen","total_sales":15},{"region":"West","product":"Pencil","total_sales":25},{"region":"West","product":null,"total_sales":40},{"region":null,"product":null,"total_sales":70}]

D [{"region":"East","product":"Pen","total_sales":10},{"region":"East","product":"Pencil","total_sales":20},{"region":"West","product":"Pen","total_sales":15},{"region":"West","product":"Pencil","total_sales":25}]

💡 Hint

ROLLUP adds subtotals for each grouping level, including grand total.

ROLLUP(region, product) groups by region and product, then by region only, then grand total (nulls). So it returns rows for each product per region, subtotal per region, and grand total.

❓ query_result

intermediate

Output of CUBE aggregation

Using the same sales table, what is the output of this query?

SELECT region, product, SUM(amount) AS total_sales
FROM sales
GROUP BY CUBE(region, product)
ORDER BY region NULLS LAST, product NULLS LAST;

PostgreSQL

CREATE TABLE sales (region TEXT, product TEXT, amount INT);
INSERT INTO sales VALUES
('East', 'Pen', 10),
('East', 'Pencil', 20),
('West', 'Pen', 15),
('West', 'Pencil', 25);

A [{"region":"East","product":"Pen","total_sales":10},{"region":"East","product":"Pencil","total_sales":20},{"region":"East","product":null,"total_sales":30},{"region":"West","product":"Pen","total_sales":15},{"region":"West","product":"Pencil","total_sales":25},{"region":"West","product":null,"total_sales":40},{"region":null,"product":"Pen","total_sales":25},{"region":null,"product":"Pencil","total_sales":45},{"region":null,"product":null,"total_sales":70}]

B [{"region":"East","product":"Pen","total_sales":10},{"region":"East","product":"Pencil","total_sales":20},{"region":"West","product":"Pen","total_sales":15},{"region":"West","product":"Pencil","total_sales":25},{"region":null,"product":null,"total_sales":70}]

C [{"region":"East","product":null,"total_sales":30},{"region":"West","product":null,"total_sales":40},{"region":null,"product":null,"total_sales":70}]

D [{"region":"East","product":"Pen","total_sales":10},{"region":"West","product":"Pen","total_sales":15},{"region":null,"product":"Pen","total_sales":25}]

💡 Hint

CUBE generates all combinations of grouping columns including subtotals for each and grand total.

CUBE(region, product) returns all combinations: by region and product, by region only, by product only, and grand total.

📝 Syntax

advanced

Identify syntax error in ROLLUP usage

Which option contains a syntax error when using ROLLUP in PostgreSQL?

A SELECT region, product, SUM(amount) FROM sales GROUP BY ROLLUP(region product);

B SELECT region, product, SUM(amount) FROM sales GROUP BY ROLLUP(region, product);

C SELECT region, product, SUM(amount) FROM sales GROUP BY ROLLUP(region, product) ORDER BY region;

D SELECT region, product, SUM(amount) FROM sales GROUP BY ROLLUP(region, product) HAVING SUM(amount) > 10;

💡 Hint

Check the syntax of the ROLLUP function arguments.

ROLLUP requires columns separated by commas inside parentheses. Missing comma causes syntax error.

❓ optimization

advanced

Optimizing query with CUBE on large dataset

You have a large sales table with columns region, product, year, and amount. You want to get all subtotals and grand totals for these three columns using CUBE. Which approach is best to optimize performance?

A Use GROUP BY CUBE(region, product, year) directly on the large table without indexes.

B Create indexes on region, product, and year columns before running GROUP BY CUBE(region, product, year).

C Run separate GROUP BY queries for each combination of columns and UNION ALL the results.

D Use GROUP BY ROLLUP(region, product, year) instead of CUBE to reduce combinations.

💡 Hint

Indexes can speed up grouping operations on large tables.

Creating indexes on grouping columns helps the database quickly group data, improving performance of GROUP BY CUBE.

🧠 Conceptual

expert

Difference between ROLLUP and CUBE

Which statement correctly describes the difference between ROLLUP and CUBE in SQL grouping?

A ROLLUP generates all possible combinations of grouping columns, while CUBE generates hierarchical subtotals.

B ROLLUP and CUBE produce the same output but differ in performance.

C ROLLUP only works with two columns, while CUBE works with any number of columns.

D ROLLUP generates hierarchical subtotals from left to right, while CUBE generates all possible combinations of grouping columns.

💡 Hint

Think about how many grouping sets each generates.

ROLLUP creates subtotals in a hierarchy from left to right columns. CUBE creates all combinations of grouping columns.