WITH user_ordersAS(
SELECT o.user_id,
o.order_id,
o.total_amount,
o.created_at,
ROW_NUMBER()OVER(PARTITION BY o.user_id
ORDER BY o.created_at ASC) AS order_seq,
LAG(o.total_amount, 1, 0)OVER(PARTITION BY o.user_id
ORDER BY o.created_at ASC) AS prev_amount
FROM orders o
WHERE o.status IN  ('completed', 'shipped')
  AND o.created_at >= '2025-01-01'
),
cohort_summaryAS(
SELECT u.country,
DATE_TRUNC('month', u.registered_at) AS cohort_month,
COUNT(DISTINCT u.user_id) AS total_users,
SUM(uo.total_amount) AS lifetime_revenue,
AVG(uo.total_amount) AS avg_basket_size
FROM users u
LEFT JOIN user_orders uo ON uo.user_id = u.user_id
WHERE u.is_active = TRUE
  AND u.role = 'customer'
GROUP BY u.country,
DATE_TRUNC('month', u.registered_at)
HAVING COUNT(DISTINCT u.user_id) > 10
)
SELECT cs.country,
cs.cohort_month,
cs.total_users,
ROUND(cs.lifetime_revenue, 2) AS formatted_revenue,
CASE
  WHEN cs.lifetime_revenue > 100000 THEN 'Platinum Tier'
  WHEN cs.lifetime_revenue > 25000 THEN 'Gold Tier'
  ELSE 'Standard Tier'
END AS cohort_tier
FROM cohort_summary cs
ORDER BY cs.lifetime_revenue DESC,
cs.total_users DESC
LIMIT 50
OFFSET 0;