Sql Server How to Find the Customers Who Have Bought a Product from Each Store

SQL Query to select customers who have already purchased all the items from their Wishlist

yes customer_id, product_id is defined as unique in both the tables – sfmc_newbie

SELECT Customer_ID
FROM Wishlist
LEFT JOIN Customer_Orders USING (Customer_ID, Product_ID)
GROUP BY Customer_ID
HAVING !SUM(Customer_Orders.Product_ID IS NULL)

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=352142a37fd1aaddcc6d3c20ef886d30

Select Customers who purchased one specific Product

You can try the query below

SELECT CustomerName 
FROM dbo.Customers c
WHERE EXISTS (
    SELECT 1
    FROM dbo.Products
    WHERE CustomerId = c.Id
        AND ProductName = 'Milk'
) AND NOT EXISTS (
    SELECT 1
    FROM dbo.Products
    WHERE CustomerId = c.Id
        AND ProductName = 'Bread'
)

SQL Query to FILTER customers who have purchased same product multiple times with a quantity greater than 5

One option uses a subquery:

select t.*
from mytable t
where (
    select count(*) 
    from mytable t1 
    where 
        t1.customer_id = t.customer_id 
        and t1.product_id = t.product_id 
        and t1.quantity > 5
) > 1

The idea is to count how many rows in the table have the same customer and product, and a quantity greater than 5.

You can also use window functions:

select *
from (
    select t.*,
        sum(case when quantity > 5 then 1 else 0 end) over(partition by customer_id, product_id) as cnt
    from mytable t
) t
where cnt > 1

Find the customers who bought ProductA in any month and then bought ProductB in the immediate next month

You may achieve this by using cross apply.

select p.* from ProductSale as p 
cross apply ( 
    select * from ProductSale as ps 
    where p.cust=ps.cust 
    and p.month+1=ps.month 
    and ps.product = 'drink' 
    and p.product='pizza' ) as pg

Find a Customer that buys only a specific product & did not buy any other product (MySQL)

One method is aggregation and having:

select customerid
from t
group by customerid
having sum(product = 'Mobile') > 0 and
       sum(product <> 'Mobile') = 0

Find Customers Who Shop at Multiple Stores

If you want customers at multiple stores, then something like:

SELECT CUSTOMER_ID
FROM SALES INNER JOIN
     STORE_DETAILS
     ON trim(STORE_ID) = trim(STORE_ID)
WHERE (CURRENT_DATE - cast(SALE_DATE AS DATE format 'mm/dd/yyyy')) < 1095
GROUP BY 1
HAVING COUNT(DISTINCT STORE_ID) > 1;

I don't understand your date expression, but presumably you know what it is supposed to be doing.

---

Related Topics