SQL AVG

-- Average price across all products
SELECT AVG(price) FROM products;

-- By category
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category;

-- Integer columns - cast to keep decimal precision
SELECT AVG(quantity::NUMERIC) FROM order_items;
-- Without the cast you may lose fractional values.

-- Filtered average
SELECT AVG(total)
FROM orders
WHERE status = 'paid'
  AND created_at > NOW() - INTERVAL '30 days';

-- ROUND for human-friendly output
SELECT ROUND(AVG(total)::NUMERIC, 2) AS avg_total
FROM orders;

-- Combine with MIN/MAX/COUNT for distribution view
SELECT
  category,
  COUNT(*)         AS n,
  MIN(price)       AS cheapest,
  AVG(price)::INT  AS avg_price,
  MAX(price)       AS most_expensive
FROM products
GROUP BY category;

-- Median is NOT MIN/MAX/AVG - use PERCENTILE_CONT
SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_price
FROM products;

-- Weighted average - one row per group
SELECT
  region,
  SUM(price * quantity) / NULLIF(SUM(quantity), 0) AS weighted_avg_price
FROM sales
GROUP BY region;
-- NULLIF prevents divide-by-zero.

-- Running average with window function
SELECT
  id, created_at, total,
  AVG(total) OVER (
    ORDER BY created_at
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7_day_avg
FROM orders;

SELECT AVG(price) AS avg_price
FROM products;