用户最长连续下单天数

用户最长连续下单天数

1、求出每个用户每个下单日期
2、求出每个下单日期的排序，按日期升序
3、每个日期减去对应的排序序号，求每个下单日期对应的连续登录天数
4、求每个用户的最长连续天数

代码如下：

SELECT  user_id
        ,MAX(continue_order_cnt) AS max_day
FROM    (
            SELECT  user_id
                    ,DATEADD(pay_rn,rn*-1,'dd') AS first_day
                    ,COUNT(1) AS continue_order_cnt
            FROM    (
                        SELECT  t.*
                                ,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY pay_rn ASC ) AS rn
                        FROM    (
                                    SELECT  user_id
                                            ,DATETRUNC(pay_time,'dd') AS pay_rn
                                    FROM    dwd_order_order_trans_pay_detail
                                    GROUP BY user_id
                                             ,DATETRUNC(pay_time,'dd')
                                ) t
                    ) f
            GROUP BY user_id
                     ,DATEADD(pay_rn,rn*-1,'dd')
        ) g
GROUP BY user_id