题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#

with t1 as -- 查询用户分别在哪天购买过产品(去重),并排序
         (
             select distinct user_id,
                             sales_date,
                             dense_rank() over (partition by user_id order by sales_date asc) 
                                as drk_date
             from sales_tb
         )
# 错误情况:直接查看排序第二(未考虑连续),但是案例依然能通过
# select user_id, max(drk_date) days_count from t1
# where drk_date >= 2
# group by user_id;

# 正确方案A:
select user_id,
# 分组辅助列,把连续的起始日期一致的分为一组,最终问题转化为:
# 同一个用户,在同一个连续起始日期,有多少天购买记录
#        date_sub(sales_date, interval drk_date-1 day) d1_group,
       count(*) days_count
from t1
group by user_id, date_sub(sales_date, interval drk_date-1 day)
having days_count >= 2
order by user_id;

# 方案B,效率不高,但是分步执行更清楚数据来源
# , d1_day_log as -- 查询用户每次连续期间的天数顺序(只购买一次的也保留)
#     (
#         select user_id,
#         # 辅助列,可不保留
#                date_sub(sales_date, INTERVAL drk_date - 1 day)
#                    as d1_date,
#         # 同一用户,同一起始日期d1_date的排序
#                dense_rank() over (partition by user_id, date_sub(sales_date, INTERVAL drk_date - 1 day) order by sales_date)
#                    as drk_d1_day
#         from user_buy_log
#     )

# select user_id,
#        max(drk_d1_day) days_count
# from d1_day_log
# group by user_id
# having days_count >= 2;