SQL Server 2017：获取CPG购买前最近非CPG购买记录（无CTE）

Got it, let's tackle this problem. The goal is to find, for each customer who bought CPG, their most recent non-CPG purchase before their first CPG order, then count how many distinct customers fall into each of those non-CPG categories.

Since you want to avoid CTEs and use SQL Server 2017 syntax, here are two working solutions:

Solution 1: Using Window Functions #

This approach uses ROW_NUMBER() to rank non-CPG purchases by date (newest first) for each customer, then picks the top-ranked entry.

SELECT 
    others_category,
    COUNT(DISTINCT customer_id) AS count_distinct_customers
FROM (
    SELECT 
        o.customer_id,
        o.category AS others_category,
        -- Rank non-CPG purchases by date (newest first) per customer
        ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.purchase_date DESC) AS purchase_rank
    FROM orders o
    -- Join with each customer's earliest CPG purchase date
    INNER JOIN (
        SELECT 
            customer_id, 
            MIN(purchase_date) AS first_cpg_date
        FROM orders
        WHERE category = 'CPG'
        GROUP BY customer_id
    ) cpg_dates 
        ON o.customer_id = cpg_dates.customer_id
        AND o.purchase_date < cpg_dates.first_cpg_date
        AND o.category != 'CPG'
) ranked_purchases
-- Keep only the most recent non-CPG purchase per customer
WHERE purchase_rank = 1
GROUP BY others_category;

Solution 2: Using Correlated Subquery #

If you prefer avoiding window functions, this version uses a correlated subquery to directly select the latest non-CPG date for each customer.

SELECT 
    o.category AS others_category,
    COUNT(DISTINCT o.customer_id) AS count_distinct_customers
FROM orders o
INNER JOIN (
    SELECT 
        customer_id, 
        MIN(purchase_date) AS first_cpg_date
    FROM orders
    WHERE category = 'CPG'
    GROUP BY customer_id
) cpg_dates 
    ON o.customer_id = cpg_dates.customer_id
    AND o.purchase_date < cpg_dates.first_cpg_date
    AND o.category != 'CPG'
-- Ensure we only select the latest non-CPG purchase for each customer
WHERE o.purchase_date = (
    SELECT MAX(purchase_date)
    FROM orders
    WHERE customer_id = o.customer_id
        AND category != 'CPG'
        AND purchase_date < cpg_dates.first_cpg_date
)
GROUP BY o.category;

Expected Output #

Both queries will return exactly the result you need:

others_category count_distinct_customers
Electronics    1
Books          1

How It Works #

• Step 1: We first calculate each customer's earliest CPG purchase date (this is our cutoff point for non-CPG purchases).
• Step 2: We filter all non-CPG purchases that happened before this cutoff date for each customer.
• Step 3: We isolate the most recent non-CPG purchase per customer (either via ranking or direct max date lookup).
• Step 4: Finally, we group by the non-CPG category and count distinct customers in each group.

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