LeetCode 1454: Active Users [SQL]

Solution and walkthrough of leetcode database problem 1454: Active Users. I'm using MySQL but this solution should work in any SQL dialect such as PostgreSQL SQL Server, etc.

Playlists:

[waleeddib]

It's very helpful when you visualize the joins like that. I really appreciate it and I hope you keep doing the visualizations no matter how simple it might seem to you so you can cover the beginner pool of audience as well! Thanks again!

[jingjingchang]

I liked all your tutorial videos because they are so straightforward and well-informed. Thank you for making those videos.

[hgocersz]

This channel is great for those who would like to learn MySQL on their own. Thanks a lot Müller !

[chineduezeofor]

Very informative as always! My querying skills in SQL have greatly improved thanks to your videos. God bless you!

[abinayensivakumar]

Hi Frederik, great video by the way. One question: why do you need to add a.login_date to the the group by. I still don't understand.

[angelmeist]

Hi Frederik,
the higher dates are the dates in B table right? in this case, should it be between 1 and 4? However, if you assigned A table dates to be the higher dates then the having statement should be having count(a.distinct login_date=4)?
also since the problem asks for 5 or more consecutive login days, shouldn't what inside the count statement be >=4 instead of =4? I thought =4 means login in for exact 5 consecutive days only.

[PATRICKCHUAD]

thank you for the playlist. I will check them one by one to learn how to solve using SQL.
I have tried to solve it also below is my solution using lag function.

select z.id, c.name, diff_day, total_diff from (
select id, diff_day, sum(diff_day) total_diff from (
select id, login_date, lag(login_date) over (order by id, login_date) prev_day,
over (order by id, login_date),
when id=lag(id) over (order by id, login_date) then
- lag(login_date) over (order by id, login_date) else 1 end diff_day
from ( select id, login_date from logins group by id, login_date order by id, login_date) x) y
group by id, diff_day
order by id, diff_day ) z
left join accounts c on z.id = c.id where diff_day=1 and total_diff >=5;

[huansun]

Hi Frederik, I love the viz for the self-join! Still confused about one thing though, when constraining the DATEDIFF( ) BETWEEN 1 AND 4, will it rule out the cases that the user had logged in for like 6 or 7 consecutive days? Which I think is also what the question asking about, of finding the active users who logged in to their accounts for `five or more` consecutive days.

[ayeoh]

this was a cleaver use of counting the distinct datediff. btwn 1-4, can only output consecutive 1, 2, 3, 4 if it is counting distinct number.

[debashish_dutta]

Hi Frederik,
I followed your solution but used IN clause:

select id, name from accounts where id in
(select distinct l1.id from logins l1 join logins l2 on l1.id = l2.id
and DATEDIFF(l2.login_date, l1.login_date) BETWEEN 1 AND 4
group by l1.id, l1.login_date
having COUNT(DISTINCT l2.login_date) = 4)
order by id

Which will be more efficient with respect to time, doing a join or with IN clause?

[YASHKUMARJAIN]

Thanks for teaching us power of self join

[akritisaxena]

Hi Frederik,

I follow your channel for Leetcode SQL questions.
Big thanks to you for this one :)

For the above question, I followed one approach, the solution was executed successfully but is not accepted upon submission.

It will be great if you can explain what's wrong here or how this can be corrected:

select r.id, accounts.name as name
from
(select distinct a.id as id, a.login_date as adate, b.login_date as bdate
from logins a
join logins b on a.id = b.id
and datediff(b.login_date, a.login_date) = 4
order by id) as r
join accounts on r.id = accounts.id


If I give and datediff(b.login_date, a.login_date) >= 4, to satisfy the number of consecutive days equal to more than 5, it gave me an altogether different result.

[sincerelygraciee]

this solution is no longer working with the new test cases on leetcode

[GM-xzxc]

What an excellent tutorial. Why dobt you make more?

[ankitgada]

Very informative and well-structured. The problem says 5 or more consecutive days - If the consecutive days are 7, will this query show up for count(distinct b.login_date) as 6?

[shubham]

Hey, QQ why'd you do between 0 & 4. why not datediff()=4?

[adityakamath]

In the case of login dates for id XYZ being 1st, 2nd, 3rd, 4th and 8th Jan - we will get difference of 1, 2, 3 and 4 (8 - 4). But it is not 5 consecutive login days. Will this case be eliminated by your query? How?

[jirenqi]

Can you please explain why we need distinct a.id again when we have group by already? Thank you.

[azimsayed]

amazing video mate how to get monthly active user, presents the percentage change compared with the previous month
• name the months

[causalinference]

DATEDIFF(a.login_date, b.login_date) is the same as DATEDIFF(b.login_date, a.login_date)