PostgreSQL中如何统计第二次及以上发生的购买事件？

我有一份用户订阅行为数据集，需要统计第二次及以上「Purchase」操作的用户，且仅统计第二次及以上购买发生当日的该次购买。

示例数据 #

期望结果 #

我当前使用的查询语句没有排除首次购买，请求修正：

select
    created_ts::date as date_day
    , count(distinct user_id) as reactivations
from user_subscription_history
where user_id in
    (select 
         user_id 
     from user_subscription_history where notification_type = 'Purchase'
     group by user_id
     having count(*) > 1)
group by 1

核心思路是给每个用户的购买记录按时间标记顺序，排除首次购买后再统计。可以用窗口函数ROW_NUMBER()实现：

WITH user_purchase_ranks AS (
    SELECT
        user_id,
        created_ts::date AS date_day,
        -- 按用户分组、创建时间排序，标记每笔购买的顺序
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_ts) AS purchase_rank
    FROM user_subscription_history
    -- 这里匹配示例中的action字段，若实际表中是notification_type，替换成WHERE notification_type = 'Purchase'
    WHERE action = 'purchase'
)
SELECT
    user_id,
    date_day AS created_ts,
    COUNT(*) AS count
FROM user_purchase_ranks
-- 筛选出第二次及以上的购买记录
WHERE purchase_rank > 1
GROUP BY user_id, date_day;

关键说明 #

1. CTEuser_purchase_ranks会给每个用户的购买记录按时间排序，purchase_rank=1对应首次购买，>1就是第二次及以上的购买；
2. 后续筛选出序号大于1的记录，按用户和日期分组统计，就能得到符合需求的结果。

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