sql常见面试题目积累

1、求用户连续登陆最大天数

select 
        a.device_id,
        max(cnt)
from(
        select 
                device_id,
                date_new,
                count(1) as cnt 
        from(
                select 
                        a.device_id,
                        a.dp,
                        a.rcnt,
                        substr(dateadd(to_date(dp,'yyyy-mm-dd'),rcnt*-1,'dd'),1,10) as date_new
                from(
                        select 
                                device_id,
                                dp,
                                row_number() over(partition by device_id order by dp asc) rcnt
                        from  browse_by_device_topic
                        where 
                                dp >= '2020-12-01'
                                and dp <= '2020-12-22'
                                and device_id > 0
                     
                ) as a 
        )
        group by 
                device_id,
                date_new
) as a 
group by 
        a.device_id;