Snowflake SQL周粒度嵌套计数：统计会话用户中计费用户数

现有两张业务表：

• 用户会话表：按日追踪用户会话记录，表结构及示例数据如下：

SESSION_DATE    USER_ID
1/1/2022         1
1/1/2022         2
1/1/2022         3
1/1/2022         4
1/2/2022         5

• 用户计费表：按日追踪产生计费记录的用户ID，表结构及示例数据如下：

BILLED_DATE USER_ID
1/1/2022    1
1/1/2022    2
1/1/2022    3
1/2/2022    4
1/2/2022    5

按周粒度（使用DATE_TRUNC截断到week级别）聚合数据，统计两个指标：

• 每周产生会话记录的去重用户总数
• 上述周会话用户群体中，当周同时存在计费记录的去重用户数

示例：某周某维度会话去重用户规模为10万时，需统计这10万用户中当周产生计费记录的用户数。

WEEK        WEEKLY_COUNT_OF_USER_SESSIONS   WEEKLY_BILLED_USERS
1/1/2022    100k                            40K
1/8/2022    110k                            50k
1/15/2022   105k                            45K
1/22/2022   120k                            56K

当前通过嵌套CTE+条件计数实现需求，要求WEEKLY_BILLED_USERS数值小于等于WEEKLY_COUNT_OF_USER_SESSIONS，但编写的SQL存在两个异常：

• 无法准确统计周维度去重USER_ID数量
• WEEKLY_BILLED_USERS字段返回结果恒为0

待排查SQL代码如下：

WITH USER_SESSIONS_CTE AS (
    SELECT DATE_TRUNC(week, EVENT_DATE)::DATE AS WEEK,
                USER_ID,
                'Active_User_Session' AS STATUS_1
    FROM USER_SESSIONS
 
    ),
        BILLED_USERS_CTE AS (
            SELECT U.USER_ID,
                   DATE_TRUNC(week,DATE)::DATE AS WEEK,
                   'BILLED_USER' AS STATUS_2,
                   S.STATUS_1
                FROM BILLED_USERS U
            FULL JOIN USER_SESSIONS_CTE S
                ON S.USER_ID = U.USER_ID AND S.WEEK = DATE_TRUNC(week, U.DATE)::DATE
        )
    
    SELECT Week,
           COUNT(DISTINCT CASE WHEN STATUS_1 = 'Active_User_Session' THEN USER_ID END) AS WEEKLY_COUNT_OF_USER_SESSIONS,
           COUNT(DISTINCT CASE WHEN STATUS_2 = 'BILLED_USER' THEN USER_ID END) AS WEEKLY_BILLED_USERS
        FROM BILLED_USERS_CTE
GROUP BY 1
ORDER BY 1 DESC

错误点 #

1. 字段引用错误：用户会话表的日期字段为SESSION_DATE，CTE中错误引用了不存在的EVENT_DATE，直接导致会话数据读取异常。
2. 关联逻辑错误：使用FULL JOIN会将仅产生计费、无会话记录的用户纳入统计范围，不符合“仅统计周会话用户范围内计费用户”的要求；关联时重复执行周截断逻辑，容易出现日期边界不一致的问题。
3. 计数逻辑错误：原计费用户计数未限定统计范围，加上FULL JOIN产生的空值问题，最终导致计数结果恒为0。

修正后SQL #

WITH USER_SESSIONS_CTE AS (
    SELECT DISTINCT
           DATE_TRUNC(week, SESSION_DATE)::DATE AS WEEK,
           USER_ID
    FROM USER_SESSIONS
),
BILLED_USERS_CTE AS (
    SELECT DISTINCT
           DATE_TRUNC(week, BILLED_DATE)::DATE AS WEEK,
           USER_ID
    FROM BILLED_USERS
)
SELECT 
    s.WEEK,
    COUNT(DISTINCT s.USER_ID) AS WEEKLY_COUNT_OF_USER_SESSIONS,
    COUNT(DISTINCT b.USER_ID) AS WEEKLY_BILLED_USERS
FROM USER_SESSIONS_CTE s
LEFT JOIN BILLED_USERS_CTE b
    ON s.WEEK = b.WEEK AND s.USER_ID = b.USER_ID
GROUP BY s.WEEK
ORDER BY s.WEEK ASC
;

逻辑说明 #

• 两个CTE分别提前完成周维度日期截断、同周同用户去重，避免重复数据干扰计数，同时统一字段引用，从根源解决字段不存在的报错。
• 以周会话用户CTE为主表左连接周计费用户CTE，保证统计范围始终限定为当周有会话的用户，不会混入无会话的计费用户。
• 关联条件同时匹配周维度日期和用户ID，直接统计能关联到计费记录的用户数，即为会话用户中当周产生计费的人数，天然满足WEEKLY_BILLED_USERS <= WEEKLY_COUNT_OF_USER_SESSIONS的要求。
• 基于提供的示例数据验证：起始于1/1/2022的周内，会话去重用户共5人，5人均在同周产生计费记录，结果符合数据逻辑。

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