SQL Server中按创建日期统计重复联系人记录数（不含原记录）

需求说明 #

统计每个创建日期（created_on）下的重复记录数，这里的「重复记录」指：该记录的email已在更早日期出现过（当前记录并非该email的首次出现），统计时需排除每个email的原始实例（首次出现的记录）。

实现SQL语句 #

-- 先获取每个邮箱的首次创建日期
WITH EmailFirstOccurrence AS (
    SELECT 
        email,
        MIN(created_on) AS first_created_on
    FROM contacts
    GROUP BY email
),
-- 标记每条记录是否为重复项
DuplicateRecords AS (
    SELECT 
        c.created_on,
        CASE WHEN c.created_on > e.first_created_on THEN 1 ELSE 0 END AS is_duplicate
    FROM contacts c
    JOIN EmailFirstOccurrence e ON c.email = e.email
)
-- 按日期分组统计重复数，确保所有存在的日期都被返回
SELECT 
    d.created_on,
    COALESCE(SUM(dr.is_duplicate), 0) AS dupe_count
FROM (SELECT DISTINCT created_on FROM contacts) d
LEFT JOIN DuplicateRecords dr ON d.created_on = dr.created_on
GROUP BY d.created_on
ORDER BY d.created_on DESC;

错误信息翻译 #

原英文错误内容：

Msg 8120, Level 16, State 1, Line 5
Column 'contacts.created_on' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

翻译为中文：

消息 8120，级别 16，状态 1，第 5 行
列 'contacts.created_on' 在选择列表中无效，因为它既未包含在聚合函数中，也未包含在 GROUP BY 子句中。

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