Aggregation

SELECT
  COUNT(*) AS total_orders,
  COUNT(DISTINCT user_id) AS unique_customers,
  SUM(total) AS revenue,
  AVG(total) AS avg_order_value,
  MIN(total) AS smallest_order,
  MAX(total) AS largest_order
FROM orders;

-- Revenue by category
SELECT category,
       COUNT(*) AS num_products,
       AVG(price) AS avg_price,
       SUM(price * quantity) AS total_revenue
FROM products
GROUP BY category
ORDER BY total_revenue DESC;

-- Monthly order summary
SELECT DATE_TRUNC('month', order_date) AS month,
       COUNT(*) AS orders,
       SUM(total) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Categories with more than 10 products
SELECT category,
       COUNT(*) AS num_products,
       AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 10
ORDER BY num_products DESC;

-- Customers who spent more than $1000
SELECT u.name,
       SUM(o.total) AS total_spent
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.name
HAVING SUM(o.total) > 1000;

-- Combine WHERE and HAVING
SELECT category,
       COUNT(*) AS num_products,
       AVG(price) AS avg_price
FROM products
WHERE price > 10           -- filter rows first
GROUP BY category
HAVING COUNT(*) >= 5      -- then filter groups
ORDER BY avg_price DESC;