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?
WHERE EXTRACT(YEAR FROM request_day) IN ( 2014,2015,2016 )
GROUP BY user_id
HAVING COUNT(*) = 3;
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;
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.