Redshift中如何通过SQL实现每日累计唯一用户计数？

需求 #

按日期统计累计唯一用户数：2020-02-20至2020-02-25无新增唯一user_id，累计数保持5；2020-02-27出现新用户6，累计数变为6。

输入数据 #

date                user_id
2020-02-20          1
2020-02-20          2
2020-02-20          3
2020-02-20          4
2020-02-20          4
2020-02-20          5
2020-02-21          1
2020-02-22          2
2020-02-23          3
2020-02-24          4
2020-02-25          4
2020-02-27          6

期望输出 #

date            daily_cumulative_count
2020-02-20              5
2020-02-21              5
2020-02-22              5
2020-02-23              5
2020-02-24              5
2020-02-25              5
2020-02-27              6

尝试的SQL及错误结果 #

尝试的SQL：

select
stat_date,count(DISTINCT user_id),
sum(count(DISTINCT user_id)) over (order by stat_date rows unbounded preceding) as cumulative_signups
from data_engineer_interview
group by stat_date
order by stat_date

得到的错误结果：

date,count,cumulative_sum
2022-02-20,5,5
2022-02-21,1,6
2022-02-22,1,7
2022-02-23,1,8
2022-02-24,1,9
2022-02-25,1,10
2022-02-27,1,11

错误原因：该SQL是将每日去重后的用户数累加，重复计算了老用户，无法得到截止到当前日期的唯一用户总数。

正确SQL实现 #

方法一：基于用户首次出现日期统计 #

-- 先获取每个用户的首次出现日期
WITH user_first_date AS (
    SELECT 
        user_id,
        MIN(date) AS first_seen_date
    FROM data_engineer_interview
    GROUP BY user_id
),
-- 获取所有存在记录的日期
date_list AS (
    SELECT DISTINCT date FROM data_engineer_interview
)
SELECT 
    dl.date,
    -- 统计截止到当前日期，首次出现的用户总数
    COUNT(ufd.user_id) OVER (ORDER BY dl.date) AS daily_cumulative_count
FROM date_list dl
LEFT JOIN user_first_date ufd ON dl.date >= ufd.first_seen_date
ORDER BY dl.date;

方法二：标记首次出现记录后累加 #

-- 标记每个用户的首次出现记录
WITH user_first_occurrence AS (
    SELECT 
        date,
        user_id,
        -- 仅标记用户第一次出现的记录为1，其余为0
        CASE WHEN ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) = 1 THEN 1 ELSE 0 END AS is_first
    FROM data_engineer_interview
)
SELECT 
    date,
    -- 累加截止到当前日期的首次出现记录数，即累计唯一用户数
    SUM(SUM(is_first)) OVER (ORDER BY date) AS daily_cumulative_count
FROM user_first_occurrence
GROUP BY date
ORDER BY date;

两种方法都能得到符合预期的输出，核心逻辑是只统计每个用户的首次出现，再累计这些首次出现的数量，避免重复计算老用户。

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