记录SQL用法—with和日期

 以力抠 550. 游戏玩法分析 IV：. - 力扣（LeetCode）为例子，记录SQL的with和日期的用法

with t1 as(
select player_id,min(event_date) first_date
from Activity
group by player_id
),
t2 as(
select player_id,first_date,DATE_ADD(first_date, INTERVAL 1 DAY) second_date,(select(second_date) - first_date) diff
from t1
),
t3 as (
    select t2.player_id
    from t2
    where exists(
        select player_id
        from Activity act 
        where act.player_id = t2.player_id and 
        act.event_date = t2.second_date
    )
)

select round((select count(*) from t3) /(select count(distinct (player_id)) from Activity) ,2)fraction

要点：

1、with的中间表可以引用前面建好的中间表，如t2引用t1，t3引用t2（这些中间表就是你的问题处理过程）。

2、日期相加函数DATE_ADD的用法：DATE_ADD(some_date, INTERVAL n DAY)。