Links

Exercise 26:1972.First and Last Call On the Same Day

1.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.

2.Create Table and insert into valu('

create table if not exists leetcode.ex_1972_calls
(caller_id string,
recipient_id string ,
call_time timestamp
) stored as orc ;
​
INSERT INTO table leetcode.ex_1972_calls VALUES
('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')
​