AVG, MIN, MAX

-- AVG: arithmetic mean (ignores NULLs)
SELECT AVG(salary) FROM employees;
SELECT AVG(DISTINCT salary) FROM employees;  -- avg of unique salaries

-- MIN: smallest value (works on numbers, dates, strings)
SELECT MIN(price) FROM products;
SELECT MIN(created_at) FROM users;           -- earliest signup
SELECT MIN(name) FROM cities;               -- alphabetically first

-- MAX: largest value
SELECT MAX(price) FROM products;
SELECT MAX(created_at) FROM orders;         -- most recent order
SELECT MAX(name) FROM cities;              -- alphabetically last

-- ROUND for decimal precision
SELECT ROUND(AVG(rating), 1) FROM reviews; -- e.g., 4.3

-- Together in one query
SELECT
  COUNT(*) AS products,
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive,
  ROUND(AVG(price), 2) AS avg_price,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_price
FROM products WHERE is_active = TRUE;

-- Pricing analysis per product category
SELECT
  cat.name                        AS category,
  COUNT(p.id)                     AS product_count,
  MIN(p.price)                    AS min_price,
  MAX(p.price)                    AS max_price,
  ROUND(AVG(p.price), 2)         AS avg_price,
  MAX(p.price) - MIN(p.price)     AS price_range,
  -- Price tier breakdown
  COUNT(CASE WHEN p.price < 1000 THEN 1 END)          AS budget_products,
  COUNT(CASE WHEN p.price BETWEEN 1000 AND 10000 THEN 1 END) AS mid_products,
  COUNT(CASE WHEN p.price > 10000 THEN 1 END)         AS premium_products,
  -- Rating summary
  ROUND(AVG(r.rating), 2)        AS avg_rating,
  MAX(p.created_at)              AS newest_product_date
FROM categories cat
JOIN products p ON cat.id = p.category_id AND p.is_active = TRUE
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY cat.id, cat.name
ORDER BY avg_price DESC;