Teja Teja - 1 month ago 5
SQL Question

Get all users who placed requests in last three years

I would like to get all the list of users who placed requests in the last three years.

Requests( request_id, request_day, user_id, userprofile_id )

Am I doing it right?

SELECT user_id
FROM requests
WHERE EXTRACT(YEAR FROM request_day) IN ( 2014,2015,2016 )
GROUP BY user_id
HAVING COUNT(*) = 3;

Answer

Your current approach is almost correct. All you need to do is to count by the distinct number of years:

SELECT user_id
FROM requests
WHERE YEAR(request_day) IN (2014, 2015, 2016)
GROUP BY user_id
HAVING COUNT(DISTINCT YEAR(request_day)) = 3;

If the DISTINCT count of years is 3, then it implies that a user has all three of the years in your WHERE IN clause.

Note that another way to do this would be conditional aggregation:

SELECT user_id
FROM requests
GROUP BY user_id
HAVING SUM(CASE WHEN YEAR(request_day) = 2014 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN YEAR(request_day) = 2015 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN YEAR(request_day) = 2016 THEN 1 ELSE 0 END) > 0

This approach would scale better if your query were to get more complex.

Comments