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

-- 先查找到三月份创建账户，四月有消费的玩家id
with temp0 as (
    select distinct p.player_id
    from players p
    inner join transactions t
    on t.player_id = p.player_id
    where date_format(p.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(t.cost),2) as total_april_spending 
    from temp0 t0
    inner join transactions t
    on t.player_id = t0.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,
    p.level as current_level,
    CASE  -- 玩家等级分类
        WHEN p.level >= 30 THEN "高阶玩家"
        ELSE "新秀玩家"
    END AS player_category,
    t1.total_april_spending
    from temp1 t1
    inner join players p
    on p.player_id = t1.player_id
    ORDER BY 
        player_category DESC,  -- 高阶玩家在前
        total_april_spending DESC,  -- 消费金额降序
        t1.player_id ASC  -- 玩家ID升序

)
select * from temp2