2019秋招阅文数据分析：sql查询连续天数

表a，有3个字段，日期字段ds(YYYY-MM-DD，如2019-08-28)，用户登录账号user_id，用户当天的总付费金额hx_amt，取出2019年8月份，连续付费4天及以上的用户的该连续付费的起止日期。

输出如下图：

#presto 

with t1 as (
    select user_id,ds,cast(date_format(ds,'%y%m%d') as bigint) - row_number() over(partition by user_id order by user_id,ds) as group_num
    from a
    where hx_amt > 0
    group by user_id,ds
    order by user_id,ds
)
select user_id,min(ds) as start_date,max(ds) as end_date
from t1
group by user_id,group_num
having count(1) >= 4
order by user_id,start_date