Myntra SQL Interview Question | Biggest Window Between Visits 💡

In this video we will solve the SQL Medium level interview question asked in Myntra, shared on Code360 by Coding Ninjas .💡
SQL Script:
-- Create the UserVisits table
CREATE TABLE UserVisits (
user_id INT,
visit_date DATE
);
-- Insert the provided data into the UserVisits table
INSERT INTO UserVisits (user_id, visit_date)
VALUES
(1, '2020-11-28'),
(1, '2020-10-20'),
(1, '2020-12-03'),
(2, '2020-10-05'),
(2, '2020-12-09'),
(3, '2020-11-11');
Easy - Level easy questions -
• Easy Level SQL Intervi...
#sqlserver #dataanalytics #dataanalyst
#dataanalyticssqlqueries #sqlqueryinterviewquestionsandanswers
#sql #sqldeveloper #dataanalytics #data #myntra #faang #faangpreparation #productbasedcompanies #datanalysis

[@utkarshakulkarni1494]

Well explained

[@ChaitanyaKariya-x4q]

select user_id ,max(diff) from (
select *,
lead - visit_date as diff
from
(select * ,
lead(visit_date,1,'2021-01-01') over(partition by user_id order by visit_date)
from uservisits)x) y
group by user_id

[@harshitsalecha221]

WITH cte1 AS (SELECT user_id,
DATEDIFF(LEAD(visit_date,1,'2021-01-01') OVER(PARTITION BY user_id ORDER BY visit_date),visit_date) as largest_window_of_days
FROM UserVisits), cte2 AS (SELECT *,
RANK() OVER(PARTITION BY user_id ORDER BY largest_window_of_days DESC) as rn
FROM cte1)
SELECT user_id,largest_window_of_days
FROM cte2
WHERE rn=1;