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

with temp0 as (
    select distinct p.player_id
    from players p inner join transactions t
    on p.player_id = t.player_id
    where date_format(creation_date,"%Y-%m") = "2025-03"
    and purchase_time >= "2025-04-01" and purchase_time <= "2025-04-30"
), temp1 as (
    select t0.player_id,round(sum(cost),2) as total_april_spending
    from temp0 t0 inner join transactions t on t0.player_id = t.player_id
    where purchase_time >= "2025-04-01" and purchase_time <= "2025-04-30"
    group by t0.player_id
), temp2 as (
    select t1.player_id,p.username,
    level as current_level,
    case
        when level >= 30 then "高阶玩家"
        else "新秀玩家"
    end as player_category,total_april_spending
    from temp1 t1 inner join players p on t1.player_id = p.player_id
    order by player_category desc,total_april_spending desc,t1.player_id asc
)

select * from temp2;