Выберите самую последнюю покупку перед последней покупкой

Именно так я бы сделал это в SQL Server 2017, однако я не уверен, сработает ли это в 2005 году (к сожалению, как я уже сказал, у меня больше нет тестовой среды 2005 года). Я думаю APPLY был добавлен в SQL Server 2008. Конечно, "VTE" не будет работать в 2005 году, поскольку предложение конструктора VALUES было добавлено в 2008 году (если я правильно помню), однако вы иметь таблицу для тестирования по крайней мере:

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;

Перефразируя, вам нужны только покупки, за которыми сразу же следовала «покупка CPG» (тем же клиентом?).

Аналитическая функция LEAD() позволяет вам найти то, что находится в "следующей" строке, без необходимости присоединять данные обратно к себе.

WITH
  orders_with_lookup AS
(
  SELECT
    *,
    LEAD(category) OVER (PARTITION BY customer_id ORDER BY purchase_date)   AS customers_next_purchase_category
  FROM
    orders
)
SELECT
  category,
  COUNT(DISTINCT customer_id)   AS count_distinct_customers
FROM
  orders_with_lookup
WHERE
  customers_next_purchase_category = 'CPG'
GROUP BY
  category
ORDER BY
  category

Попробуй это

;WITH CTE(customer_id , purchase_date , category)
AS
(
SELECT 1,'9/1/2018' ,'Electronics' UNION ALL
SELECT 1,'9/2/2018' ,'Art'         UNION ALL
SELECT 1,'9/3/2018' ,'Books'       UNION ALL
SELECT 1,'9/5/2018' ,'CPG'         UNION ALL
SELECT 2,'9/2/2018' ,'Books'       UNION ALL
SELECT 2,'9/4/2018' ,'Electronics' UNION ALL
SELECT 2,'9/20/2018','CPG'     
)
,CTE2 
AS
(
SELECT customer_id,purchase_date,category, 
       ROW_NUMBER()OVER(PARTITION BY customer_id ORDER BY purchase_date DESC) AS MostRecentPurchase
FROM
(
SELECT customer_id , 
       CAST( purchase_date AS DATE) purchase_date, 
       category
FROM CTE
)dt
)
SELECT Category AS Others_category,
       COUNT(DISTINCT customer_id) AS Count_distinct_customers
FROM  CTE2
WHERE  MostRecentPurchase = 2
GROUP BY category

Результат

Others_category     Count_distinct_customers
-----------------------------------------
Books                   1
Electronics             1

Вы можете использовать OUTER APPLY (доступный с SQL Server 2005) для присоединения каждого заказа CPG к его предыдущей покупке OTHERS, а затем вы можете просто вернуть данные этого заказа OTHERS.

Я добавил DISTINCT, поэтому, если два заказа CPG имеют один и тот же предыдущий заказ OTHERS (потому что между ними не было введено других заказов), он возвращается только один раз.

   SELECT DISTINCT others.*
   FROM orders cpg
        OUTER APPLY (SELECT top 1 others.*
                     FROM orders as others
                     WHERE category <> 'CPG' and
                           others.purchase_date < cpg.purchase_date
                           order by others.purchase_date desc) as others
   WHERE category = 'CPG'

Вот еще один подход ... так много способов снять шкуру с этой кошки.

declare @Something table
(
    customer_id int
    , purchase_date date
    , category varchar(20)
)
insert @Something 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')

, (3, '9/2/2018 ', 'Books') --added customer 3 
, (3, '9/4/2018 ', 'Electronics')
, (3, '9/20/2018', 'CPG')

select category
    , DistinctCustomerCount = count(*)
from
(
    select *
        , RowNum = row_number()over(partition by customer_id, case when category = 'CPG' then 1 else 0 end order by purchase_date desc)
    from @Something
) x
where x.category <> 'CPG'
    and x.RowNum = 1
group by x.category