CODEWITHSUNDEEP
mysqldatesubquery
Didi Bui
Didi Bui

Reputation: 643

MySQL - customers with at least one purchase in every one of the three months

I'm trying to select all customers who have at least one purchase in every one of the three months defined by a function called PrevMonth that accepts 2 parameters and returns a string 'YYYY-MM'. First parameter is a date and the second is the number of months to be subtracted from the date in first parameter.

delimiter #
drop function if exists PrevMonth#
CREATE FUNCTION PrevMonth (
  in_date DATE
, in_mn_adjust INT)
RETURNS varchar(20)

BEGIN
DECLARE adjusted_date varchar(20);

set in_date := coalesce(in_date, current_date());
set adjusted_date := in_date;
set adjusted_date := date_format(date_sub(adjusted_date, interval in_mn_adjust month), '%Y-%m');

RETURN adjusted_date;
END;
#

And here's my query to select the customers that made at least one purchase each month (I manually counted and there's only one customer)

select DISTINCT oh.cust_id
      , concat(cust_name_last,', ',cust_name_first) as 'customer name'
      , order_date
from a_bkorders.order_headers oh
join a_bkorders.customers cu on oh.cust_id = cu.cust_id
where Date_format(order_date, '%Y-%m') in (PrevMonth(current_date(), 4))
AND oh.cust_id IN 
    (
    SELECT cust_id
    FROM a_bkorders.order_headers   
    WHERE Date_format(order_date, '%Y-%m') in (PrevMonth(current_date(), 3)))
AND oh.cust_id IN
    (
    SELECT cust_id
    FROM a_bkorders.order_headers oh    
    WHERE Date_format(order_date, '%Y-%m') in (PrevMonth(current_date(), 2)))   
;#

And some strange reason that I can't figure out, it only show me names of customers from the first month (PrevMonth(current_date(), 4) and that's it. The 'AND's and subqueries didn't work.

Somebody knows why??

Thank you for your help,

Didi

Upvotes: 0

Views: 482

Answers (2)

user3400008
user3400008

Reputation: 21

  • EXISTS is when you need to match the results of query with another subquery. Query#1 results need to be retrieved where SubQuery results match. Kind of a Join.
    E.g. select customers who have placed orders in month1 and say month2 too,

  • Whereas IN is used to retrieve if the value of a specific column lies in a list (1,2,3,4,5). In this particular case, it would output all the customers who have a record in at least one of the previous months not necessarily ALL.

Upvotes: 1

Didi Bui
Didi Bui

Reputation: 643

OK, after getting stuck for 3 days on the same problem. I've realized that in this case, an intersection (or AND.. IN for MySQL) wouldn't work. So to get the customer that has purchased at least one order in each of the months, I had to use correlated joins with EXISTS. Now, I get one name.

select DISTINCT cust_id, concat(cust_name_last,', ',cust_name_first) as 'customer name'
from a_bkorders.customers
where exists
    (select * 
    from a_bkorders.order_headers
    where order_headers.cust_id = customers.cust_id
    and date_format(order_date, '%Y-%m') in (PrevMonth( current_date(), 3)))
and exists
    (select *
    from a_bkorders.order_headers
    where order_headers.cust_id = customers.cust_id
    and date_format(order_date, '%Y-%m') in (PrevMonth( current_date(), 4)))
and exists
    (select *
    from a_bkorders.order_headers
    where order_headers.cust_id = customers.cust_id
    and date_format(order_date, '%Y-%m') in (PrevMonth( current_date(), 2)));

And I get :

enter image description here

VOILA!!!

Upvotes: 1

Related Questions