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

select
    p.player_id,
    p.username,
    p.level as current_level,
    case
        when p.level >= 30 then "高阶玩家"
        else "新秀玩家"
    end as player_category,
    round(sum(t.cost), 2) as total_april_spending
from
    (
        select
            player_id,
            username,
            level,
            creation_date
        from
            players
        where
            year(creation_date) = 2025
            and month(creation_date) = 3
    ) p
    join (
        select
            transaction_id,
            player_id,
            item_name,
            cost,
            purchase_time
        from
            transactions
        where
            year(purchase_time) = 2025
            and month(purchase_time) = 4
    ) t on p.player_id = t.player_id
group by
    p.player_id, p.username, p.level
order by
    player_category desc,
    total_april_spending desc,
    p.player_id asc