牛客SQL29：某宝店铺连续2天及以上购物的用户及其对应的天数(连续问题)

牛客SQL29 ：某宝店铺连续2天及以上购物的用户及其对应的天数

一、描述

11月结束后，小牛同学需要对其在某宝的网店就11月份用户交易情况和产品情况进行分析以更好的经营小店。

11月份销售数据表sales_tb如下（其中，sales_date表示销售日期，user_id指用户编号，item_id指货号，sales_num表示销售数量，sales_price表示结算金额）：

sales_date	user_id	item_id	sales_num	sales_price
2021-11-01	1	A001	1	90
2021-11-01	2	A002	2	220
2021-11-01	2	B001	1	120
2021-11-02	3	C001	2	500
2021-11-02	4	B001	1	120
2021-11-03	5	C001	1	240
2021-11-03	6	C002	1	270

以上是给出部分表示例

二、解题思路

这个就是一个连续问题的解题思路，详细解题思路见：sql解决连续问题
大概的步骤可以分为以下几步

0. 根据题意考虑是否需要去重，比如连续登录天数，可能存在一天内有多次登录
1. 按照user_id分组，日期排序，给出序号
2. 用日期减去序号的天数，得到最开始的日期early_time，最开始日期一样的，就是连续登录的
3. 按照user_id和early_time分组，求count

三、代码

-- 3.计算出每个user_id的连续购买天数，count
select user_id
  ,count(*) days_count
from (
   -- 2.找到最开始的时间early_date
   select user_id
     ,sales_date
     ,date_sub(sales_date, interval dr day) tmp_date
   from (
      -- 1.按照user_id分区，销售时间排序
      select user_id
        ,sales_date
        ,dense_rank()
        over(partition by user_id order by sales_date) dr
      from (
         -- 0.去重同一天多个用户下单
         select distinct sales_date
           ,user_id
         from sales_tb
         where month(sales_date)=11
      ) t1
   ) t2
) t3
group by user_id, tmp_date
having count(*) > 1

这里可以不用写那么多子查询，但是为了清晰我分开写的，如果减少子查询，可以直接写

SELECT user_id
    ,COUNT(*) days_count
FROM (
    SELECT DISTINCT user_id
    	,sales_date
    	,DENSE_RANK() 
    	over(PARTITION by user_id ORDER BY sales_date) dr
FROM sales_tb
) t1
GROUP BY user_id,DATE_ADD(sales_date,INTERVAL - dr day)
HAVING days_count >=2
ORDER BY user_id