Paul's questions

我一直在尝试通过计算给定数据库表中用户的特定属性来为我们的应用程序创建功能分析，并通过 SQL CTE 将其存储在分析视图中

一旦我向数据模型添加两个以上的 CTE，最终输出就会与原始正确输出不同。

例如当我开始跑步时：

WITH USER_INFO AS 
(
    SELECT
        ID AS ACCOUNT_ID,
        STATUS,
        CREATED_AT AS REGISTRATION_DATE
    FROM
        LOOPCARE_SOURCE_DATABASE.LOM_DB_US_PUBLIC.ACCOUNT
    WHERE
        STATUS IS NULL
),
WEIGHT_LOG AS 
(
    SELECT
        wl.ID AS WEIGHT_LOG_ID,
        wl.ACCOUNT_ID,
        wl.WEIGHT,
        wl.CREATED_AT
    FROM
        LOOPCARE_SOURCE_DATABASE.LOM_DB_US_PUBLIC.WEIGHT_LOG wl
    LEFT JOIN
        USER_INFO ui ON wl.ACCOUNT_ID = ui.ACCOUNT_ID
    WHERE
        wl.CREATED_AT > ui.REGISTRATION_DATE
)
SELECT 
    ui.ACCOUNT_ID,
    COUNT(wl.WEIGHT_LOG_ID) AS WEIGHT_LOG_COUNT,
FROM
    USER_INFO ui
LEFT JOIN
    WEIGHT_LOG wl ON ui.ACCOUNT_ID = wl.ACCOUNT_ID
GROUP BY
    ui.ACCOUNT_ID
ORDER BY
    ui.ACCOUNT_ID;

我获得了数据库表中每个用户的正确行数weight_log减去入职过程中生成的条目（注册日期）。

Pre_Join 表视图

Account_ID 879 = 4 
Account_ID 881 = 1 

现在，当我尝试在最终的选择语句中添加另一列Reflection_Count（该列表示用户已完成的不同反射的总数）时，我的体重日志用户的数据发生了变化并且是错误的。

WITH USER_INFO AS 
(
    SELECT
        ID AS ACCOUNT_ID,
        STATUS,
        CREATED_AT AS REGISTRATION_DATE
    FROM
        LOOPCARE_SOURCE_DATABASE.LOM_DB_US_PUBLIC.ACCOUNT
    WHERE
        STATUS IS NULL
),
WEIGHT_LOG AS 
(
    SELECT
        wl.ID AS WEIGHT_LOG_ID,
        wl.ACCOUNT_ID,
        wl.WEIGHT,
        wl.CREATED_AT
    FROM
        LOOPCARE_SOURCE_DATABASE.LOM_DB_US_PUBLIC.WEIGHT_LOG wl
    LEFT JOIN
        USER_INFO ui ON wl.ACCOUNT_ID = ui.ACCOUNT_ID
    WHERE
        wl.CREATED_AT > ui.REGISTRATION_DATE
),
REFLECTIONS AS 
(
    WITH REFLECTION_PROGRESS_LOG AS 
    (
        SELECT
            ACCOUNT_ID,
            ID AS REFLECTION_PROGRESS_ID,
            EXTERNAL_REFLECTION_ID,
            CREATED_AT
        FROM
            LOOPCARE_SOURCE_DATABASE.LOM_DB_US_PUBLIC.REFLECTION_PROGRESS
        ORDER BY
            ACCOUNT_ID
    ),
    REFLECTION_DETAILS AS 
    (
        SELECT 
            ID AS REFLECTION_ID,
            EXTERNAL_ID,
            TITLE AS REFLECTION_TITLE,
            LANGUAGE
        FROM 
            LOOPCARE_SOURCE_DATABASE.LOM_DB_US_PUBLIC.REFLECTION
        WHERE
            LANGUAGE = 'en'
    )
    SELECT
        rpl.ACCOUNT_ID,
        rpl.REFLECTION_PROGRESS_ID,
        rpl.EXTERNAL_REFLECTION_ID,
        r.REFLECTION_TITLE,
        rpl.CREATED_AT
    FROM
        REFLECTION_PROGRESS_LOG rpl
    LEFT JOIN
        REFLECTION_DETAILS r ON rpl.EXTERNAL_REFLECTION_ID = r.EXTERNAL_ID
    ORDER BY 
        ACCOUNT_ID,
        REFLECTION_PROGRESS_ID
)
SELECT 
    ui.ACCOUNT_ID,
    COUNT(wl.WEIGHT_LOG_ID) AS WEIGHT_LOG_COUNT,
    COUNT(DISTINCT r.EXTERNAL_REFLECTION_ID) AS REFLECTION_COUNT
FROM
    USER_INFO ui
LEFT JOIN
    WEIGHT_LOG wl ON ui.ACCOUNT_ID = wl.ACCOUNT_ID
LEFT JOIN
    REFLECTIONS r ON ui.ACCOUNT_ID = r.ACCOUNT_ID
GROUP BY
    ui.ACCOUNT_ID
ORDER BY
    ui.ACCOUNT_ID;

Post_Join 表视图

Account_ID 879 = 12 
Account_ID 881 = 1 

我似乎找不到问题所在，希望得到任何见解。

谢谢