如何统计2021-2022年先购指定商品再购另一类商品的用户

需求 #

统计2021年至2022年间，先购买第一组商品（product_id：10000001、10000002、10000003、10000004、10000005），之后再购买第二组商品（product_id：20000001、20000002、20000003、20000004）的用户数量及对应交易数。用户自行编写的SQL返回结果不符合预期，需修正查询。

原查询语句 #

SELECT YEAR(date) AS YEAR,
       count(distinct customer_id) AS Customers,
       count(distinct Transaction_id) AS Transactions
FROM dbo.transactions 
WHERE product_id IN (10000001,
                        10000002,
                        10000003,
                        10000004,
                        10000005,
                        20000005,
                        20000002,
                        20000003,
                        20000004),
        AND date >= '2021-01-01'
        AND date = (SELECT min(date)
                          FROM dbo.transactions
                          WHERE product_id IN (
                                               10000001,
                                               10000002,
                                               10000003,
                                               10000004,
                                                10000005))

GROUP BY YEAR(date)

交易表结构及示例数据 #

预期输出 #

Date    |   SUM_Customer_id
     2021   |   1
     2022   |   1

原查询的问题 #

1. 语法错误：WHERE子句中IN括号后多了一个逗号，导致SQL无法正常执行。
2. 逻辑错误：子查询date = (SELECT min(date)...)取的是全表中第一组商品的最早交易日期，而非每个用户首次购买第一组商品的日期，会过滤掉所有非该日期的记录，完全不符合“先买第一组再买第二组”的顺序验证逻辑。
3. 商品组范围错误：第二组商品包含了20000005，但需求中第二组仅包含20000001-20000004。
4. 未验证购买顺序：仅筛选了包含两组商品的记录，但未验证用户是先购买第一组、后购买第二组的时间顺序。

修正后的SQL语句 #

WITH user_first_group AS (
    -- 获取每个用户首次购买第一组商品的日期，限定2021-2022年范围
    SELECT 
        customer_id,
        MIN(date) AS first_group_date
    FROM dbo.transactions
    WHERE 
        product_id IN (10000001, 10000002, 10000003, 10000004, 10000005)
        AND date BETWEEN '2021-01-01' AND '2022-12-31'
    GROUP BY customer_id
),
valid_transactions AS (
    -- 筛选符合条件的用户后续购买第二组商品的交易记录
    SELECT 
        t.customer_id,
        t.transaction_id,
        t.date
    FROM dbo.transactions t
    JOIN user_first_group ufg ON t.customer_id = ufg.customer_id
    WHERE 
        t.product_id IN (20000001, 20000002, 20000003, 20000004)
        AND t.date > ufg.first_group_date
        AND t.date BETWEEN '2021-01-01' AND '2022-12-31'
)
-- 按年份统计用户数和交易数（若仅需用户数，可调整SELECT字段）
SELECT 
    YEAR(date) AS Date,
    COUNT(DISTINCT customer_id) AS SUM_Customer_id
FROM valid_transactions
GROUP BY YEAR(date)
ORDER BY YEAR(date);

说明 #

1. CTE user_first_group：先提取每个用户首次购买第一组商品的日期，限定统计时间范围在2021-2022年。
2. CTE valid_transactions：关联用户首次购买记录，筛选出用户在该日期之后购买第二组商品的交易，确保购买顺序符合需求。
3. 最终统计：按年份分组统计符合条件的用户数量，结果与预期输出完全匹配；若需要交易数，可在SELECT中添加COUNT(DISTINCT transaction_id) AS Transactions字段。

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