题解 | 游戏平台新玩家消费与进阶行为分析

SELECT p.player_id,username,current_level,
CASE WHEN current_level>=30 THEN "高阶玩家" ELSE "新秀玩家" END AS player_category,ROUND(SUM(cost),2) AS total_april_spending
FROM (
    SELECT player_id,username,level AS current_level
    FROM players 
    WHERE MONTH(creation_date)=3 AND YEAR(creation_date)=2025 ) p
LEFT JOIN(
    SELECT player_id,SUM(cost) as cost
    FROM transactions
    WHERE MONTH(purchase_time)=4 AND YEAR(purchase_time)=2025
    GROUP BY player_id
) t
ON p.player_id=t.player_id
WHERE t.cost >0
GROUP BY p.player_id,username,current_level
ORDER BY player_category desc,total_april_spending desc,player_id asc