grouping_sets.sql

SELECT
  day,
  month,
  year,
  sum(total)
FROM 
  revenue
GROUP BY GROUPING SETS (
  (day, month, year),
  (month, year),
  (year)
);

original_rollup.sql

SELECT
  day,
  month,
  year,
  sum(total)
FROM
  revenue
GROUP BY
  day, month, year
  
UNION

SELECT
  NULL,
  month,
  year,
  sum(total)
FROM
  revenue
GROUP BY
  month, year
  
  UNION
  
SELECT
  NULL,
  NULL,
  year,
  sum(total)
FROM
  revenue
GROUP BY
  year

rollup.sql

SELECT
  day,
  month,
  year,
  sum(total)
FROM 
  revenue
GROUP BY ROLLUP(day, month, year);