LeetCode Medium 1709 Interview SQL Question with Detailed Explanation

In this video I solve and explain a medium difficulty 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 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

[florincopaci]

with flo as (
select user_id, datediff(day, visit_date, lead(visit_date, 1, cast('2021-1-1' as date)) over(partition by user_id order by visit_date)) as gape
from table)
select user_id, max(gape)as max_diff
from flo
group by user_id
All the best!

[adityams]

*Instead of using CASE for null value, LEAD can take 3 args.*
*LEAD(column, number, default value)*
we can give a 3rd arg as the given date and there wont be any null value.

[maheshwaroli]

select * from uservisits;
with cte as (select *from uservisits order by user_id, visit_date),
cte2 as (select *, ifnull(lead(visit_date) over( partition by user_id order by visit_date), "2021-01-01") as
next_date from cte),
cte3 as(select user_id, DATEDIFF(next_date, visit_date) as biggest_window from cte2)
select user_id, max(biggest_window) as biggest_window from cte3 group by user_id;

[yashnikhare]

with data1 as (
select *, lead(visit_date, 1, cast('2021-01-01' as date)) over(partition by user_id order by visit_date) nxt_date from UserVisits
),
data2 as (
select *, (nxt_date-visit_date) as window from data1 )

select user_id, max(window) as max_window from data2 group by user_id order by user_id