如何用SQL查询用户首次产生正收入时的留存天数？

示例数据 #

user_id, creation_date, activity_date, net_revenue, retained_days

1, 2019/01/01, 2019/01/01, 0, 0
2, 2019/01/01, 2019/01/01, 0, 0

1, 2019/01/01, 2019/01/02, 0, 1
2, 2019/01/01, 2019/01/02, 0, 1

1, 2019/01/01, 2019/01/03, 0, 2
2, 2019/01/01, 2019/01/03, 0, 2
 
1, 2019/01/01, 2019/01/04, 5.5, 3
2, 2019/01/01, 2019/01/04, 0, 3

1, 2019/01/01, 2019/01/05, 5.5, 4
2, 2019/01/01, 2019/01/05, 3, 4

需求与期望输出 #

需要获取每个用户**首次产生正收入（net_revenue>0）**时对应的retained_days值，示例期望输出：

1, 3
2, 4

尝试过的错误SQL #

写法1 #

SELECT DISTINCT uid, 
CASE
WHEN SUM(net_revenue) >0
THEN SUM(retained_days)
END AS ret_day_fst_purch
FROM table1 
GROUP BY 1

写法2 #

SELECT DISTINCT uid, 
activity_date,
CASE
WHEN SUM(net_revenue) >0
THEN SUM(retained_days)
END AS ret_day_fst_purch
FROM table1 
GROUP BY 1,2

上述写法错误原因：使用SUM()聚合函数会对所有记录的数值求和，而需求是获取单条首次正收入记录的retained_days，并非求和结果。

正确解决方案 #

方案1：使用窗口函数（通用且准确） #

通过窗口函数ROW_NUMBER()对每个用户的正收入记录按日期排序，标记出第一条记录：

SELECT user_id, retained_days
FROM (
    SELECT 
        user_id,
        retained_days,
        -- 按用户分组，按活动日期升序排序，标记第一条记录
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) AS record_rank
    FROM table1
    -- 只筛选正收入的记录
    WHERE net_revenue > 0
) ranked_records
-- 取每个用户的第一条正收入记录
WHERE record_rank = 1;

方案2：利用留存天数递增特性简化查询 #

由于示例中retained_days随activity_date递增，首次正收入对应的retained_days是该用户所有正收入记录中的最小值，因此可以直接聚合取最小值：

SELECT 
    user_id,
    MIN(retained_days) AS ret_day_fst_purch
FROM table1
WHERE net_revenue > 0
GROUP BY user_id;

这两种写法都能得到预期结果，方案1更通用，即使retained_days不严格递增也能准确获取首次正收入记录；方案2仅适用于retained_days与activity_date严格正相关的场景。

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