Combining WHERE + GROUP BY

-- Revenue by user for DELIVERED orders only (WHERE pre-filters)
SELECT
  u.name,
  COUNT(o.id) AS orders,
  SUM(o.total_amount) AS revenue
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'delivered'          -- filter first
GROUP BY u.id, u.name                 -- then group
ORDER BY revenue DESC;

-- Top products by total stock (only available products)
SELECT
  name,
  price,
  stock
FROM products
WHERE stock > 0                       -- pre-filter
  AND price < 1000
ORDER BY stock DESC;

-- Payments grouped by month (only confirmed payments)
SELECT
  DATE_FORMAT(paid_at, '%Y-%m') AS month,  -- MySQL
  SUM(amount) AS monthly_revenue,
  COUNT(*) AS payment_count
FROM payments
WHERE paid_at IS NOT NULL             -- exclude unpaid
GROUP BY DATE_FORMAT(paid_at, '%Y-%m')
HAVING SUM(amount) > 100             -- only months with >$100
ORDER BY month;