SQL难题

接上一道题目，补充一道类似的面试题：求每个用户连续登录的天数

select

device_id,

count(date) cnt

from(

select

device_id,

date_sub(date, interval rk day) as date

#date_sub(date,interval x day/minute/month...)表示，从data里减

#x 个日期单位；相应的，还有date_add(date,interval x XXX)

#如果相等，那么用户是连续登陆的

from (

select

device_id,

date,

dense_rank() over(partition by device_id order by date) as rk

#dense_rank()非跳跃排序，rank()跳跃排序，row_number()默认排序

from

question_practice_detail

) as a

) as a

group by

device_id,date