Window Function in SQL: A Step-by-Step Guide to Solve Amazon Interview Question

[@dannyd9333]

This channel is a gold mine of info!

[@Moflia2]

I love y'alls website! It has been a lot of fun to get on, practice, and see how other people answer the same question in many different ways. Thank you

[@stratascratch]

Thank you! Appreciate the kind words. If you have any feedback or feature requests, you can always reach out to me here or at [e-mail].

[@mehmetkaya4330]

Great! Thank you so much!

[@stratascratch]

Glad it was helpful!

[@chillvibe4745]

Great question!
In my opinion it's not right to include the call made on 2022-07-06 because there is only one call made on that day.
The way the question is asked it seems like we should filter out that day.
In my solution I filtered it out by getting only the days where there is more than one call.

[@redwannabil8031]

i couldnt figure out the assumptions but this is what i came up with...
select x.* from
(select *,
case when datepart(day,date_called)=lead((datepart(day,date_called))) over(partition by caller_id order by date_called)
and recipient_id =lead(recipient_id) over (partition by caller_id order by date_called)
then 1 else 0 end as flag
from caller_history) x
where x.flag=1

[@abhimanyutiwari100]

Can we write case statement in first_value() function?

[@stratascratch]

I think so. What would it look like? Try it in the coding editor. There's a link in the description.

[@tanmaythaker2905]

My solution
with first_last_calls as (
select *,
first_value(recipient_id) over (partition by caller_id,date_called::DATE order by date_called asc) as first_call_for_that_day,
first_value(recipient_id) over (partition by caller_id, date_called::DATE order by date_called desc range between unbounded preceding and unbounded following) as last_call_for_that_day
from caller_history)
select distinct caller_id, recipient_id, date_called::DATE
from first_last_calls
where first_call_for_that_day = last_call_for_that_day;