如何找到购买过特定产品后又购买其他特定产品的用户

Question

我需要看看在2021年和2022年，有多少用户首先从第一个列表中购买了一些产品，然后从第二个列表中购买了一些产品。

第一个列表有product_id:1000000110000002100000031000000410000005第二个列表有product_id:20000001200000022000000320000004

我有一个事务表，我已经写了一个查询，但它没有返回适当的结果。条件是首先从第一个列表购买，然后从第二个列表购买(不一定是另一次购买)。有多少这样的用户和事务?有人能帮我吗?

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)
Table:
Transaction_id   |Customer_id | Date        |product_id
1045411          | 1554411    |2022-01-05   |10000032770333
57486997         | 1554411    |2021-04-30   |20000005
66893928         | 1554411    |2021-04-28   |10000043477221
76300859         | 1554411    |2021-04-26   |10000001
10452342         | 1445444    |2022-01-06   |10000069125012
19859273         | 1445444    |2022-01-07   |10000004
29266204         | 1445444    |2022-01-08   |20000004
38673135         | 1118543    |2021-05-04   |10000043477001
48080066         | 1009576    |2021-05-02   |10000043285004
85707790         | 573708     |2022-05-04   |10000043285004
95114721         | 464741     |2022-07-08   |10000043480001
38633135         | 355774     |2022-09-11   |10000043285004
11228583         | 246807     |2022-11-15   |10000043480001
expected output:
Date    |   SUM_Customer_id
2021   |   1
2022   |   1

Answer 1

;WITH CTE_CustomerList(customer_id) AS
(
SELECT DISTINCT customer_id
FROM dbo.transactions 
WHERE product_id IN (10000001,
10000002,
10000003,
10000004,
10000005)
AND [date] BETWEEN '2021-01-01' AND '2021-12-31'
INTERSECT
SELECT DISTINCT customer_id
FROM dbo.transactions 
WHERE product_id IN (   20000005,
20000002,
20000003,
20000004)
AND [date] BETWEEN '2022-01-01' AND '2022-12-31'
)
SELECT YEAR(tr.[date]) AS YEAR,
count(distinct tr.customer_id) AS Customers,
count(distinct tr.Transaction_id) AS Transactions  
FROM dbo.transactions tr
JOIN CTE_CustomerList cl
ON cl.customer_id = tr.customer_id
WHERE product_id IN (
10000001,
10000002,
10000003,
10000004,
10000005,
20000005,
20000002,
20000003,
20000004
)
AND [date] BETWEEN '2021-01-01' AND '2022-12-31'
GROUP BY YEAR(tr.[date])