Using SQL Query to Find Details of Customers Who Ordered > X Types of Products

Using SQL query to find details of customers who ordered x types of products

Isn't that what you are looking for? Seems to be a little bit simpler. Tested it on SQL Server - works fine.

SELECT customer_name, COUNT(DISTINCT product_ID) as products_count FROM customer_table
INNER JOIN orders_table ON customer_table.customer_ID = orders_table.customer_ID
GROUP BY customer_table.customer_ID, customer_name
HAVING COUNT(DISTINCT product_ID) > 10

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'
)

Using SQL to find customers who ordered first time in July

You could try:

SELECT COUNT(DISTINCT user_id)
FROM yourTable t1
WHERE processing_at >= '2021-07-01' AND processing_at < '2021-08-01' AND
      NOT EXISTS (SELECT 1 FROM yourTable t2
                  WHERE t2.user_id = t1.user_id AND
                        t2.processing_at < '2021-07-01');

The above query counts the number of distinct users who placed any order in the month of July, 2021. The condition that this July order be the first order is enforced by exists logic, which asserts that we cannot find any order for that user in a month earlier than July.

Edit:

Regarding performance, the above query might benefit from the following compound index:

CREATE INDEX idx ON yourTable (processing_at, user_id);

This index, if used, would cover both WHERE clauses, as well as the outer SELECT clause.

SQL to identify customers who placed more than X orders in a given year

You need 2 levels of aggregation:

select c.customer_id
from (
  select customer_id
  from cust_orders 
  where year(order_date) in (2016, 2017)
  group by customer_id, year(order_date)
  having count(*) >= 10
) c
group by c.customer_id
having count(*) = 2;

Replace 10 with the number of purchases.

Change 2 to the number of years that you want to search for.

See the demo.

MySQL Query to find customers who have ordered two specific products

I do this type of query in the following way:

SELECT COUNT(DISTINCT t1.userid) AS user_count
  FROM TRANSACTIONS t1
  JOIN TRANSACTIONS t2 USING (userid)
 WHERE t1.product_id = 'prod1' 
   AND t2.product_id = 'prod2';

The GROUP BY solution shown by @najmeddine also produces the answer you want, but it doesn't perform as well on MySQL. MySQL has a hard time optimizing GROUP BY queries.

You should try both queries, analyzing the optimization with EXPLAIN, and also run some tests and time the results given the volume of data in your database.

SQL. Find the customers who bought same brands and at-least 2 products in each brand

Use COUNT() window function to count the number of distinct brands and the number of distinct products of each brand that each customer has bought.

Then filter out the customers who haven't bought both brands and GROUP BY customer with a HAVING clause that filters out the customers who haven't bought at least 2 products of each brand.

Also your join should be an INNER join and not a LEFT join.

select t.customer_id "Customer ID" 
from (
  select s.customer_id,
    count(distinct p.brand_id) over (partition by s.customer_id) brands_counter,
    count(distinct p.product_id) over (partition by s.customer_id, p.brand_id) products_counter
  from sales s inner join product p
  on p.product_id = s.product_id
  where p.brand_name in ('X', 'Y')
) t
where t.brands_counter = 2
group by t.customer_id
having min(t.products_counter) >= 2

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

---

Related Topics