HAVING

-- HAVING syntax (comes AFTER GROUP BY)
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 1000;    -- only categories with avg price > 1000

-- HAVING vs WHERE:
-- WHERE: filters rows BEFORE grouping (can't use aggregates)
-- HAVING: filters groups AFTER grouping (can use aggregates)

-- Both together
SELECT user_id, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
WHERE created_at >= '2025-01-01'   -- filter rows first (can use index)
GROUP BY user_id
HAVING COUNT(*) >= 5               -- then filter groups (high-frequency buyers)
ORDER BY total DESC;

-- Common HAVING patterns
HAVING COUNT(*) > 1         -- groups with more than 1 row (find duplicates)
HAVING SUM(amount) > 50000  -- high-value groups
HAVING MAX(price) < 10000   -- groups where all items are under 10K

-- Find duplicate email addresses in users table
SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY count DESC;

-- Identify VIP customers: ordered 10+ times with total > ₹1 lakh
SELECT
  u.id,
  u.name,
  u.email,
  COUNT(DISTINCT o.id)    AS total_orders,
  SUM(o.total_amount)     AS lifetime_value,
  AVG(o.total_amount)     AS avg_order_value,
  MAX(o.created_at)       AS last_order
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.id, u.name, u.email
HAVING
  COUNT(DISTINCT o.id) >= 10
  AND SUM(o.total_amount) >= 100000
ORDER BY lifetime_value DESC;