SELECT
  c.tier,
  COUNT(DISTINCT c.cust_id) AS customers,
  COUNT(o.order_id) AS total_orders,
  ROUND(AVG(o.total), 2) AS avg_order_value,
  SUM(o.total) AS total_revenue
FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id AND o.status = 'completed'
GROUP BY c.tier
ORDER BY avg_order_value DESC NULLS LAST;