166 ) 6) Customers Purchasing the Same Product in Different Stores in Same Month

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

6) Customers Purchasing the Same Product in Different Stores in Same Month
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Question: Write a query to find 

customers who have purchased the same product 

across different physical stores

 within the exact same month and year.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 6.1) FOR Finding orders of same product in different store
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Query:  DDL AND DML for SSMS

------------------------------------------------------------ 
-- to show which prod and which cust purchased more times 
------------------------------------------------------------ 

WITH RankedOrders AS (
    SELECT 
        CustomerID,
        ProductID,
        StoreLocation,
        -- In SSMS, we can partition and flag unique store occurrences
        DENSE_RANK() OVER (PARTITION BY CustomerID, ProductID ORDER BY StoreLocation) as DenseRankNum,
        COUNT(StoreLocation) OVER (PARTITION BY CustomerID, ProductID, StoreLocation) as StoreOccurrenceCount
    FROM CustomerOrders
)


-- to show which prod and which cust purchased more times 


SELECT DISTINCT customerid,
                productid
FROM   rankedorders
WHERE  storeoccurrencecount = 1
 AND (SELECT Count(DISTINCT storelocation)
            FROM   customerorders co
            WHERE  co.customerid = rankedorders.customerid
                   AND co.productid = rankedorders.productid) > 1;


  


 

  

  
CustomerID
  
  

  
ProductID
  
 
 

  

  
101
  
  

  
501
  
 
 

  

  
103
  
  

  
503
  
 
 

  

  
104
  
  

  
504
  
 
 

  

  
106
  
  

  
506
  
 
 

  

  
108
  
  

  
509
  
 
 

  

  
110
  
  

  
511
  
 
 

  

  
111
  
  

  
512
  
 
 

  

  
112
  
  

  
514
  
 
 

  

  
114
  
  

  
516
  
 


 
------------------------------------------------------------ 
-- to show which prod and which cust purchased how many times
------------------------------------------------------------ 

SELECT customerid,
       productid,
       Count(DISTINCT storelocation) AS DistinctStoreCount,
       Count(orderid)                AS TotalPurchaseCount
FROM   customerorders
GROUP  BY customerid,
          productid
HAVING Count(DISTINCT storelocation) > 1; 





 

  

  
CustomerID
  
  

  
ProductID
  
  

  
DistinctStoreCount
  
  

  
TotalPurchaseCount
  
 
 

  

  
101
  
  

  
501
  
  

  
3
  
  

  
3
  
 
 

  

  
103
  
  

  
503
  
  

  
2
  
  

  
2
  
 
 

  

  
104
  
  

  
504
  
  

  
2
  
  

  
2
  
 
 

  

  
106
  
  

  
506
  
  

  
3
  
  

  
3
  
 
 

  

  
108
  
  

  
509
  
  

  
2
  
  

  
2
  
 
 

  

  
110
  
  

  
511
  
  

  
2
  
  

  
2
  
 
 

  

  
111
  
  

  
512
  
  

  
2
  
  

  
2
  
 
 

  

  
112
  
  

  
514
  
  

  
4
  
  

  
4
  
 
 

  

  
114
  
  

  
516
  
  

  
2
  
  

  
2