GROUP BY and Aggregation

-- Count
SELECT COUNT(*) FROM users;                    -- Total rows
SELECT COUNT(DISTINCT city) FROM users;        -- Unique cities
SELECT COUNT(*) FROM orders WHERE status = 'completed';

-- Sum
SELECT SUM(amount) FROM orders WHERE user_id = 1;

-- Average
SELECT AVG(age) FROM users;

-- Min/Max
SELECT MIN(age), MAX(age), AVG(age) FROM users;

-- Count users per city
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city;

-- Result:
-- New York  | 150
-- London    | 120
-- Tokyo     | 80
-- Paris     | 65

-- Average age per city
SELECT city, ROUND(AVG(age), 1) AS avg_age
FROM users
GROUP BY city
ORDER BY avg_age DESC;

-- Total revenue per product category
SELECT
    category,
    COUNT(*) AS total_sales,
    SUM(amount) AS revenue,
    AVG(amount) AS avg_sale
FROM orders
JOIN products ON orders.product_id = products.id
GROUP BY category
ORDER BY revenue DESC;

-- Cities with more than 50 users
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING COUNT(*) > 50;

-- Products with total sales > $10,000
SELECT product_name, SUM(amount) AS total_revenue
FROM orders
GROUP BY product_name
HAVING SUM(amount) > 10000
ORDER BY total_revenue DESC;

-- Active users (placed more than 3 orders in last 30 days)
SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE created_at > CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id
HAVING COUNT(*) > 3;

-- Monthly revenue breakdown
SELECT
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    COUNT(*) AS orders,
    SUM(amount) AS revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at)
ORDER BY year, month;

-- Sales by product per region
SELECT region, product, SUM(quantity) AS total_sold
FROM sales
GROUP BY region, product
ORDER BY region, total_sold DESC;

-- The order SQL clauses execute (logical, not actual):
SELECT
    department,
    COUNT(*) AS employees,
    AVG(salary) AS avg_salary
FROM employees                           -- 1. FROM
WHERE hire_date > '2020-01-01'          -- 2. WHERE (filters rows)
GROUP BY department                      -- 3. GROUP BY
HAVING AVG(salary) > 75000              -- 4. HAVING (filters groups)
ORDER BY avg_salary DESC                -- 5. ORDER BY
LIMIT 10;                               -- 6. LIMIT

-- Rank employees by salary within each department
SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS above_below_avg
FROM employees;

-- Running total of orders
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- Row number
SELECT
    *,
    ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_num
FROM posts;

-- Categorize users by age
SELECT name, age,
    CASE
        WHEN age < 18 THEN 'Minor'
        WHEN age BETWEEN 18 AND 35 THEN 'Young Adult'
        WHEN age BETWEEN 36 AND 55 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group
FROM users;

-- Pivot-like aggregation
SELECT
    product,
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_revenue,
    SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS pending_revenue,
    SUM(CASE WHEN status = 'cancelled' THEN amount ELSE 0 END) AS cancelled_revenue
FROM orders
GROUP BY product;