GROUP BY

SELECT 
    grouping_column,
    AGGREGATE_FUNCTION(column)
FROM table
GROUP BY grouping_column;

-- Count users by country
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country;

-- Total revenue per customer
SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(total) AS total_spent
FROM orders
GROUP BY user_id;

-- Average rating per product
SELECT product_id, AVG(rating) AS avg_rating
FROM reviews
GROUP BY product_id;

-- Sales by year and month
SELECT 
    strftime('%Y', created_at) AS year,
    strftime('%m', created_at) AS month,
    SUM(total) AS revenue
FROM orders
GROUP BY 
    strftime('%Y', created_at),
    strftime('%m', created_at)
ORDER BY year, month;

-- Users by country and status
SELECT country, status, COUNT(*)
FROM users
GROUP BY country, status;

-- Top customers by total spending
SELECT 
    u.name,
    u.email,
    COUNT(o.id) AS orders,
    SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email
ORDER BY total_spent DESC;