SQL Interview Questions – The Latest Login in 2020

Problem Description –

Write an SQL query to report the latest login for all users in the year 2020. Do not include the users who did not login in 2020.

Return the result table in any order.

The query result format is in the following example.

Problem Link – Latest Login

Difficulty Level – Easy

Solution –

SELECT
    user_id,
    time_stamp as last_stamp
FROM (
SELECT
    user_id,
    time_stamp,
    DENSE_RANK() OVER(PARTITION BY user_id ORDER BY time_stamp DESC) as rnk
FROM Logins
WHERE YEAR(time_stamp) = '2020'
    ) x
WHERE rnk = 1