LeetCode 1890 Interview SQL Question with Detailed Explanation | Practice SQL

Previous Video: LeetCode 1873 Employee Special Bonus

In this video I solve and explain a leetcode SQL question using MySQL query. This question has been asked in Apple, Facebook, Amazon, Google, Adobe, Microsoft, Adobe interviews or what we popularly call FAANG interviews.
I explain the related concept as well. This question is about finding latest login in 2020 and also includes points to keep in mind to develop SQL queries.

LeetCode is the best platform to help you enhance your skills, expand your knowledge and prepare for technical interviews.

If you found this helpful, Like and Subscribe to the channel for more content.

#LeetCodeSQL #FAANG #SQLinterviewQuestions

[expandingourselves]

select user_id, max(time_stamp) as last_stamp from Logins
where year(time_stamp ) = 2020
group by 1

This is believe is a easier method to solve this problem :)

[imdeepu]

Good Explanation..Thank you for putting effort to explain it more detaily. Really appreciated.

[abhimanyutiwari]

Hi, great explaination. I just want to know if this question can be done by using GROUP BY? Or we cannot use composite key in GROUP BY? Something like
SELECT user_id, MAX(timestamp)

[rishabhjain_]

I came up with this solution which is easier and more understandable
select user_id, MAX(time_stamp) as last_stamp
from logins
where year(time_stamp) = '2020'
group by user_id
having count(user_id) > 0

[inspiredomkar]

Since, Many students may or may not be aware of the first_value() function it would be better to use DENSE_RANK() and solve the question like following:
WITH cte AS
(
SELECT DISTINCT user_id, time_stamp,
DENSE_RANK() OVER(PARTITION BY user_id ORDER BY time_stamp DESC) AS rnk
FROM Logins
WHERE EXTRACT(year FROM time_stamp) = '2020'
)
SELECT user_id, time_stamp AS last_stamp
FROM cte
WHERE rnk = 1

[rishabhjain]

I came up with solution which is much easier and understandable
select user_id, MAX(time_stamp) as last_stamp
from logins
where year(time_stamp) = '2020'
group by user_id
having count(user_id) > 0

[rose]

Why we cannot use having instead of where, isnt having clause used to filter record in groups which we are trying do here. Please explain

[-Joshna]

# Write your MySQL query statement below
select user_id, max(time_stamp) as last_stamp from Logins where year(time_stamp)='2020' group by user_id ;

[gagansingh]

If you give rank in desc order then you can extract it by rank 1

[yassinbenyahia]

yours seems little complicated
select user_id, MAX(time_stamp) as last_stamp
from Logins where YEAR(time_stamp)=2020
group by user_id

[gunasourav]

Can you please provide links to those leetcode questions in description

[bhaskarpathak]

Easy_method

SELECT user_id, MAX(time_stamp) AS last_stamp
FROM logins
WHERE time_stamp LIKE "2020%"
GROUP BY user_id
ORDER BY MAX(time_stamp)

[pankajchandel]

select user_id, max(time_stamp) as last_stamp from Logins
where year(time_stamp) = '2020'
group by user_id

[supratimbhattacharjee]

SELECT user_id, MAX(time_stamp) AS last_stamp FROM Logins WHERE YEAR(time_stamp)='2020' GROUP BY user_id;