leetcode 550 游戏玩法分析IV

游戏玩法分析

 

select round(avg(a.event_date is not null), 2) as fraction
from 
    (select player_id, min(event_date) as event_date
    from activity
    group by player_id) as p 
left join activity as a 
on p.player_id=a.player_id
and a.event_date = date_add(p.event_date, interval 1 day)

 

我自己的写法

 

select round(sum(if(p.player_id is not null, 1, 0)) / count(distinct a.player_id), 2) as fraction from Activity a
left join (
    select player_id, min(event_date) as event_date from Activity
    group by player_id
) as p
on a.player_id = p.player_id 
    and a.event_date = date_add(p.event_date, interval 1 day)

 

使用 DATEDIFF(`日期1`, `日期2`) = 1 也可

avg =  sum( if( `具体条件`,1, 0) ) / count(`具体字段`)