What are SQL aggregate functions? Explain COUNT, SUM, AVG, MAX, MIN.

| id | customer | product  | quantity | price  | status    |
|----|----------|----------|----------|--------|-----------|
|  1 | Alice    | Laptop   |        1 | 999.99 | completed |
|  2 | Bob      | Mouse    |        3 |  29.99 | completed |
|  3 | Alice    | Keyboard |        2 |  79.99 | pending   |
|  4 | Charlie  | Laptop   |        1 | 999.99 | completed |
|  5 | Bob      | Monitor  |        1 | 299.99 | cancelled |
|  6 | Alice    | Headset  |        2 |  59.99 | completed |

-- Count all rows
SELECT COUNT(*) AS total_orders FROM orders;
-- Result: 6

-- Count non-NULL values in a column
SELECT COUNT(customer) AS orders_with_customer FROM orders;

-- Count distinct values
SELECT COUNT(DISTINCT customer) AS unique_customers FROM orders;
-- Result: 3

-- Count with condition (conditional aggregation)
SELECT
    COUNT(*) AS total,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed,
    COUNT(CASE WHEN status = 'pending'   THEN 1 END) AS pending,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled
FROM orders;
-- total=6, completed=4, pending=1, cancelled=1

-- Total revenue from completed orders
SELECT SUM(price * quantity) AS total_revenue
FROM orders
WHERE status = 'completed';
-- (999.99 + 89.97 + 999.99 + 119.98) = 2209.93

-- Sum per customer
SELECT customer, SUM(price * quantity) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY customer
ORDER BY total_spent DESC;

-- Average order value
SELECT AVG(price) AS avg_price FROM orders;

-- Average per product category
SELECT product, AVG(price) AS avg_price
FROM orders
GROUP BY product;

-- Round to 2 decimal places
SELECT ROUND(AVG(price), 2) AS avg_price FROM orders;

-- Most and least expensive product
SELECT MAX(price) AS highest_price,
       MIN(price) AS lowest_price
FROM orders;

-- Latest and earliest order
SELECT MAX(created_at) AS latest_order,
       MIN(created_at) AS first_order
FROM orders;

-- Per customer: most expensive purchase
SELECT customer, MAX(price) AS biggest_purchase
FROM orders
GROUP BY customer;

SELECT
    customer,
    COUNT(*)                    AS order_count,
    SUM(price * quantity)       AS total_spent,
    ROUND(AVG(price), 2)        AS avg_order_value,
    MAX(price)                  AS most_expensive,
    MIN(price)                  AS cheapest
FROM orders
WHERE status = 'completed'
GROUP BY customer
HAVING COUNT(*) >= 2           -- only customers with 2+ orders
ORDER BY total_spent DESC;

-- After running tests: check counts match expectations
SELECT
    COUNT(*)                                            AS total_records,
    COUNT(CASE WHEN status = 'active' THEN 1 END)      AS active_count,
    SUM(amount)                                         AS total_amount,
    MAX(created_at)                                     AS latest_record
FROM test_orders
WHERE test_run_id = 'RUN-2024-001';