How to get users that purchased items ONLY in a specific time period (MySQL Database)

Question

I have a table that contains all purchased items.

I need to check which users purchased items in a specific period of time (say between 2013-03-21 to 2013-04-21) and never purchased anything after that.

I can select users that purchased items in that period of time, but I don't know how to filter those users that never purchased anything after that...

SELECT `userId`, `email` FROM my_table 
WHERE `date` BETWEEN '2013-03-21' AND '2013-04-21' GROUP BY `userId`

Answer 1

Give this a try

SELECT 
  user_id
FROM 
  my_table 
WHERE 
  purchase_date >= '2012-05-01'        --your_start_date
GROUP BY 
  user_id
HAVING 
  max(purchase_date) <= '2012-06-01';  --your_end_date

It works by getting all the records >= start date, groups the resultset by user_id and then finds the max purchase date for every user. The max purchase date should be <=end date. Since this query does not use a join/inner query it could be faster

Test data

CREATE table user_purchases(user_id int, purchase_date date);
insert into user_purchases values (1, '2012-05-01');
insert into user_purchases values (2, '2012-05-06');
insert into user_purchases values (3, '2012-05-20');
insert into user_purchases values (4, '2012-06-01');
insert into user_purchases values (4, '2012-09-06');
insert into user_purchases values (1, '2012-09-06');

Output

| USER_ID |
-----------
|       2 |
|       3 |

SQLFIDDLE

Answer 2

This is probably a standard way to accomplish that:

SELECT `userId`, `email` FROM my_table mt
WHERE `date` BETWEEN '2013-03-21' AND '2013-04-21' 
AND NOT EXISTS (
    SELECT * FROM my_table mt2 WHERE 
        mt2.`userId` = mt.`userId` 
        and mt2.`date` > '2013-04-21' 
)
GROUP BY `userId`