技术需求：统计自用户首次交易月至今每月至少1笔交易的用户数

需求：统计从用户首次交易月份到当前月份，每个月至少有1笔交易的用户总数。

解决思路 #

1. 确定每个用户的首次交易月份，以及从该月份到当前月份的总月份数；
2. 统计每个用户在这段时间内有交易记录的唯一月份数；
3. 筛选出「有交易的月份数 = 总月份数」的用户，最终统计这类用户的数量。

示例数据 #

User_ID Transaction_date 
1       2022-07-23 
1       2022-09-23
1       2022-08-23   

SQL实现（适用于SQL Server） #

WITH UserFirstMonth AS (
    -- 获取每个用户的首次交易月份
    SELECT 
        User_ID,
        DATEFROMPARTS(YEAR(MIN(Transaction_date)), MONTH(MIN(Transaction_date)), 1) AS First_Transaction_Month
    FROM Transactions
    GROUP BY User_ID
),
UserMonthCount AS (
    -- 统计每个用户有交易的月份数，以及从首次交易到当前的总月份数
    SELECT 
        uf.User_ID,
        -- 计算有交易的唯一月份数
        COUNT(DISTINCT DATEFROMPARTS(YEAR(t.Transaction_date), MONTH(t.Transaction_date), 1)) AS Transaction_Month_Count,
        -- 计算从首次交易月到当前月的总月份数
        DATEDIFF(MONTH, uf.First_Transaction_Month, GETDATE()) + 1 AS Total_Month_Count
    FROM UserFirstMonth uf
    JOIN Transactions t ON uf.User_ID = t.User_ID
    -- 只统计首次交易月及之后的记录
    WHERE t.Transaction_date >= uf.First_Transaction_Month
    GROUP BY uf.User_ID, uf.First_Transaction_Month
)
-- 筛选出每个月都有交易的用户，统计数量
SELECT COUNT(User_ID) AS Qualified_User_Count
FROM UserMonthCount
WHERE Transaction_Month_Count = Total_Month_Count;

代码说明 #

• UserFirstMonth CTE：通过MIN(Transaction_date)提取每个用户的首次交易日期，再转换为当月第一天作为首次交易月份；
• UserMonthCount CTE：关联交易表，统计用户在首次交易后有交易的唯一月份数，同时用DATEDIFF计算从首次交易月到当前月的总月份（+1是因为包含首尾两个月份）；
• 最后筛选出交易月份数等于总月份数的用户，统计总数。

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