Advanced Analytics—Technology and Tools: In-Database Analytics - Data Science and Big Data Analytics - page 467

WHERE r.product_id IN (SELECT o.product_id
FROM orders_recent o
GROUP BY o.product_id
ORDER BY SUM(o.item_quantity) DESC
LIMIT 3)
GROUP BY CUBE( r.product_id, DATE_PART('year',
r.order_datetime) )
ORDER BY r.product_id,
DATE_PART('year', r.order_datetime

product_id year total
15060 2013 5996
15060 2014 57
15060 6053
15066 2013 6030
15066 2014 52
15066 6082
15072 2013 6023
15072 2014 66
15072 6089
2013 18049 ← additional row

2014 175 ← additional row

18224

Because null values in the output indicate the subtotal and grand total rows,
care must be taken when null values appear in the columns being grouped. For
example, null values may be part of the dataset being analyzed. The GROUPING()
function can identify which rows with null values are used for the subtotals or
grand totals.

SELECT r.product_id,
DATE_PART('year', r.order_datetime) AS year,
SUM(r.item_quantity) AS total,
GROUPING(r.product_id) AS group_id,
GROUPING(DATE_PART('year', r.order_datetime)) AS
group_year
FROM orders_recent r
WHERE r.product_id IN (SELECT o.product_id
FROM orders_recent o