PostgreSQL按周统计试用订阅用户数及付费转化用户数SQL实现问询

解决代码 #

你可以通过公用表表达式（CTE）先提取试用用户的周维度数据，再关联付费记录统计转化量，实现代码如下：

WITH trial_user_weekly AS (
    SELECT 
        user_id,
        date_trunc('week', created_at::date) AS trial_week
    FROM subscription
    WHERE sub_type = 'trial'
)
SELECT 
    to_char(trial_week, 'FMMonth DD') AS week,
    COUNT(DISTINCT tu.user_id) AS trial,
    COUNT(DISTINCT s.user_id) AS paid
FROM trial_user_weekly tu
LEFT JOIN subscription s 
    ON tu.user_id = s.user_id 
    AND s.sub_type = 'paid'
    -- 若要求付费必须发生在试用之后，可新增下面的时间过滤条件
    -- AND s.created_at > tu.created_at
GROUP BY trial_week
ORDER BY trial_week;

逻辑说明 #

1. 先通过CTE获取所有领取试用的用户，以及对应领取试用的周维度时间
2. 左关联订阅表中对应用户的付费订阅记录，只要用户有付费记录就会被匹配
3. 统计时使用COUNT(DISTINCT)避免同一用户多次领试用、多次付费导致计数重复
4. 日期格式化函数可根据你使用的数据库调整：MySQL可替换为DATE_FORMAT(trial_week, '%M %e')，SQL Server可替换为DATENAME(MONTH, trial_week) + ' ' + DATENAME(DAY, trial_week)

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