Hive sql 面试题-计算3天沉默用户数

表名称：user
uid login_time
1 ‘2023-04-01 12:02:00’
1 ‘2023-04-01 13:04:00’
3 ‘2023-04-01 23:02:00’
1 ‘2023-04-02 23:02:00’
1 ‘2023-04-03 23:02:00’
2 ‘2023-04-07 23:02:00’

select count(distinct if(cha_date > 3,uid,null )) as '超过3天',
       count(distinct if(cha_date > 5,uid,null )) as '超过5天'
from (
    select uid ,
           login_time,
           datediff(date_sub(login_time ,1 ) , LAG(login_time,1,login_time) over (partition by uid order by desc )) as cha_date,
           LAG(login_time,1,login_time )  over(partition by uid order by desc ) as pre_time
    from (
      select uid,date_format(login_time,'%Y-%m-%d') as login_time
      from user
      group by uid
    ) t1
) t2 
1
2
3
4
5
6
7
8
9
10
11
12
13