-- Created on savesnippets.com · https://savesnippets.com/1jd97cTWM7GUXA
-- Orders per user
SELECT user_id,
       COUNT(*)        AS order_count,
       SUM(amount)     AS total_spent,
       MIN(created_at) AS first_order,
       MAX(created_at) AS last_order,
       AVG(amount)     AS avg_order
FROM   orders
GROUP  BY user_id
ORDER  BY total_spent DESC;

-- Group by multiple columns
SELECT country, state, COUNT(*) AS user_count
FROM   users
GROUP  BY country, state
ORDER  BY country, user_count DESC;

-- Group + filter aggregates with HAVING (NOT WHERE — that's per-row)
SELECT user_id, COUNT(*) AS orders
FROM   orders
GROUP  BY user_id
HAVING COUNT(*) > 10        -- only users with > 10 orders
ORDER  BY orders DESC;