CODEWITHSUNDEEP
mysqlsqlsubquerydate-arithmetic
Rishab Gupta
Rishab Gupta

Reputation: 581

SQL: Customers who have not made a purchase during the current year

I have these two schemas:Schemas

I want to find the list of customers who have not made a purchase during the current year.

I am stuck at "during the current year" part.

SELECT c.fname, c.lname
FROM customers c
WHERE c.cust_id NOT IN (SELECT s.Cust_id FROM sales s);

Thank you.

Upvotes: 1

Views: 1310

Answers (2)

James Anderbard
James Anderbard

Reputation: 494

SELECT c.fname, c.lname
FROM customers c
LEFT JOIN sales s ON c.cust_id = s.cust_id
    AND YEAR(s.Date) = YEAR(CURDATE())
WHERE s.Sales_id IS NOT NULL

This query returns customers and left joins the sales making it optional for the query as a whole. It join on the sale is for the customer and occurred in this year. Then in the where clause it only returns a row if no row for sales is returned i.e. the Sales_id is null.

Upvotes: 0

GMB
GMB

Reputation: 222442

To filter on the date, an efficient option is to compare it against a fixed value, that represents the beginning of the current year, like DATE_FORMAT(NOW() ,'%Y-01-01').

Also, instead of using the IN operator, I would use a NOT EXISTS condition with a correlated subquery (while both should work, this is a typical use case for NOT EXISTS, since you do not actually need to access anything from the subquery): 

SELECT c.fname, c.lname
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 
    FROM sales s 
    WHERE s.cust_id = c.cust_id AND s.date >= DATE_FORMAT(NOW() ,'%Y-01-01')
)

Here is another option that uses `LEFT JOINè :

SELECT c.fname, c.lname
FROM customers c
LEFT JOIN sales s 
    ON  s.cust_id = c.cust_id 
    AND s.date >= DATE_FORMAT(NOW() ,'%Y-01-01')
WHERE s.cust_id IS NULL

Upvotes: 2

Related Questions