力扣---高频SQL题---22---550. 游戏玩法分析 IV

一、题目

二、代码

# Write your MySQL query statement below
-- select co from Activity as table1
-- join
-- (
-- select player_id ,min(event_date ) as min_date ,count(player_id) as co from Activity
-- group by player_id 
-- ) as table2
-- where table1.player_id = table2.player_id
-- and datediff(table1.event_date ,table2.min_date) = 1


select coalesce (round(table2.sum_co/count(distinct player_id ) ,2) ,0) as fraction from Activity 
join 
(
    select sum(case when table1.co then 1 else 0 end) as sum_co from 
    (
        select count( table1.player_id ) as co from Activity as table1
        join
        (
        select player_id ,min(event_date ) as min_date  from Activity
        group by player_id 
        ) as table2
        where table1.player_id = table2.player_id
        and datediff(table1.event_date ,table2.min_date) = 1
        group by table1.player_id
    ) as table1
) as table2

-- select sum(case when table1.co then 1 else 0 end) as sum_co from 
-- (
--     select count( table1.player_id ) as co from Activity as table1
--     join
--     (
--     select player_id ,min(event_date ) as min_date  from Activity
--     group by player_id 
--     ) as table2
--     where table1.player_id = table2.player_id
--     and datediff(table1.event_date ,table2.min_date) = 1
--     group by table1.player_id
-- ) as table1

    

三、运行结果