SELECT
  u.id,
  u.name,
  u.city,
  CASE
    WHEN o.amount >= 500 THEN 'high'
    WHEN o.amount > 0 THEN 'normal'
    ELSE 'none'
  END AS tier,
  count(o.id) AS order_cnt
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
  AND u.created_at >= '2026-01-01'
  AND u.city IN (
    '杭州',
    '上海',
    '北京',
    '深圳',
    '成都'
  )
GROUP BY
  u.id,
  u.name,
  u.city
HAVING count(o.id) > 0
ORDER BY
  order_cnt DESC,
  u.id ASC
LIMIT 50