使用SQL计算指定日期间日维度未登录用户数的问题

核心逻辑 #

Num_users_no_log_in的计算逻辑为：全量去重用户总数，减去在(last_log_in_date, present_date]时间区间内有登录记录的去重用户数量，同时需满足时间区间不超过回溯90天的限制。

完整实现代码 #

-- 先统计全量去重用户总数
WITH all_users AS (
    SELECT COUNT(DISTINCT User_Id) AS total_user_cnt
    FROM users_table
),
-- 生成去重登录日期列表
dts AS (
    SELECT DISTINCT log_in_dates
    FROM users_table
),
-- 生成符合90天回溯要求的日期对（基准日+最后登录区间起始日）
date_pairs AS (
    SELECT 
        x.log_in_dates AS present_date,
        DATEDIFF(DAY, y.log_in_dates, x.log_in_dates) AS days_difference,
        y.log_in_dates AS last_log_in_date
    FROM dts x
    CROSS JOIN dts y
    WHERE x.log_in_dates >= y.log_in_dates
      AND DATEDIFF(DAY, y.log_in_dates, x.log_in_dates) <= 90
)
-- 关联登录表计算区间未登录用户数
SELECT 
    dp.present_date,
    dp.days_difference,
    dp.last_log_in_date,
    au.total_user_cnt - COUNT(DISTINCT ut.User_Id) AS Num_users_no_log_in
FROM date_pairs dp
CROSS JOIN all_users au
LEFT JOIN users_table ut
    ON ut.log_in_dates > dp.last_log_in_date
    AND ut.log_in_dates <= dp.present_date
GROUP BY dp.present_date, dp.days_difference, dp.last_log_in_date, au.total_user_cnt
ORDER BY dp.present_date, dp.days_difference DESC

样例校验 #

以你提供的样例数据验证：全量用户共6人，基准日为2021-09-02、last_log_in_date为2021-09-01时，区间内有登录记录的用户为2、3共2人，6-2=3，和样例输出结果完全匹配。

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