Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/1972.html
1972. First and Last Call On the Same Day
Level
Hard
Description
Table: Calls
+--------------+----------+
| Column Name | Type |
+--------------+----------+
| caller_id | int |
| recipient_id | int |
| call_time | datetime |
+--------------+----------+
(caller_id, recipient_id, call_time) is the primary key for this table.
Each row contains information about a call the time of a call between caller_id and recipient_id.
Write an SQL query to report the IDs of the user who had the first and the last call with the same person on any day.
Return the result table in any order.
The query result format is in the following example:
Calls table:
+-----------+--------------+---------------------+
| caller_id | recipient_id | call_time |
+-----------+--------------+---------------------+
| 8 | 4 | 2021-08-24 17:46:07 |
| 4 | 8 | 2021-08-24 19:57:13 |
| 5 | 1 | 2021-08-11 05:28:44 |
| 8 | 3 | 2021-08-17 04:04:15 |
| 11 | 3 | 2021-08-17 13:07:00 |
| 8 | 11 | 2021-08-17 22:22:22 |
+-----------+--------------+---------------------+
Result table:
+---------+
| user_id |
+---------+
| 1 |
| 4 |
| 5 |
| 8 |
+---------+
On 2021-08-24, the first and last call of this day for user 8 was with user 4. User 8 should be included in the answer.
Similary, User 4 had the first and last call on 2021-08-24 with user 8. User 4 should be included in the answer.
On 2021-08-11, user 1 and 5 had a call. The call was the only call for both of them on this day. Since this call is the first and last call of the day for both of them, they both should be included in the answr.
Solution
Combine caller_id and recipient_id and generate t1. Then generate the rankings as t2. Select the users with rankings equal to 1 and with the same other user.
# Write your MySQL query statement below
with t1 as (
select caller_id as id1, recipient_id as id2, call_time from Calls
union all
select recipient_id as id1, caller_id as id2, call_time from Calls
),
t2 as (
select id1, id2, date(call_time) as dt,
rank() over(partition by id1, date(call_time) order by call_time) as rk1,
rank() over(partition by id1, date(call_time) order by call_time desc) as rk2
from t1
)
select distinct id1 user_id from t2
where rk1 = 1 or rk2 = 1
group by dt, id1 having count(distinct id2) = 1;