SELECT
  c.name AS customer,
  c.country,
  c.tier,
  cat.name AS category,
  p.name AS product,
  SUM(oi.qty) AS units_sold,
  SUM(oi.qty * oi.unit_price) AS gross_revenue,
  SUM(oi.qty * oi.unit_price * (1 - oi.discount/100)) AS net_revenue,
  AVG(oi.discount) AS avg_discount_pct,
  MIN(o.order_date)::DATE AS first_purchase,
  MAX(o.order_date)::DATE AS last_purchase
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories cat ON p.cat_id = cat.cat_id
WHERE o.status = 'completed'
GROUP BY c.cust_id, c.name, c.country, c.tier, cat.cat_id, cat.name, p.product_id, p.name
ORDER BY net_revenue DESC
LIMIT 50;