SELECT state, COUNT(*) FROM customers GROUP BY state HAVING COUNT(*)>=1000 ORDER BY state;
This returns the count of customer records, by state, having a customer record count >=1,000. This does not consider whether a customer has purchased or not.
Should the solution not be:
select c.state, count(distinct c.customer_id) from customers c inner join sales s on c.customer_id =s.customer_id group by c.state having count(distinct c.customer_id)>=1000 order by c.state;
To ensure we are only counting, at State level, distinct customers that have purchased a product.
The question requests the customer count for states that have at least 1,000 customers who have purchased from ZoomZoom.
The solution provided ([https://github.com/TrainingByPackt/SQL-for-Data-Analytics/blob/master/Lesson04/Exercise15/Exercise15.sql] :
SELECT state, COUNT(*) FROM customers GROUP BY state HAVING COUNT(*)>=1000 ORDER BY state;
This returns the count of customer records, by state, having a customer record count >=1,000. This does not consider whether a customer has purchased or not.
Should the solution not be:
select c.state, count(distinct c.customer_id) from customers c inner join sales s on c.customer_id =s.customer_id group by c.state having count(distinct c.customer_id)>=1000 order by c.state;
To ensure we are only counting, at State level, distinct customers that have purchased a product.