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

# 一个用户在一天可能会有多个购买记录，需要去重
with t1 as (
    select DISTINCT user_id, sales_date
    from sales_tb
),
t2 as (
    select user_id, sales_date,
    (case when LAG(sales_date, 1, 1) over(partition by user_id order by sales_date) = 1 then 1 else timestampdiff(day, LAG(sales_date) over(partition by user_id order by sales_date), sales_date) end) as grp
    from t1
)
select user_id, count(sales_date) as days_count
from t2
where grp = 1
group by user_id
having days_count >= 2;