题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#
某宝店铺连续2天及以上购物的用户及其对应的天数
https://www.nowcoder.com/practice/63ac3be0e4b44cce8dd2619d2236c3bf
首先利用笛卡尔积制作一个表来存储连续两天都购物的人uid和其时间
with tb(uid,day1,day2) as (
select distinct * from(
select a.user_id,a.sales_date day1,b.sales_date day2
from (select user_id,sales_date
from sales_tb) a ,(select user_id,sales_date
from sales_tb)b
where a.sales_date+1 = b.sales_date and a.user_id = b.user_id) c)
其次将时间合并成一个列，利用count函数根据uid进行分组，计数不重复的时间即可
select uid,count(distinct da)
from(
select uid,day1 da
from tb
union all
select uid,day2 da
from tb )d
group by uid
order by uid
