实现用SQL查询连续发文天数/连续登录天数

我的知乎：DarrenChan陈驰

当月最长连续发文天数:
//临时：id_time_table:
select distinct app_id, from_unixtime(create_date_time, 'yyyy-MM-dd') fawen_date
from bjh_ods_task_manage_df
where event_day='$baseDay' 
and object_type=2 
and from_unixtime(create_time,'yyyyMMdd')>='$firstDay'
and from_unixtime(create_time,'yyyyMMdd')<='$lastDay'


//最终：longest_days_table
select app_id, max(cnt) longest_continuous_days
from 
(
    select app_id, group_no, count(*) cnt
    from 
    (
        select app_id, fawen_date,(day(fawen_date) - ROW_NUMBER() OVER (partition by app_id order by fawen_date asc)) group_no 
        from id_time_table
    ) T1
    group by app_id, group_no
) T2
group by app_id