-- 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;
