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?


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
      FactoryID, CustomerID,  
      min(RelatedRecordID) as MinRelatedRecordID
  group by 
      FactoryID, CustomerID, ItemID
    FactoryID, CustomerID, 
    count(distinct MinRelatedRecordID)-1 as OrdersWithNewItems 
group by 
    FactoryID, CustomerID 
    count(distinct MinRelatedRecordID) > 3 ;

Tested in SQLfiddle.com

| improve this answer | |

I'm not sure in terms of performance, IMHO it requires at least an index on ([FactoryID], [CustomerID], [RelatedRecordID])

First I've filtered those customers that have 3 or more orders, then I've numbered different lines of each order ([RelatedRecordID]) just to avoid first item on final result, and I've counted how many times the item has been purchased.

Finally it returns those items that has been purchased once.

    -- customers that have 3 or more orders
    SELECT FactoryID, CustomerID, RelatedRecordID, count(*) AS NumOrders
    FROM   Table1
    GROUP BY FactoryID, CustomerID, RelatedRecordID
    HAVING count(*) >= 3
, Items AS
    -- item that appears once and not on the first line
    SELECT t1.[RecordNo], t1.[FactoryID], t1.[CustomerID], t1.[ItemID], t1.[RelatedRecordID],
           ROW_NUMBER() OVER (PARTITION BY t1.[FactoryID], t1.[CustomerID], t1.[RelatedRecordID] 
                        ORDER BY t1.[FactoryID], t1.[CustomerID], t1.[RelatedRecordID], t1.[RecordNo]) AS RNOrder,
           COUNT(ItemID) OVER (PARTITION BY t1.[FactoryID], t1.[CustomerID], t1.[ItemID]) CItems
    FROM       Table1 t1
    INNER JOIN NOrders
    ON         NOrders.FactoryID = t1.FactoryID
    AND        NOrders.CustomerID = t1.CustomerID
SELECT [RecordNo], [FactoryID], [CustomerID], [ItemID]
FROM   Items
WHERE  CItems = 1 
AND    RNOrder > 1

RecordNo | FactoryID | CustomerID | ItemID
-------: | --------: | ---------: | -----:
       5 |         1 |          1 |      4

dbfiddle here

| improve this answer | |

just as a thought:

with sub as (
coalesce((select itemid from table1 t_in where t_in.recordno < t.recordno and t_in.itemid=t.itemid),-1) flag
from table1 t)

relatedrecordid, min(flag)
from sub

group by relatedrecordid

All orders flagged with -1 contain new items.

| improve this answer | |
  • How to align the windows for the outer and inner query? We have more than one customer and factory. – Ryan Jun 19 '17 at 23:49
  • t.factory = t_in.factory and t.customer = t_in.customer. It would probably also work with a left join under the same conditions. – rasenkantenstein Jun 20 '17 at 18:00

This query is based on your sample data. Assume RelatedRecordID is the sequence of the purchase.

;WITH cte_OrderCounts AS (SELECT [FactoryID], [CustomerID]
                              , COUNT(DISTINCT [RelatedRecordID]) AS OrderCounts
                          FROM Table1
                          GROUP BY [FactoryID], [CustomerID]
), cte AS (SELECT *
               , DENSE_RANK() OVER (PARTITION BY [FactoryID], [CustomerID] ORDER BY [RelatedRecordID]) AS CustomerOrderRank
               , ROW_NUMBER() OVER (PARTITION BY [FactoryID], [CustomerID], [ItemID] ORDER BY [RelatedRecordID]) AS CustomerPurchaseItem
           FROM Table1
FROM cte a
INNER JOIN cte_OrderCounts b
    ON b.FactoryID = a.FactoryID
    AND b.CustomerID = a.CustomerID
WHERE a.CustomerOrderRank > 1
AND a.CustomerPurchaseItem = 1
AND b.OrderCounts >= 3;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.