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

-- 有purchase_time购买行为，就【至少一次消费记录】，不用专门过滤次数了
-- 如果至少2次， 增加一个having（t.transaction_id） >=2 即可
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 
    players as p 
    inner join transactions as t on p.player_id = t.player_id
where
    p.creation_date >= '2025-03-01' and p.creation_date <'2025-04-01'
    and t.purchase_time >= '2025-04-01' and t.purchase_time < '2025-05-01'
group by 
    p.player_id, p.username, p.level

order by 
    case when player_category = '高阶玩家' then 0 else 1 end asc ,
    total_april_spending desc ,
    p.player_id asc ;

select player_id,username,level current_level,
if(level>=30,'高级玩家','新秀玩家') player_category,
round(sum(cost),2) total_april_spending
from (
    select p.player_id,username,level,creation_date,transaction_id,item_name,cost,purchase_time
    from players p join transactions t on p.player_id = t.player_id 
    and creation_date like '2025-03%' and purchase_time like '2025-04%'
) t1 
group by player_id,username,level
order by player_category desc,total_april_spending desc,player_id

with people as(
    select p.player_id,
           sum(case when date(purchase_time) between '2025-04-01' and ' 2025-04-30' then 1 else 0 end) as purchase_cnt
    from players p
    inner join transactions t on p.player_id = t.player_id
    where month(creation_date) = '3'
    group by p.player_id
    having purchase_cnt>=1
)

select distinct p.player_id,username,level as current_level,
       case when level>=30 then '高阶玩家' else '新秀玩家' end as player_category,
       sum(cost) over(partition by p.player_id) as total_april_spending
from players p
inner join transactions t2 on p.player_id = t2.player_id
where p.player_id in (select player_id from people) and (date(purchase_time) between '2025-04-01' and ' 2025-04-30')
order by field(player_category,'高阶玩家','新秀玩家'),total_april_spending desc ,player_id asc

select player_id,username,current_level,
    if(current_level >=30,'高阶玩家','新秀玩家') player_category,
    total_april_spending
from (
    select 
        player_id,username,level current_level,
        sum(cost) total_april_spending
    from transactions
    join players using(player_id)
    where creation_date like '2025-03%'
        and purchase_time like '2025-04%'
    group by player_id,username,current_level
)a
order by player_category desc,total_april_spending desc,player_id