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

-- 连续2天及以上在该店铺购物的用户及其对应的次数
-- 连续购物的天数_连续天数的计算方法
-- 筛选出两天的
select
user_id,
consecutive_days
from(
    SELECT 
    user_id,
    COUNT(group_date) AS consecutive_days
    FROM(
        SELECT 
        user_id,
        sales_date,
        DATE_SUB(sales_date, INTERVAL xuhao DAY) AS group_date
        FROM(
            select
            user_id,
            sales_date,
            row_number()over(partition by user_id order     by      sales_date) as xuhao
            from(
                select
                distinct user_id,
                sales_date
                from sales_tb) as t1) as t2 )as t3
    group by user_id) as t4
where consecutive_days>=2

这个方法实际上比较传统，就是时间扫描法，主要的做法是新建连续不挑次的序号，将序号和时间相减，最后分组统计相同的时间值就可以看到不同连续时长的分布长度。

可以参考下面的模板：

WITH 
-- 第一步：获取每个作者的回答日期（去重）
distinct_dates AS (
    SELECT DISTINCT author_id, answer_date
    FROM answer_tb
),

-- 第二步：为每个作者的回答日期排序并添加序号
ranked_dates AS (
    SELECT 
        author_id,
        answer_date,
        ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY answer_date) AS row_num
    FROM distinct_dates
),

-- 第三步：计算日期减去序号（关键步骤）
date_groups AS (
    SELECT 
        author_id,
        answer_date,
        DATE_SUB(answer_date, INTERVAL row_num DAY) AS group_date
    FROM ranked_dates
),

-- 第四步：统计每个连续分组的长度
consecutive_counts AS (
    SELECT 
        author_id,
        group_date,
        COUNT(*) AS consecutive_days
    FROM date_groups
    GROUP BY author_id, group_date
),

-- 第五步：找出每个作者的最大连续天数
max_consecutive AS (
    SELECT 
        author_id,
        MAX(consecutive_days) AS max_days
    FROM consecutive_counts
    GROUP BY author_id
    HAVING MAX(consecutive_days) >= 3
)

-- 最终结果：关联作者信息表
SELECT 
    m.author_id,
    a.author_level,
    m.max_days AS days_cnt
FROM max_consecutive m
JOIN author_tb a ON m.author_id = a.author_id
ORDER BY m.author_id;