SQL Server – Counting Orders with New Items

countsql serversql-server-2008-r2window functions

Scenario:
Find out any customers in each factory that have three or more orders which contain new items they have never purchased before. For example, John has:

Order A: Item 1, Item 2
Order B: Item 2, Item 3, Item 4
Order C: Item 3

Order B has items John has never purchased before, while Order C does not.

To put it another way: Starting with each customer's second order, we want to know how many orders they have with at least one item that's never been on one of their orders before.

Table schema: see SQL Fiddle. RelatedRecordID binds multiple records into a single order.

My approach: see also SQL Fiddle above. This approach is flawed and not robust. In the previous John example, this method will give out 2 increments, but because Order C is placed after B, there is actually only one increment. And if Order B does not contain Item 3, its precedence won't affect the result.

Can anybody think of a fix or a new way to do this?

Best Answer

The CTE first finds the first order than every item was purchased (per customer and factory) and then we simply count the distinct order IDs (and subtract 1 as the very first order doesn't count):

with cta as
(
  select 
      FactoryID, CustomerID,  
      min(RelatedRecordID) as MinRelatedRecordID
  from 
      Table1
  group by 
      FactoryID, CustomerID, ItemID
)
select 
    FactoryID, CustomerID, 
    count(distinct MinRelatedRecordID)-1 as OrdersWithNewItems 
from 
    cta 
group by 
    FactoryID, CustomerID 
having 
    count(distinct MinRelatedRecordID) > 3 ;

Tested in SQLfiddle.com

Related Question