Advanced Aggregation

-- ROLLUP: hierarchical subtotals
SELECT region, product, SUM(revenue)
FROM sales
GROUP BY ROLLUP(region, product);

-- CUBE: all possible combinations
SELECT region, product, SUM(revenue)
FROM sales
GROUP BY CUBE(region, product);

-- GROUPING SETS: explicit levels
SELECT region, product, SUM(revenue)
FROM sales
GROUP BY GROUPING SETS (
  (region, product),
  (region),
  ()
);

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'active') AS active,
  COUNT(*) FILTER (WHERE status = 'churned') AS churned,
  AVG(amount) FILTER (WHERE amount > 0) AS avg_positive_amount
FROM customers;