SQL - Showing only users who made a purchase in BOTH Q4 2021, and any date in 2022-CodePudding

I want to run a SQL query to show only users who purchased BOTH in Q4 2021, and also purchased at any date in 2022. I don't want to show users who only purchased in one period or the other.

SELECT user_id 
FROM table1
INNER JOIN table2 ON table1.col = table2.col
    AND month(date(ORDER_PLACED)) IN ('10','11','12') 
    AND year(date(ORDER_PLACED)) in ('2021')
    AND year(date(ORDER_PLACED)) in ('2021')

CodePudding user response：

Presuming you are using MySQL, You can use INTERSECT clause -

SELECT user_id 
  FROM table1
 INNER JOIN table2 ON table1.col = table2.col
 WHERE ORDER_PLACED BETWEEN '01-10-2021' AND '12-31-2021'
INTERSECT
SELECT user_id 
  FROM table1
 INNER JOIN table2 ON table1.col = table2.col
 WHERE ORDER_PLACED BETWEEN '01-01-2022' AND '12-31-2022';

CodePudding user response：

SELECT user_id
FROM table1
INNER JOIN table2 ON table1.col = table2.col
WHERE (month(date(ORDER_PLACED)) IN ('10','11','12') AND year(date(ORDER_PLACED)) = '2021')
AND
(EXISTS (SELECT 1 FROM table2
WHERE user_id = table1.user_id
AND year(date(ORDER_PLACED)) = '2022'))
GROUP BY user_id
HAVING COUNT(DISTINCT year(date(ORDER_PLACED))) = 2;

This query will show only users who purchased BOTH in Q4 2021 and also in 2022, by joining the two tables on a common column, filtering the results to only show purchases made in the Q4 of 2021 and also in 2022 by using the month and year functions on the date column and checking for user_id on both conditions, then grouping by user_id and having a count of distinct years equal to 2, meaning that the user purchased in both years.