215 ) query : Customers Purchasing the Same Product in Different Stores in Same Month

 /* 

### Step 1: DDL & DML (Sample Data Setup)

```sql
-- 1. Create Orders Table */
DROP TABLE customerorders;

CREATE TABLE customerorders
  (
     orderid       INT,
     customerid    INT,
     productid     INT,
     storelocation VARCHAR(50),
     orderdate     DATE
  );

-- 2. Insert Sample Data
INSERT INTO customerorders
VALUES      (1,
             101,
             501,
             'Store_A',
             '2026-01-01'),
            (2,
             101,
             501,
             'Store_B',
             '2026-01-05'), -- Match: Customer 101, Product 501 in Store A and B
            (3,
             102,
             502,
             'Store_A',
             '2026-01-02'),
            (4,
             102,
             502,
             'Store_A',
             '2026-01-06'), -- No match: Same store twice
            (5,
             103,
             503,
             'Store_X',
             '2026-01-03'),
            (6,
             103,
             503,
             'Store_Y',
             '2026-01-04');

------------------------------------------------------------ 

SELECT *
FROM   customerorders;

------------------------------------------------------------ 
/* 

### Step 2: SSMS Query Solution

#### Option A: Using `GROUP BY` and `HAVING` (Recommended & Cleanest)

SQL Server natively evaluates distinct aggregate counts efficiently:

```sql */
SELECT customerid,
       productid,
       Count(DISTINCT storelocation) AS DistinctStoreCount
FROM   customerorders
GROUP  BY customerid,
          productid
HAVING Count(DISTINCT storelocation) > 1;

------------------------------------------------------------ 


/*

#### Option B: Using a Window Function (CTE)

If your curriculum or requirement specifically mandates using `PARTITION BY` window syntax:

```sql */

------------------------------------------------------------ 

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;

------------------------------------------------------------ 
-- 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; 

------------------------------------------------------------