WITH
  customer_revenue AS (
    SELECT
      c.customer_id,
      c.first_name || ' ' || c.last_name AS full_name,
      c.country,
      SUM(o.total_amount) AS total_spent,
      COUNT(o.order_id) AS orders_count,
      ROW_NUMBER() OVER (
        PARTITION BY
          c.country
        ORDER BY
          SUM(o.total_amount) DESC
      ) AS country_rank
    FROM
      customers c
      INNER JOIN orders o ON c.customer_id = o.customer_id
    WHERE
      o.status IN ('COMPLETED', 'SHIPPED')
      AND o.created_at >= '2025-01-01'
    GROUP BY
      c.customer_id,
      c.first_name,
      c.last_name,
      c.country
    HAVING
      COUNT(o.order_id) >= 3
  )
SELECT
  customer_id,
  full_name,
  country,
  total_spent,
  orders_count,
  country_rank,
  CASE
    WHEN total_spent > 10000 THEN 'VIP Diamond'
    WHEN total_spent > 5000 THEN 'Platinum'
    ELSE 'Gold'
  END AS tier_status
FROM
  customer_revenue
WHERE
  country_rank <= 10
ORDER BY
  country ASC,
  total_spent DESC;