Is there a better way to scale the solution for this problem assuming the table has a billion rows of data. I am interested in getting all the new users who submitted a request in 2016.
Requests( request_id, request_day, user_id, userprofile_id )
SELECT DISTINCT user_id
WHERE EXTRACT( year FROM request_day ) = 2016
( SELECT DISTINCT user_id
WHERE EXTRACT( YEAR FROM request_day ) <> 2016
I would use the following query to answer this question:
SELECT user_id FROM requests GROUP BY user_id HAVING SUM(CASE WHEN YEAR(request_day) <> 2016 THEN 1 ELSE 0 END) = 0
This will only return users who have requests appearing 2016 and no other year.