Select most recent purchase prior to the latest purchase

Question

I have a table that looks something like this:

customer_id  purchase_date  category
1            9/1/2018       Electronics
1            9/2/2018       Art
1            9/3/2018       Books
1            9/5/2018       CPG
2            9/2/2018       Books
2            9/4/2018       Electronics
2            9/20/2018      CPG     

From here, I am trying to derive the most recent purchase prior to a CPG purchase. To explain further, here are my steps:

Step 1. create a table of purchases not in CPG category:

WITH OTHERS AS(
    SELECT customer_id,
           category as others_category,
           purchase_date
    FROM orders o
    WHERE category IN ('Electronics', 'Books', 'Art')
),

Step 2. Create a table of purchases in CPG category:

CPG AS( 
    SELECT customer_id,
           category as cpg_category,
           purchase_date
    FROM orders o
    WHERE category = 'CPG'
)

Step 3.Left join: This is where I am stuck. I would like to produce a table that has the most recent OTHER purchase prior to a CPG purchase. i.e, the output should look like:

others_category  count_distinct_customers
Electronics            1
Books                  1

Ideally I would like to not use CTE. SQL type is SQL Server 2017.

Answer 1

This is how I would do it in SQL Server 2017, however, I'm not certain if this'll work in 2005 (unfortunately, like i said, I don't have a 2005 test environment anymore). I think APPLY was added in SQL Server 2008. Certainly the "VTE" won't work in 2005, as the VALUES constructor clause was added in 2008 (if I recall correctly), however, you'll have a table to test against at least:

WITH VTE AS(
    SELECT V.customer_id,
           CONVERT(date,V.purchase_date,101) AS purchase_date,
           V.category
    FROM (VALUES(1,'9/1/2018 ','Electronics'),
                (1,'9/2/2018 ','Art'),
                (1,'9/3/2018 ','Books'),
                (1,'9/5/2018 ','CPG'),
                (2,'9/2/2018 ','Books'),
                (2,'9/4/2018 ','Electronics'),
                (2,'9/20/2018','CPG')) V(customer_id,purchase_date,category))
SELECT V2.category,
       COUNT(DISTINCT V2.customer_id) AS DistinctCustomers
FROM VTE V1
     CROSS APPLY (SELECT TOP 1
                         customer_id,
                         purchase_date,
                         category
                  FROM VTE ca 
                  WHERE ca.customer_id = V1.customer_id
                    AND ca.purchase_date < V1.purchase_date
                  ORDER BY ca.purchase_date DESC) V2

WHERE V1.category = 'CPG'
GROUP BY V2.category;