use tempdb
go
/* 
--uncomment this to create purchase table.. This script to generate the 1 million rows is 
--created by Jeff Moden on sql server central . com

--===== This code takes about 23 seconds to run on a 10 year old,
     -- single CPU desktop machine.

--===== Conditionally drop the test table to make reruns in SSMS easier.
     -- This is NOT a part of the solution.
     IF OBJECT_ID('tempdb..Purchase','U') IS NOT NULL
        DROP TABLE Purchase
;
--===== Create and populate the test table.
     -- This is NOT a part of the solution.
 SELECT TOP (1000000)
        PurchaseID  = IDENTITY(INT,1,1),
        CustomerID  = ABS(CHECKSUM(NEWID())) % 50000 + 1,
        ProductCode = CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)
   INTO Purchase
   FROM      sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
;
--===== Add the expected PK
  ALTER TABLE Purchase
    ADD PRIMARY KEY CLUSTERED (PurchaseID)
;
--===== Can you guess which index will be used?
 CREATE INDEX IX_Purchase_CustomerID_ProductCode
     ON Purchase (CustomerID, ProductCode)
;
 CREATE INDEX IX_Purchase_ProductCode_CustomerID
     ON Purchase (ProductCode, CustomerID)
     
 update statistics Purchase with fullscan
*/     
--- find only the customer ids who bought a,b but not c
with cte1
as
(
select 
CustomerId
,MAX(case when ProductCode = 'A' then 1 else 0 end) +
max(case when ProductCode = 'B' then 2 else 0 end) + 
max(case when ProductCode = 'C' then 4 else 0 end ) as status
from Purchase
group by CustomerID
)
select * from cte1 where status = 3

--Above uses a non clustered index scan and clocked 1867 logical IO and around 800 ms cpu and elapsed time.

--query to get the details of customers along with product code and using join back to the original table..
with cte1
as
(
select 
CustomerId
,MAX(case when ProductCode = 'A' then 1 else 0 end) +
max(case when ProductCode = 'B' then 2 else 0 end) + 
max(case when ProductCode = 'C' then 4 else 0 end ) as status
from Purchase
group by CustomerID
)
select p.CustomerId,p.ProductCode,cte1.status from cte1  inner join purchase p on cte1.CustomerId = p.CustomerId
where status = 3
and p.ProductCode not in ('C'); --as we 

--It clocked 3734 Io's and 1200 ms CPU time and 1500 ms of elapsed time for 137 K rows..This is quite fast


--This is using without join but using parttion by ..This is performaing badly.. 
--Thus do not try the new methods just to make the statement simple..
--Always benchmark with the best method you have with any new method which is introduced in the new versions..
--It is clocking 2.2 million IO's approx...Which is way too high 7533 ms CPU time... and 2853 ms elapsed time for 137 k rows
with cte1
as
(
select 
CustomerID,ProductCode
,
  case when ProductCode = 'A' then 1 else 0 end as IsAExist
, case when ProductCode = 'B' then 2 else 0 end as IsBExist
, case when ProductCode = 'C' then 4 else 0 end as IsCExist
from Purchase
),cte2
as
(
select CustomerID,ProductCode, MAX(IsAExist) over(PARTITION by CustomerID) + MAX(IsBExist)over(PARTITION by CustomerID)  + MAX(IsCExist)over(PARTITION by CustomerID) as status from cte1
)
select * from cte2
where status = 3 
--option(maxdop 1)
; --a and b but not C
go

--

/*
To compare the above...
without using parttition

Logical IO	CPU Time	Elapsed Time
3734		800ms		800ms
2201871		7500ms		2850ms
*/

--SET STATISTICS IO ON
--SET STATISTICS TIME ON

SELECT CustomerID
FROM #Purchase
WHERE ProductCode IN('A','B','C') -- only A,B,C, AB,AC,BC and ABC
GROUP BY CustomerID
HAVING COUNT(DISTINCT ProductCode) = 2 -- only AB,AC and BC
AND MAX(ProductCode) = 'B' -- only AB! :)

/*
(6763 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads   0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Purchase'. Scan count 3, logical reads 226, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 187 ms,  elapsed time = 187 ms.
*/