SELECT 
  DATE(order_date) as date,
  COUNT(*) as order_count,
  SUM(total_amount) as revenue,
  ROUND(AVG(total_amount), 2) as avg_order_value
 FROM orders
 WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND status NOT IN ('cancelled', 'refunded')
 GROUP BY DATE(order_date)
 ORDER BY date;

SELECT 
  p.sku,
  p.name,
  p.category,
  SUM(oi.quantity) as units_sold,
  SUM(oi.quantity * oi.unit_price) as revenue,
  SUM(oi.quantity * (oi.unit_price - p.cost)) as gross_profit,
  ROUND(100.0 * SUM(oi.quantity * (oi.unit_price - p.cost)) / 
  NULLIF(SUM(oi.quantity * oi.unit_price), 0), 2) as profit_margin_pct
 FROM order_items oi
 INNER JOIN products p ON oi.product_id = p.id
 INNER JOIN orders o ON oi.order_id = o.id
 WHERE o.order_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  AND o.status = 'completed'
 GROUP BY p.id, p.sku, p.name, p.category
 ORDER BY revenue DESC
 LIMIT 20;

WITH customer_metrics AS (
  SELECT 
  customer_id,
  DATEDIFF(CURDATE(), MAX(order_date)) as recency_days,
  COUNT(*) as frequency,
  SUM(total_amount) as monetary
  FROM orders
  WHERE status = 'completed'
  AND order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
  GROUP BY customer_id
 ),
 rfm_scores AS (
  SELECT *,
  NTILE(5) OVER (ORDER BY recency_days DESC) as R,
  NTILE(5) OVER (ORDER BY frequency) as F,
  NTILE(5) OVER (ORDER BY monetary) as M
  FROM customer_metrics
 )
 SELECT 
  customer_id,
  recency_days, frequency, monetary,
  R, F, M,
  CASE 
  WHEN R >= 4 AND F >= 4 THEN 'Champions'
  WHEN R >= 4 AND F >= 2 THEN 'Loyal Customers'
  WHEN R >= 3 AND F >= 3 THEN 'Potential Loyalists'
  WHEN R >= 4 AND F = 1 THEN 'New Customers'
  WHEN R <= 2 AND F >= 4 THEN 'At Risk'
  WHEN R <= 2 AND F <= 2 THEN 'Lost'
  ELSE 'Others'
  END as segment
 FROM rfm_scores;

SELECT 
  p.sku,
  p.name,
  i.quantity as current_stock,
  i.reorder_point,
  COALESCE(sales.units_sold, 0) as last_month_sales,
  CASE 
  WHEN i.quantity = 0 THEN 'Out of Stock'
  WHEN i.quantity <= i.reorder_point THEN 'Low Stock'
  ELSE 'OK'
  END as stock_status
 FROM products p
 INNER JOIN inventory i ON p.id = i.product_id
 LEFT JOIN (
  SELECT oi.product_id, SUM(oi.quantity) as units_sold
  FROM order_items oi
  INNER JOIN orders o ON oi.order_id = o.id
  WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  GROUP BY oi.product_id
 ) sales ON p.id = sales.product_id
 WHERE i.quantity <= i.reorder_point
 ORDER BY last_month_sales DESC;