WHERE vs HAVING: B2B customers by order count and average value

-- ======================================================================
-- Q: Find the B2B customers with more than 10 orders in the last quarter and an
-- average value above 500 EUR.
--
-- WHERE cuts rows before grouping (cheaper, can use an index), HAVING filters on
-- the aggregates. FILTER (WHERE ...) is cleaner than SUM(CASE WHEN ...). Amounts
-- always in cents or numeric, never float.
-- ======================================================================

SELECT o.customer_id,
       COUNT(*)                         AS orders_cnt,
       AVG(o.total_cents) / 100.0       AS avg_eur,
       COUNT(*) FILTER (WHERE o.channel = 'b2b_portal') AS portal_orders
FROM orders o
JOIN customers c ON c.id = o.customer_id AND c.kind = 'b2b'
WHERE o.created_at >= now() - interval '3 months'
  AND o.status IN ('paid', 'shipped')
GROUP BY o.customer_id
HAVING COUNT(*) > 10 AND AVG(o.total_cents) > 50000
ORDER BY avg_eur DESC;