-- Created on savesnippets.com · https://savesnippets.com/WCHDlUdGW4s2N8
-- Bucket users by age
SELECT id, name,
       CASE
         WHEN age < 18          THEN 'minor'
         WHEN age < 30          THEN 'young adult'
         WHEN age < 65          THEN 'adult'
         ELSE                        'senior'
       END AS age_group
FROM users;

-- Conditional aggregation — count by category in a single pass
SELECT
    COUNT(*)                                            AS total,
    SUM(CASE WHEN status = 'active'   THEN 1 ELSE 0 END) AS active_count,
    SUM(CASE WHEN status = 'archived' THEN 1 ELSE 0 END) AS archived_count,
    SUM(CASE WHEN amount > 100        THEN amount ELSE 0 END) AS big_ticket_revenue
FROM orders;

-- Custom sort order with CASE in ORDER BY
SELECT id, status
FROM orders
ORDER BY
  CASE status
    WHEN 'pending'  THEN 1
    WHEN 'shipped'  THEN 2
    WHEN 'delivered' THEN 3
    ELSE 99
  END,
  created_at DESC;