sql aggregation analytics postgresql grouping rollup cube

Advanced aggregation and analytical functions

Maria Garcia Feb 2026

2 tabs

-- ROLLUP for hierarchical subtotals
SELECT
  COALESCE(category, 'ALL CATEGORIES') AS category,
  COALESCE(subcategory, 'ALL SUBCATEGORIES') AS subcategory,
  SUM(revenue) AS total_revenue,
  COUNT(*) AS order_count
FROM sales
GROUP BY ROLLUP(category, subcategory)
ORDER BY category NULLS FIRST, subcategory NULLS FIRST;

-- Result includes:
-- Total for each (category, subcategory)
-- Subtotal for each category
-- Grand total

-- CUBE for all grouping combinations
SELECT
  COALESCE(region, 'ALL') AS region,
  COALESCE(product, 'ALL') AS product,
  COALESCE(quarter, 'ALL') AS quarter,
  SUM(revenue) AS revenue
FROM sales
GROUP BY CUBE(region, product, quarter);

-- GROUPING SETS for specific combinations
SELECT
  category,
  brand,
  color,
  SUM(revenue) AS revenue
FROM products
GROUP BY GROUPING SETS (
  (category, brand),
  (category, color),
  (brand, color),
  ()
);

-- FILTER clause for conditional aggregation
SELECT
  product_id,
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders,
  SUM(total) AS total_revenue,
  SUM(total) FILTER (WHERE created_at >= CURRENT_DATE - 30) AS revenue_last_30_days,
  AVG(total) FILTER (WHERE total > 100) AS avg_large_order
FROM orders
GROUP BY product_id;

-- String aggregation
SELECT
  user_id,
  STRING_AGG(product_name, ', ' ORDER BY created_at DESC) AS recent_purchases,
  STRING_AGG(DISTINCT category, ' | ') AS categories_purchased
FROM orders
GROUP BY user_id;

-- JSON aggregation
SELECT
  category,
  JSON_AGG(
    JSON_BUILD_OBJECT(
      'id', id,
      'name', name,
      'price', price,
      'stock', stock
    ) ORDER BY price DESC
  ) AS products
FROM products
GROUP BY category;

-- Array aggregation
SELECT
  user_id,
  ARRAY_AGG(order_id ORDER BY created_at DESC) AS order_ids,
  ARRAY_AGG(DISTINCT product_category) AS categories
FROM orders
GROUP BY user_id;

-- Ordered-set aggregates (percentiles)
SELECT
  product_category,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_price,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY price) AS q1_price,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY price) AS q3_price,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY price) AS p95_price,
  MODE() WITHIN GROUP (ORDER BY brand) AS most_common_brand
FROM products
GROUP BY product_category;

-- Multiple percentiles at once
SELECT
  category,
  PERCENTILE_CONT(ARRAY[0.25, 0.5, 0.75, 0.95])
    WITHIN GROUP (ORDER BY price) AS price_percentiles
FROM products
GROUP BY category;

-- Hypothetical-set aggregates
SELECT
  RANK(250.00) WITHIN GROUP (ORDER BY salary DESC) AS rank_if_salary_was_250k,
  PERCENT_RANK(250.00) WITHIN GROUP (ORDER BY salary DESC) AS percentile_rank
FROM employees;

-- DISTINCT in aggregates
SELECT
  COUNT(*) AS total_orders,
  COUNT(DISTINCT user_id) AS unique_customers,
  COUNT(DISTINCT product_id) AS unique_products,
  COUNT(DISTINCT DATE(created_at)) AS days_with_orders
FROM orders;

-- Conditional aggregation (pre-FILTER syntax)
SELECT
  product_id,
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_count,
  SUM(CASE WHEN status = 'completed' THEN total ELSE 0 END) AS completed_revenue,
  AVG(CASE WHEN rating >= 4 THEN rating END) AS avg_good_rating
FROM orders
GROUP BY product_id;

-- Moving average
SELECT
  date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7_days
FROM daily_sales
ORDER BY date;

-- Cumulative sum
SELECT
  month,
  revenue,
  SUM(revenue) OVER (ORDER BY month) AS cumulative_revenue
FROM monthly_sales
ORDER BY month;

-- Year-over-year comparison
SELECT
  date,
  revenue,
  LAG(revenue, 365) OVER (ORDER BY date) AS revenue_last_year,
  revenue - LAG(revenue, 365) OVER (ORDER BY date) AS yoy_change,
  ROUND(
    100.0 * (revenue - LAG(revenue, 365) OVER (ORDER BY date)) /
    NULLIF(LAG(revenue, 365) OVER (ORDER BY date), 0),
    2
  ) AS yoy_pct_change
FROM daily_sales
ORDER BY date;

-- Rank products by sales within each category
SELECT
  category,
  product_name,
  total_sales,
  RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS sales_rank,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS dense_rank,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_sales DESC) AS row_num
FROM product_sales
ORDER BY category, sales_rank;

-- Top N per group
SELECT *
FROM (
  SELECT
    category,
    product_name,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
  FROM products
) ranked
WHERE rn <= 3;

-- Running total with reset
SELECT
  user_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY order_date
  ) AS running_total
FROM orders
ORDER BY user_id, order_date;

-- First and last value in window
SELECT
  user_id,
  order_date,
  amount,
  FIRST_VALUE(amount) OVER (
    PARTITION BY user_id
    ORDER BY order_date
  ) AS first_order_amount,
  LAST_VALUE(amount) OVER (
    PARTITION BY user_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_order_amount
FROM orders;

-- Nth value
SELECT
  category,
  product_name,
  price,
  NTH_VALUE(price, 2) OVER (
    PARTITION BY category
    ORDER BY price DESC
  ) AS second_highest_price
FROM products;

-- NTILE for quartiles
SELECT
  user_id,
  total_spent,
  NTILE(4) OVER (ORDER BY total_spent) AS quartile,
  NTILE(10) OVER (ORDER BY total_spent) AS decile,
  NTILE(100) OVER (ORDER BY total_spent) AS percentile
FROM user_totals;

-- Complex analytics query
WITH monthly_metrics AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    COUNT(DISTINCT user_id) AS unique_customers,
    SUM(total) AS revenue,
    AVG(total) AS avg_order_value
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
  month,
  order_count,
  unique_customers,
  revenue,
  avg_order_value,
  -- Month-over-month growth
  revenue - LAG(revenue) OVER (ORDER BY month) AS mom_revenue_change,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) /
    NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
    2
  ) AS mom_revenue_pct,
  -- Moving average
  AVG(revenue) OVER (
    ORDER BY month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS revenue_3mo_avg,
  -- Cumulative
  SUM(revenue) OVER (ORDER BY month) AS cumulative_revenue,
  -- Ranking
  RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM monthly_metrics
ORDER BY month;

-- Cohort analysis
WITH user_cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(created_at)) AS cohort_month
  FROM orders
  GROUP BY user_id
),
cohort_data AS (
  SELECT
    c.cohort_month,
    DATE_TRUNC('month', o.created_at) AS order_month,
    COUNT(DISTINCT o.user_id) AS users,
    SUM(o.total) AS revenue
  FROM user_cohorts c
  JOIN orders o ON c.user_id = o.user_id
  GROUP BY c.cohort_month, DATE_TRUNC('month', o.created_at)
)
SELECT
  cohort_month,
  order_month,
  users,
  revenue,
  FIRST_VALUE(users) OVER (
    PARTITION BY cohort_month
    ORDER BY order_month
  ) AS cohort_size,
  ROUND(
    100.0 * users / FIRST_VALUE(users) OVER (
      PARTITION BY cohort_month
      ORDER BY order_month
    ),
    2
  ) AS retention_pct
FROM cohort_data
ORDER BY cohort_month, order_month;

2 files · sql Explain with highlit

Advanced aggregations extract insights from data. ROLLUP creates hierarchical subtotals. CUBE generates all possible grouping combinations. GROUPING SETS specifies exact grouping combinations. FILTER clause conditions aggregations. String aggregation concatenates values. JSON aggregation builds complex structures. Ordered set aggregates compute percentiles, mode. Hypothetical set aggregates answer what-if questions. Moving aggregates track trends. Array aggregation collects values. Understanding aggregate functions unlocks powerful analytics. Aggregate filters improve query clarity. Proper use of aggregations replaces complex application logic with efficient SQL. Aggregations are foundational for reporting, analytics, data warehousing.