Leetcode Problem 1972. First and Last Call On the Same Day
1972. First and Last Call On the Same Day
Leetcode Solutions
UNION + RANK() + HAVING()
Use UNION to combine two SELECT queries, one where the user is the caller and another where the user is the recipient, to create a unified view of calls with user_id and recipient_id.
Create a CTE (CTE1) from the unified view, where we partition by user_id and call date, and use the DENSE_RANK() window function to assign ranks in ascending and descending order of call_time.
Select distinct user_id from CTE1 where the rank is 1 (first or last call of the day).
Group the results by user_id and call date.
Use the HAVING clause to filter groups where the count of distinct recipient_id is 1, indicating the first and last calls were with the same person.
erDiagram
Calls {
int caller_id
int recipient_id
datetime call_time
}