nasoj1100 nasoj1100 - 6 months ago 9
SQL Question

Query to get all users activity since last login

I want to create a query to get back all users, their last login date and how many questions they have answered since their last login.

I have the following query that does what I am looking for but with an individual user instead of all users.

SELECT l.user_id,
Count(*) attemptsSinceLastLogin
FROM production.score s
JOIN processedquestion pq
ON s.attempt_id = pq.attempt_id
JOIN login l
ON l.user_id = pq.user_id
WHERE l.user_id = 123
AND s.selected_answer IS NOT NULL
AND pq.attempt_datetime > (SELECT Max(in_datetime)
FROM production.login
WHERE user_id = 123);


I also have this query that gets all users and their last log in date but it doesn't have how many questions they have answered.

SELECT user_id,
Max(in_datetime)
FROM production.login
GROUP BY user_id


I am having trouble using these two queries to try and come up with one that brings back the data I am looking for all in one go. Any help appreciated

Answer

Try this:

SELECT l.user_id, 
       Count(*) attemptsSinceLastLogin 
FROM   production.score s 
   JOIN processedquestion pq 
     ON s.attempt_id = pq.attempt_id 
   JOIN login l 
     ON l.user_id = pq.user_id 
   JOIN (SELECT user_id, 
         Max(in_datetime) AS last_login
         FROM   production.login 
         GROUP  BY user_id) t
     ON l.user_id = t.user_id
WHERE  s.selected_answer IS NOT NULL 
       AND pq.attempt_datetime > t.last_login
GROUP BY l.user_id