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

# 第一步：取出用户、购物日期、购物次数的   表
select
	 distinct user_id,
	 sales_date d1,
	 dense_rank() over(partition by user_id order by sales_date) rk
from
	sales_tb

# 第二步：判断日期是否连续→如果连续的话，用sales_date-rank得到的日期是一样的且都是同一用户
select
	*,
	row_number() over(partition by a.user_id,a.d2 order by a.d1) rk2
from
(
	select
	 distinct user_id,
	 sales_date d1,
	 dense_rank() over(PARTITION by user_id ORDER BY sales_date) rk,
	 sales_date-(dense_rank() over(PARTITION by user_id ORDER BY sales_date) ) d2
	from
		sales_tb
) a

# 第三步：筛选上表存在连续购买行为的用户
select 
	user_id, 
	max(rk2)
from 
(
	select
		*,
		row_number() over(partition by a.user_id,a.d2 order by a.d1) rk2
	from
	(
		select
		distinct user_id,
		sales_date d1,
		row_number() over(PARTITION by user_id ORDER BY sales_date) rk,
		sales_date-(row_number() over(PARTITION by user_id ORDER BY sales_date) ) d2
		from
			sales_tb
	) a
) b
where rk2 >= 2
group by user_id