SQL Test Based on Real Interview | SQL Interview Questions and Answers

[@ankitbansal6]

It takes a lot of effort to make these videos. Please do hit the like button and subscribe to the channel.

[@macx8360]

anything for you majesty

[@sushantkumar7450]

Ankit I do like and watch your videos regularly, but you also know where the crowd goes and which kind of channels got millions of subscribers.🙂

[@ankitbansal6]

@@sushantkumar7450 please eloborate 😊

[@vijaypradeep8315]

@@ankitbansal6 ankit can you make an class on complete de i will for sure subscribe it

[@sushantkumar7450]

​@@ankitbansal6
Hi Ankit,
I was not commenting on the quality of your videos. for me, your playlist is most valuable SQL vedios I have ever gone through in youtube.
I just wrote that comment on a lighter note regarding people watching mostly useless family vlogs and some other channels and those creators get subscribes in millions. It is a general trend in all types of youtube channels let it be politics, spiritual, sports.😊

[@alijonrahmatov5877]

Thank you Ankit for your lessons! These are super helpful!

[@LearnSQL2024]

Hi Ankit,
You're really awesome, helping so many.
Last Question in PostgreSQL:
with cte as(
select
generate_series(min(login_timestamp),max(login_timestamp),'1 day')::date as login_timestamp
from logins)
select login_timestamp from cte
where login_timestamp not in (select distinct login_timestamp::date
from logins)

[@keshavkaranam]

I wish more folks watch your channel before giving interviews , solid stuff. Infact , they should pay you 10% post getting a new job ;)

[@TheUnsponseredHaul]

Thanks a lot bro! Great content, helped me crack my SQL interview today!!!

[@ankitbansal6]

Congratulations 🎉

[@Datapassenger_prashant]

Hi Ankit Sir,
Thank you for this wonderful Case Study..
It took 70 mins for me to solve all the questions.. only for 6th question I used a bit complex query when compared to yours. other wise everthing were good. enjoyed a lot solving these problem statement..
and for last one I used recursive cte..

[@ankitbansal6]

Well done

[@SunilKumar_67]

This is truly amazing service to the analytics community. Thank you brother

[@nagarajshenoy4848]

This video is pretty simple to understand and this expertise will come with practice.. I hope all the folks practice this at least once a day.. thanks so much Ankit

[@085_Ritika]

great questions and explanations 🤩

[@debabratabar2008]

it was really a well diversed problems , thanks a lot

[@uday7291]

Hi Ankit,
I really appreciate your efforts. I learnt a lot from your videos and I've become confident in SQL
Thanks a ton.

[@ankitbansal6]

It's my pleasure

[@SidIndian082]

Brilliantly Explained sir .... Mindblowing ... 🙂

[@Reva_Prime]

❤❤❤❤❤❤❤❤ i don't have any words 🎉🎉🎉🎉🎉🎉
Love you 🤟 bro

[@sudhirsharma9103]

Thank you Ankit, it's really interesting and cool stuff

[@NiranjanDevaraddi]

Hi Ankith,
It's one of the best SQL videos, and you have amazing explanation skill.

[@ankitbansal6]

Glad you liked it!

[@TwoMinutesisEnough]

You are mind-blowing

[@abhijeetsonwani5950]

Your Lecture is awesome sir. 🙏🙏

[@AshishMishra-b9u]

thank you for providing

[@philosaifer96]

Really appreciate your efforts.. always happy to learn from you..

[@varunas9784]

Just the video I needed to revise! Thanks a ton Ankit!!
Just a twist to your 2nd Q, solve the same question (#2) WITHOUT using DATETRUNC function
it's quite interesting!
Anyone reading this comment, comment your approach in above case, its fun!

[@kadagaladurgesh3691]

In mysql :
select count(distinct USER_ID) user_count, count(SESSION_ID) as session_count,
min(LOGIN_TIMESTAMP) as min_date,
date_sub(min(LOGIN_TIMESTAMP), interval dayofmonth(min(LOGIN_TIMESTAMP)) -1 day) as first_day
from LOGINS
group by QUARTER(LOGIN_TIMESTAMP)
could you share any other approach I am happy to learn if there is any better or optimized approach

[@exanode]

-- 2. Quarter Analysis [Oracle SQL]
SELECT
TO_CHAR(ADD_MONTHS(TO_DATE('01/01/2024', 'MM/DD/YYYY'), 3 * (TO_CHAR(LOGIN_TIMESTAMP, 'Q') - 1)), 'MM/DD')
|| '/' || TO_CHAR(MIN(LOGIN_TIMESTAMP), 'YYYY') AS quarter,
COUNT(*) AS sessions,
COUNT(DISTINCT user_id) AS unique_users
FROM
logins
GROUP BY
TO_CHAR(LOGIN_TIMESTAMP, 'Q')
ORDER BY
1;

[@mohdtoufique7446]

Hi Ankit! Thanks for the content
My approach from q4 onwards
4)WITH quarter_cnt AS(
select YEAR(cast(login_timestamp AS Date)) AS login_year,DATEPART(QUARTER,cast(login_timestamp AS Date)) AS login_quarter,
COUNT(DISTINCT USER_ID) AS user_cnt,
COUNT(DISTINCT SESSION_ID) AS session_cnt
from logins
GROUP BY YEAR(cast(login_timestamp AS Date)),DATEPART(QUARTER,cast(login_timestamp AS Date)))
SELECT
CASE WHEN login_quarter=1 THEN CONCAT('01-01-',login_year)
WHEN login_quarter=2 THEN CONCAT('01-04-',login_year)
WHEN login_quarter=3 THEN CONCAT('01-07-',login_year)
WHEN login_quarter=4 THEN CONCAT('01-10-',login_year)
END AS qrtr_str_date,session_cnt,
(session_cnt-(lag(session_cnt)OVER(ORDER BY login_year,login_quarter)))*100.0/lag(session_cnt)OVER(ORDER BY login_year,login_quarter),
user_cnt
FROM quarter_cnt
--5)
WITH score_day AS(
SELECT cast(login_timestamp AS Date) AS login_day,user_id,
sum(session_score) AS total_score
FROM logins
GROUP BY cast(login_timestamp AS Date),user_id),
rn_cte AS(
SELECT *,DENSE_RANK()over(PARTITION BY login_day ORDER BY total_score DESC) AS rn
FROM score_day)
SELECT * FROM rn_cte
WHERE rn=1
----6)
WITH user_login_history AS(
SELECT USER_ID,
min(cast(LOGIN_TIMESTAMP AS DATE)) as FIRST_LOGIN,
MAX(CAST('2024-06-28' AS Date)) AS Last_Date,
COUNT(distinct cast(LOGIN_TIMESTAMP AS DATE)) as no_logins
FROM logins
GROUP BY USER_ID)
SELECT *,datediff(DAY,FIRST_LOGIN,Last_Date)+1 AS logins_btw_str_end
FROM user_login_history
WHERE (datediff(DAY,FIRST_LOGIN,Last_Date)+1)=no_logins
---7)
WITH master_dates as(
select cast('2023-07-15' AS Date) AS login_date
UNION all
SELECT DATEADD(day,1,login_date)
FROM master_dates
WHERE login_date

[@pspreetesh4166]

very good session sir

[@exanode]

Perfect timing! I have an interview in two days.
I've solved these in Oracle.
-- 1. Usernames that did not login in the past 5 months
SELECT user_id, user_name
FROM users
WHERE user_id NOT IN (
SELECT user_id
FROM logins
WHERE login_timestamp > ADD_MONTHS(SYSDATE, -5)
);
-- 2. Quarter Analysis
SELECT
TO_CHAR(ADD_MONTHS(TO_DATE('01/01/2024', 'MM/DD/YYYY'), 3 * (TO_CHAR(LOGIN_TIMESTAMP, 'Q') - 1)), 'MM/DD')
|| '/' || TO_CHAR(MIN(LOGIN_TIMESTAMP), 'YYYY') AS quarter,
COUNT(*) AS sessions,
COUNT(DISTINCT user_id) AS unique_users
FROM
logins
GROUP BY
TO_CHAR(LOGIN_TIMESTAMP, 'Q')
ORDER BY
1;
-- 3. Users in Jan2024 but not in Nov2023
SELECT DISTINCT user_id
FROM logins
WHERE TO_CHAR(LOGIN_TIMESTAMP, 'MM/YYYY') = '01/2024'
AND user_id NOT IN (
SELECT user_id
FROM logins
WHERE TO_CHAR(LOGIN_TIMESTAMP, 'MM/YYYY') = '11/2023'
);
-- 4. Percent increase from ans2
WITH raw_data AS (
SELECT
TO_DATE(
TO_CHAR(
ADD_MONTHS(TO_DATE('01/01/2024', 'MM/DD/YYYY'), 3 * (TO_CHAR(LOGIN_TIMESTAMP, 'Q') - 1)),
'MM/DD')
|| '/' || TO_CHAR(MIN(LOGIN_TIMESTAMP), 'YYYY'),
'MM/DD/YYYY') AS quarter,
COUNT(*) AS sessions,
COUNT(DISTINCT user_id) AS unique_users
FROM
logins
GROUP BY
TO_CHAR(LOGIN_TIMESTAMP, 'Q')
ORDER BY
quarter
)
SELECT
QUARTER,
SESSIONS,
unique_users,
ROUND((SESSIONS / LAG(SESSIONS, 1, SESSIONS) OVER (ORDER BY quarter)) * 100 - 100) AS pct
FROM
raw_data;
-- 5. Users with highest scores [will show multiple entries is more than one max]
WITH cte AS (
SELECT
user_id,
TRUNC(login_timestamp) AS login_time,
SUM(session_score) AS total_score
FROM
logins
GROUP BY
user_id, TRUNC(login_timestamp)
)
SELECT
user_id,
login_time,
MAX(total_score) AS score
FROM
cte
GROUP BY
user_id, login_time
ORDER BY
user_id, login_time;
-- 6. Users logged in everyday
WITH cte AS (
SELECT
user_id,
MAX(trunc(login_timestamp)) OVER (PARTITION BY user_id) AS max_date,
MIN(trunc(login_timestamp)) OVER (PARTITION BY user_id) AS min_date,
COUNT(DISTINCT trunc(login_timestamp)) OVER (PARTITION BY user_id) AS distinct_dates_count
FROM
logins
)
SELECT
DISTINCT user_id
FROM
cte
WHERE
max_date - min_date = distinct_dates_count - 1;
-- 7. Dates without login
WITH cte (start_date) AS (
SELECT MIN(TRUNC(login_timestamp)) AS start_date
FROM logins
UNION ALL
SELECT start_date + 1
FROM cte
WHERE start_date < (SELECT MAX(TRUNC(login_timestamp)) FROM logins)
)
SELECT *
FROM cte
WHERE start_date NOT IN (
SELECT DISTINCT TRUNC(login_timestamp)
FROM logins
)
ORDER BY 1;

[@ankitbansal6]

Great work 👏

[@dilipinamdar5523]

Thank you🙏

[@tanujreddy8597]

QUES 6 WAS AWESOME

[@theinsightminer08]

Thanks Ankit for providing these interview questions

[@GiriPrasath.D]

Learned many new date functions, from this video, do it more

[@aman_mashetty5185]

one of the best video and as always nice explanation Ankit

[@thegamingtron8698]

Thanks Ankit!
Not correcting you at all, but the 2nd question condition is to sort from newest to oldest.
Result should be order by datetrunc(quarter,login_timestamp) desc
"Nevertheless, exceptional learning as always."

[@ankitbansal6]

I think I missed sorting it 😊 thanks for pointing out 🙏

[@eldhokuriakose234]

Hi Ankit Sir
You're really awesome, its very helpful added to it
if you can add on these question in an seperate file it will be very useful

[@newmanokereafor2368]

Thank you @Ankit. Please share the questions as you did the CREATE & INSERT statements. Thank you once again.

[@sandeepthewanderer]

great explanation

[@MazharKhan-hx7xu]

Hi Ankit,
I appreciate your learning session.
Could you please make some videos apart from SQL queries like SQL Dashboard operation, basic options, error handling part, trigger, procedure etc...

[@addhyasumitra90]

awesome and straight to the point.. rocked it :)

[@ankitbansal6]

Cheers 🍻

[@rakeshpanigrahi577]

AWSM Sir!!

[@GritGraceGratitute]

Good one !!

[@rahulmehla2014]

32:45
with recursive cte as(
select min(date(login_timestamp)) as dt from logins
union all
select date_add(dt,interval 1 day) from cte where dt

[@amberjain7355]

Hi Ankit, it is really helpful, could you please start creating content on AWS and pyspark. Also just to add on in 1st question we need to use user tables and there might be the case user has never logged in so that entry might not come in logins table??

[@parmoddhiman678]

3rd
with cte as (select *
from logins
where login_timestamp between "2024-01-01" and "2024-01-31" ),
ct as (select *
from logins
where login_timestamp between "2023-11-01" and "2023-11-30")
select * from cte
where user_id not in (select user_id from ct)

[@ambeshpandey8937]

Superb video Ankit

[@ankitbansal6]

Thank you so much 😀

[@rahulmehla2014]

26:53
WITH cte AS (
SELECT *,
CAST(DAY(login_timestamp) AS SIGNED) -
cast((ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_timestamp)) as signed) diff
FROM logins
)
select user_id from cte group by user_id having count(distinct diff) = 1;

[@shawnsmothers1715]

I think this i great but i do think its more advance than most will ask. I cant see many companies asking these questions to juniors. One because they are pretty complex and two because of time.

[@JackHugh-eh4hl]

Great video Ankit... nothing major, I think in question 5 it should be max(score) not sum

[@ankitbansal6]

We need to do sum at user and day level first then find the max for each day using row number

[@UjjwalSinghPal-um3pb]

if i started learning for now than there are very less data engineering internships(if one has data internship it is easy to get a job ,and that person also get hand on experience ) ,,,,so should i do data analyst first only for internship and after that i will study for data engineering for job because it is also a good way , and i need to do an internship because it is in our college criteria , they do not support much if you do not have done internship??
informative video by the way

[@radhikamaheshwari4835]

with cte as (
select *,lag(login_timestamp, 1, login_timestamp) over(partition by user_id order by login_timestamp) as l,
DATEDIFF(day,
lag(login_timestamp, 1, login_timestamp-1) over(partition by user_id order by login_timestamp),
login_timestamp) as diff
from logins
--order by user_id, login_timestamp
)
select user_id
from cte
group by user_id
having count(user_id) = sum(diff)

[@rajvihari4326]

If possible, can we use min(user_id), and convert it in simple query:
*Twist is, it will give min(user_id) but max(score).
select cast(LOGIN_TIMESTAMP as date), max(SESSION_SCORE) max_score, min(user_id) user_id from logins
group by cast(LOGIN_TIMESTAMP as date)

[@saibabac7431]

Query for Q3)select distinct USER_ID from logins
where LOGIN_TIMESTAMP between '2024-01-01' and '2024-01-31'
except
select distinct USER_ID from logins
where LOGIN_TIMESTAMP between '2023-11-01' and '2023-11-30'

[@houseofroses3942]

I want to learn very complex problems i solved all the questions without any problem as i work on sql daily but i want to improve my problem solving. Kindly guide sir

[@ankitbansal6]

Check this
Complex SQL Questions for Interview Preparation: ruclips.net/p/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb

[@hairavyadav6579]

Hi Sir, Thanks a lot for making video and helping student ,
I understand each and every things but i facing some problems because i am using mysql for q2 i have no option for date_trun .
Can you help me in this

[@ankitbansal6]

date_trunc(unit, date_or_time)

[@hairavyadav6579]

@@ankitbansal6 I am asking .... In questions no 2nd you use ... datetrunc(quarter,min(log_timestamp) to get first date of the each quarter i understand but i am using mysql their is not datetrunc function or date_trunc .. so how i get the first date for the each quarter in mysql

[@hairavyadav6579]

i use this please let me kknow this will ok or not - --- date_format(min(login_timestamp),'%Y-%m-%01') as first_date

[@ankitbansal6]

@@hairavyadav6579 what if no transaction in the first month of a quarter . Then you will not get quarter start date

[@jayashreepaul3890]

In the first question There are two tables we can join the two tables and implement the query right?
With cte (Select * from logins join users on users.userid= logins.user_id )as l
Select user_id,login_timestamp
From l
As follows in the video..........

[@PowerBiLearning-xh8wk]

i was getting this error with datetrunc function 'DATETRUNC' is not a recognized built-in function name.'

[@ritudahiya6223]

@Ankit Bansal
Can you please write the answer for this query.it will take 2 mins
A. B
1. 2
2. 2
1. 3
No value 4
Null. Null
There are 2 tables .table A have one row where we have blank value(row 4) .please give output for innere join and left join
Eagerly waiting for your reply

[@ritudahiya6223]

@ankitbansal please reply on this..I have an interview tomorrow...

[@ritudahiya6223]

@ankitbansal I just want to know what will be the output when there is blank row in one table

[@rajathratnakaran7893]

@@ritudahiya6223
Blank space will be considered as a another character, it is not same as null. Two nulls aren't equal, blanks are same as 1=1,2=2.
Thanks for this question. I created a table and figured out myself. Adding details below, so that you can also try.
CREATE TABLE T (name char(20));
insert into t values
('a'),
('b'),
('c'),
('a'),
('a'),
(''),
('b'),
(null);
select * from T;
CREATE TABLE T1 (ch char(20));
insert into t1 values
('b'),
('a'),
(''),
('c'),
('a'),
(''),
('d'),
(null);
left join : 12 rows
right join : 13 rows
inner join : 11 rows

[@shubhamanand2661]

Inner - 2
Left - 6
Right - 5

[@ritudahiya6223]

Yes inner join will be 2 and not 11 rows..anyways thanks for replying

[@shivammishra-mk9jp]

Query for 1 ques. not working in mysql. I used date_add funtion compatible with mySQL but it is throwing an error.
select user_id , max(LOGIN_TIMESTAMP) from logins
group by user_id
having max(LOGIN_TIMESTAMP) < date_add(LOGIN_TIMESTAMP,INTERVAL -5 MONTH)
Error:- Unknown column in having clause.....

[@sahilnaik3904]

Question 6:
Can we do this?
with test as(
select
user_id,
min(LOGIN_TIMESTAMP) as first_day,
max(LOGIN_TIMESTAMP) as last_day,
count(LOGIN_TIMESTAMP) as ct
from logins
group by user_id
--order by 1
)
select user_id from test
where ct = DATEDIFF(day,first_day,last_day)+1

[@abdulashwaq7312]

I have query base city like, i need to check piiza send across city like bangalore, mumbai , delhi etc, pls help how write this code using slq table called pizza.

[@ankitbansal6]

Share data and expected output

[@dgsharma4773]

Hello ankit, In question number 5 you missed user_name

[@dasoumya]

Why DATETRUNC is not working in my SQL server 😢 it says 'datetrunc' is not a recognized built-in function name.

[@ankitbansal6]

Maybe older version

[@varunas9784]

@@dasoumya I guess DATETRUNC was introduced as a party of SQL server 2022.
You might wanna go through Microsoft's documentation to see if your version of SQL server supports this or not

[@ananddalmia4044]

Good morning sir,
Please help me for setup the sql environment, i show lot of video but some time network adopter error is showing
Please suggest me where should i pratice sql or which platform is good for pratice 🙏🙏🙏🙏🙏 i installed oracle 19c.....

[@ankitbansal6]

Install SQL server or MySQL

[@ananddalmia4044]

@@ankitbansal6 sir please suggest me any video because I am facing issue while installing it was showing monthly charges like that 🙏🙏🙏🙏

[@ananddalmia4044]

@@ankitbansal6 sir could you provide me link I am using windows and one more thing sir please provide me guidelines sir for data analytics if u help me out it will be helpful for me 🙏🙏

[@RamaKrishna-ll8iz]

Hello everyone i got simple solution for question number 6 . please check it
select user_id from logins where cast(login_timestamp as date)>= all(
select distinct cast(login_timestamp as date) as dt from logins)
Thanks,

[@nidhikumari1350]

How to solve Q7. in MYSQL? Anyone please assist if you know!! Thanks

[@ayushkawale2017]

didnt get the correct option from first question

[@prabhatthakur4257]

These questions asked for a experienced role or 1-2 year experience?

[@ankitbansal6]

3+ years of experience

[@rohitsharma-mg7hd]

for second question :
if there is no login in april month (2024-04) then in ur case minimun date will come as (2024-05), then this is not first day of quarter.
so your answer might fail in that case ,
for accuracy below case statement can be used which wont fail in above case :
case when qtr=1 then concat(yr,'-01-01') when qtr=3 then concat(yr,'-06-01') when qtr=2 then concat(yr,'-03-01') when qtr=4 then concat(yr,'-09-01') end as day_qtr

[@ankitbansal6]

It will work as we are doing the date trunc of quarter not month

[@rohitsharma-mg7hd]

@@ankitbansal6 yes u r right

[@ethyria7685]

QUESTION 1.
SELECT USER_NAME FROM users_new
WHERE user_id NOT IN
(SELECT USER_ID FROM
(SELECT USER_ID,LOGIN_TIMESTAMP,
DATEDIFF(MONTH, LOGIN_TIMESTAMP, MAX(LOGIN_TIMESTAMP)over()) as diff, MAX(LOGIN_TIMESTAMP)over() as highest
FROM logins) a
WHERE diff < 5 )

[@parmoddhiman678]

last answer in my sql
select * from (WITH RECURSIVE DateRange AS (
SELECT (select min(cast(login_timestamp as date)) as login_date from logins) AS Start_date
UNION ALL
SELECT DATE_ADD(Start_date, INTERVAL 1 DAY)
FROM DateRange
WHERE Start_date < '2024-06-28'
)
SELECT Start_date
FROM DateRange) x
where Start_date not in (select distinct cast(login_timestamp as date) as login_date
from logins)

[@ethyria7685]

QUESTION 2:
WITH cte AS
(SELECT *, MONTH(login_timestamp) AS month, year(login_timestamp) as yr,
CASE WHEN MONTH(login_timestamp) < 4 THEN 'Q1'
WHEN MONTH(login_timestamp) < 7 AND MONTH(login_timestamp) > 3 THEN 'Q2'
WHEN MONTH(login_timestamp) < 10 AND MONTH(login_timestamp) > 6 THEN 'Q3'
WHEN MONTH(login_timestamp) 9 THEN 'Q4'
END AS QTR
FROM logins)
SELECT MIN(login_timestamp) AS FIRST_DAY_Q, COUNT(DISTINCT user_id) as user_cnt, COUNT(SESSION_ID) as session_cnt
FROM cte
GROUP BY QTR

[@karthikvijay5626]

Hi,Ankit iam preparing SQL interview questions and Practice Questions with answers but i want in MYSQL if you make upcoming vedios in MYSQL workbench it will be helpful for me its my kind request ..

[@ethyria7685]

QUESTION 7:
WITH RECURSIVE_CTE AS
(SELECT MIN(login_timestamp) AS first_dt FROM logins
UNION ALL
SELECT first_dt + 1 as first_dt FROM RECURSIVE_CTE
)
SELECT CAST(first_dt AS DATE) AS DATE FROM RECURSIVE_CTE
WHERE first_dt < (SELECT (MAX(login_timestamp)) FROM logins)
AND CAST(first_dt AS DATE) NOT IN (SELECT CAST(login_timestamp AS DATE) FROM logins )
OPTION (MAXRECURSION 1000);

[@SK-wp4tm]

I am waiting for India to win then I will get 50% discount 😋

[@ankitbansal6]

Use code INDIA

[@tinalahoty1132]

Why +1 in Q6

[@shreyaprasad5203]

WITH cte AS (
SELECT *,
MAX(total_session_score) OVER (PARTITION BY login_date) AS max_score
FROM (
SELECT userid,
CAST(logintimestamp AS DATE) AS login_date,
SUM(session_score) AS total_session_score
FROM login
GROUP BY userid
) A
)
SELECT userid
FROM cte
WHERE max_score = total_session_score

[@parmoddhiman678]

last second in mysql
select * from (select USER_ID, min(cast(LOGIN_TIMESTAMP as date)) as first_login,
datediff("2024-06-29",min(cast(LOGIN_TIMESTAMP as date))) as no_of_login_required,
count(distinct cast(LOGIN_TIMESTAMP as date)) as login_by_user
from logins
group by user_id
order by user_id ) a
where no_of_login_required = login_by_user

[@tejas4054]

Tera itna dimag kaise chalta hai,,, tum intelligence ho iska mtlab hum nbi bhai itna chatgpt se answer nikal ke krne walw log hai hum

[@Kudapali_fans]

SELECT *
FROM users
WHERE last_login < NOW() - INTERVAL '3 months';

[@shreyaprasad5203]

WITH cte AS (
SELECT *,
LEAD(final_login_of_day, 1) OVER (PARTITION BY USER_ID ORDER BY final_login_of_day) AS next_day_login
FROM (
SELECT USER_ID,
LOGIN_DATE,
MAX(LOGIN_TIMESTAMP) AS final_login_of_day
FROM (
SELECT *,
CAST(LOGIN_TIMESTAMP AS DATE) AS login_date
FROM logins
) AS A
GROUP BY USER_ID, login_date
ORDER BY USER_ID, login_date
) AS B
),
cte2 AS (
SELECT *,
DATEDIFF(NEXT_DAY_LOGIN, FINAL_LOGIN_OF_DAY) AS diff_days
FROM cte
)
SELECT user_id, max(DIFF_DAYS) as max_days_diff
FROM cte2
group by userid
having max(DIFF_DAYS) = 1

[@harshkhannashorts]

Hi Ankit I have one concern in your solution, you returned those users which are not logged in last 5 months and at having at least once logged in record in logins table, but if user is created but not logged in once in whole history then that user will not be returned by this query..If I am wrong somewhere please correct me.. @ankitbansal6

[@rahulmehla2014]

32:37
with recursive cte as(
select min(date(login_timestamp)) as dt from logins
union all
select date_add(dt,interval 1 day) from cte where dt < (select max(date(login_timestamp)) from logins))
select c.dt from cte c left join logins l on c.dt = date(l.login_timestamp) where LOGIN_TIMESTAMP is null;