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
Getting Student Name With Highest Total Mark in SQL
Select to Get Rows Based on Minimum Value of a Column
How to in a Query Calculate the Sum of the Union
Localhost/Phpmyadmin Giving Page Not Found Error
Mysql - Get All Records That Have More Than 1 Record for the Same Id
Sql Get Parent Where Children Have Specific Values
Oracle Sql, Concatenate Multiple Columns + Add Text
Select and Compare Two Datetime Columns from Different Table Without Having Any Relation
Retrieve String Between HTML Tags Using Regex
Mysql - Trigger for Updating Same Table After Insert
Wamp Server Error [Local Server - 2 of 3 Services Running]
Sql: Select All Rows If Parameter Is Null, Else Only Select Matching Rows
Retrieve Varbinary Value as Base64 in Mssql
Disable Secure Priv for Data Loading on MySQL
Er_Access_Denied_Error: Access Denied for User ''@'Localhost' (Using Password: No)
Select Only Rows With Max Date
Query City Names Starting and Ending With Vowels
How to Parser a SQL Query to Pull Out the Column Names and Table Names