2.4GROUP BY and HAVING

GROUP BY collapses rows into groups. HAVING filters groups (like WHERE, but for aggregated data).

Detecting Duplicates

-- Duplicate emails (data integrity issue)
SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Duplicate orders for the same user within 1 minute (possible double-click bug)
SELECT user_id, COUNT(*) AS order_count, MIN(created_at), MAX(created_at)
FROM orders
WHERE created_at > NOW() - INTERVAL '1 hour'
GROUP BY user_id
HAVING COUNT(*) > 1
   AND MAX(created_at) - MIN(created_at) < INTERVAL '1 minute';

Error Analysis

-- Error distribution in last 24 hours
SELECT error_code, COUNT(*) AS occurrences,
       MIN(created_at) AS first_seen,
       MAX(created_at) AS last_seen
FROM error_logs
WHERE created_at > NOW() - INTERVAL '24 hours'
GROUP BY error_code
ORDER BY occurrences DESC;

-- Errors by hour (find peak error times)
SELECT DATE_TRUNC('hour', created_at) AS hour,
       COUNT(*) AS error_count
FROM error_logs
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY DATE_TRUNC('hour', created_at)
ORDER BY hour;

-- Error rate by endpoint
SELECT endpoint, COUNT(*) AS total_requests,
       SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) AS errors,
       ROUND(100.0 * SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) / COUNT(*), 2) AS error_rate
FROM request_logs
WHERE created_at > NOW() - INTERVAL '24 hours'
GROUP BY endpoint
HAVING SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) > 0
ORDER BY error_rate DESC;

Business Metrics

-- Orders per status (verify expected distribution)
SELECT status, COUNT(*) AS count
FROM orders
GROUP BY status
ORDER BY count DESC;

-- Average order value by month
SELECT DATE_TRUNC('month', created_at) AS month,
       COUNT(*) AS order_count,
       ROUND(AVG(total), 2) AS avg_order_value,
       ROUND(SUM(total), 2) AS total_revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- Top 10 customers by order count
SELECT u.email, COUNT(o.id) AS order_count, SUM(o.total) AS total_spent
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'completed'
GROUP BY u.email
ORDER BY order_count DESC
LIMIT 10;