SQL查询连续7天访问同一URL用户占比结果异常排查

待统计的events表结构如下：

需求：计算浮点数格式的用户占比，分子是存在至少1次连续7天访问同一URL行为的独立用户数，分母是全量独立用户数。
原有SQL执行结果不符合预期，无法定位问题。原SQL代码：

with x as (
     select count(user_id) as allusers
     from events
), y as 
( select count(e2.user_id) as users
     from events e
       join events e2
     on e.user_id = e2.user_id
     and e.url = e2.url
     and e2.created_at = DATE_ADD(e.created_at, INTERVAL 6 DAY)
)
select ROUND(users * 1.0 / allusers,2) as precent_of_users
from x,y

• 总用户数统计错误：count(user_id)统计的是总访问记录数，没有做用户去重，同一个用户多次访问会被重复计数。
• 连续访问判定逻辑错误：仅校验了用户在某条记录的6天后有同URL访问记录，完全没有校验中间5天是否存在访问，会把间隔7天各访问1次的用户误判为连续7天访问。
• 符合条件用户统计错误：没有对命中的用户做去重，同一个用户如果有多组间隔6天的访问记录会被重复计数。
• 时间匹配逻辑错误：created_at是带时分秒的datetime类型，直接做等值匹配只有两条记录时分秒完全一致才能关联上，同一天不同时间访问的记录会被漏判。

采用窗口函数做连续日期分组，逻辑准确且易维护：

1. 先对同用户、同URL、同日期的访问记录去重，统一截断为日期格式消除时分秒影响
2. 按用户+URL分组，给访问日期按升序打行号，用「访问日期 - 行号对应的天数」生成分组key：连续日期的分组key完全相同
3. 统计每个分组下的日期条数，条数≥7即代表存在连续7天访问同URL的行为，提取对应的去重用户
4. 最后计算去重后的符合条件用户数和总用户数的比值，保留2位小数

修正后的SQL代码：

WITH daily_unique AS (
    SELECT DISTINCT
        user_id,
        url,
        DATE(created_at) AS visit_date
    FROM events
),
continuous_group AS (
    SELECT
        user_id,
        url,
        visit_date,
        DATE_SUB(
            visit_date,
            INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id, url ORDER BY visit_date) DAY
        ) AS group_tag
    FROM daily_unique
),
valid_users AS (
    SELECT DISTINCT user_id
    FROM continuous_group
    GROUP BY user_id, url, group_tag
    HAVING COUNT(*) >= 7
),
total_stats AS (
    SELECT COUNT(DISTINCT user_id) AS total_user
    FROM events
)
SELECT ROUND(v.valid_cnt * 1.0 / t.total_user, 2) AS percent_of_users
FROM total_stats t
CROSS JOIN (SELECT COUNT(1) AS valid_cnt FROM valid_users) v

注：如果业务对连续访问的判定不是严格自然日（比如允许跨天间隔小于24小时算连续），可以根据实际规则调整日期截断和分组逻辑，上述代码基于自然日连续访问的通用规则实现。

内容的提问来源于stack exchange，提问作者Chris90