GROUP BY

-- Basic GROUP BY
SELECT category, COUNT(*) AS count
FROM products
GROUP BY category;

-- GROUP BY with multiple columns (group by combination)
SELECT department, job_title, COUNT(*), AVG(salary)
FROM employees
GROUP BY department, job_title;

-- GROUP BY with expression
SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*)
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- GROUP BY with HAVING (filter groups after aggregation)
SELECT user_id, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 10000    -- only high-value customers
ORDER BY total_spent DESC;

-- ROLLUP: group by + subtotals
SELECT department, job_title, SUM(salary)
FROM employees
GROUP BY ROLLUP (department, job_title);

-- Weekly revenue by product category and region
SELECT
  DATE_TRUNC('week', o.created_at) AS week,
  cat.name                          AS category,
  u.region,
  COUNT(DISTINCT o.id)              AS orders,
  COUNT(DISTINCT o.user_id)         AS unique_buyers,
  SUM(oi.quantity)                  AS units_sold,
  SUM(oi.quantity * oi.unit_price)  AS gross_revenue,
  ROUND(
    SUM(oi.quantity * oi.unit_price) / COUNT(DISTINCT o.id),
    2
  )                                 AS avg_order_value
FROM orders o
JOIN order_items oi  ON o.id = oi.order_id
JOIN products p      ON oi.product_id = p.id
JOIN categories cat  ON p.category_id = cat.id
JOIN users u         ON o.user_id = u.id
WHERE
  o.status = 'completed'
  AND o.created_at >= NOW() - INTERVAL '12 weeks'
GROUP BY week, cat.id, cat.name, u.region
ORDER BY week DESC, gross_revenue DESC;