eatsleepcode February 2016

SQL query to compute total number of customers who purchase >4 products on one day

I want to write a SQL query to compute which customers have purchased more than 4 products on the same day.

Here are my tables:

Sales (date, customer_id, product_id, units_sold)

Products (id, name, price)

Customers (id, name)

& here's what I have so far:

SELECT COUNT(s.product_id) as total_customers
FROM Sales s1
WHERE DATEDIFF(s1.date, s2.date)=0
INNER JOIN Sales s2
  ON s1.product_id = s2.product_id
HAVING COUNT(s.product_id) > 4;

Answers


MotoGP February 2016

Too many mistakes's in your query try this

SELECT customer_id,cast(s1.date as date),COUNT(s1.product_id) as total_customers
FROM Sales s1
Group by customer_id,cast(s1.date as date)
HAVING COUNT(s1.product_id) > 4;


Gordon Linoff February 2016

If you want the customers who have purchased more than 4 products on the same date:

SELECT DISTINCT s.customer_id
FROM Sales s
GROUP BY s.customer_id, date(s.date)
HAVING COUNT(*) > 4;

This is one of the few cases where SELECT DISTINCT is used with GROUP BY. If you want to know the dates as well, then include date(s.date) in the SELECT.

Note that this assumes that any given product is purchased by a customer only once on each date. If a customer can have multiple records for a single product on one date, use COUNT(DISTINCT product_id) instead of COUNT(*).

To get the total number of customers, use a subquery:

SELECT COUNT(*)
FROM (SELECT DISTINCT s.customer_id
      FROM Sales s
      GROUP BY s.customer_id, date(s.date)
      HAVING COUNT(*) > 4
     ) c

Post Status

Asked in February 2016
Viewed 3,694 times
Voted 8
Answered 2 times

Search




Leave an answer