Teja Teja - 1 month ago 10
SQL Question

Get all the new users who submitted a request in 2016

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
FROM requests
WHERE EXTRACT( year FROM request_day ) = 2016
AND user_id
NOT IN
( SELECT DISTINCT user_id
FROM requests
WHERE EXTRACT( YEAR FROM request_day ) <> 2016
);

Answer

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.

Comments