按来源每日计算用户平均得分（含未答题用户最新记录）的SQL实现

要实现「每个日期的得分计算包含对应来源下所有用户截止到该日期的最新记录」，核心是先构建所有日期×对应来源下所有用户的完整组合，再为每个组合匹配最新答题记录，最后计算平均值。以下是分步实现的SQL逻辑：

步骤1：生成基础数据集 #

先准备三个基础数据集，为后续逻辑铺路：

• 所有存在答题记录的日期（确保报表覆盖所有有数据的日期）
• 每个来源对应的所有用户（确保每个日期都包含该来源下的全部用户）
• 每个用户在每个来源下的每日最新提交记录（复用你提供的查询逻辑）

WITH 
-- 1. 提取所有唯一的答题日期
all_dates AS (
    SELECT DISTINCT submission_date 
    FROM quiz 
    WHERE id != ''
),
-- 2. 提取每个来源对应的所有唯一用户
source_users AS (
    SELECT DISTINCT source, id 
    FROM quiz 
    WHERE id != ''
),
-- 3. 复用原逻辑，获取每个用户、来源、每日的最新提交记录
latest_daily_submissions AS (
    SELECT
        id,
        score,
        submission_date,
        source
    FROM quiz
    WHERE id != ''
    QUALIFY ROW_NUMBER() OVER(PARTITION BY source, submission_date, id ORDER BY submission_date DESC) = 1
),
-- 4. 生成「日期×来源×用户」的完整组合（确保每个日期每个来源的所有用户都被纳入）
date_source_user_combinations AS (
    SELECT 
        ad.submission_date AS report_date,
        su.source,
        su.id
    FROM all_dates ad
    CROSS JOIN source_users su
)

步骤2：匹配每个组合的最新记录 #

为每个「日期-来源-用户」组合，找到该用户在对应来源下截止到报表日期的最新答题记录：

-- 5. 为每个组合匹配截止到当日的最新得分
user_latest_score_per_date AS (
    SELECT
        dc.report_date,
        dc.source,
        dc.id,
        -- 取截止到报表日期的最新得分，无记录则返回NULL（可根据需求调整）
        MAX(ls.score) KEEP (DENSE_RANK LAST ORDER BY ls.submission_date) AS latest_score
    FROM date_source_user_combinations dc
    LEFT JOIN latest_daily_submissions ls 
        ON dc.source = ls.source 
        AND dc.id = ls.id 
        AND ls.submission_date <= dc.report_date
    GROUP BY dc.report_date, dc.source, dc.id
)

步骤3：计算每日平均得分 #

最后按来源和日期分组，计算平均得分（可根据需求决定是否从未答题的用户）：

-- 最终报表：按来源分组的每日平均得分
SELECT
    report_date,
    source,
    -- 计算平均得分，排除从未答题的用户（若需包含则去掉WHERE条件）
    AVG(latest_score) AS daily_avg_score
FROM user_latest_score_per_date
WHERE latest_score IS NOT NULL
GROUP BY report_date, source
ORDER BY source, report_date;

逻辑说明 #

• 用CROSS JOIN生成所有日期和来源用户的组合，彻底避免遗漏任何用户在任何日期的记录
• 用LEFT JOIN+KEEP (DENSE_RANK LAST ORDER BY submission_date)（Snowflake语法）快速定位最新得分；若使用其他SQL引擎（如BigQuery），可替换为ROW_NUMBER()窗口函数实现相同逻辑
• 最终分组时，可根据业务需求选择是否纳入从未答题的用户

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