Leetcode MEDIUM 3124 - Find Longest Calls RANKING in SQL - Explained by Everyday Data Science

Опубликовано: 05 Ноябрь 2024
на канале: Everyday Data Science

254

Question: https://leetcode.com/problems/find-lo...

SQL Schema:
Create table if Not Exists Contacts(id int, first_name varchar(20), last_name varchar(20))
Create table if Not Exists Calls(contact_id int, type ENUM('incoming', 'outgoing'), duration int)
Truncate table Contacts
insert into Contacts (id, first_name, last_name) values ('1', 'John', 'Doe')
insert into Contacts (id, first_name, last_name) values ('2', 'Jane', 'Smith')
insert into Contacts (id, first_name, last_name) values ('3', 'Alice', 'Johnson')
insert into Contacts (id, first_name, last_name) values ('4', 'Michael', 'Brown')
insert into Contacts (id, first_name, last_name) values ('5', 'Emily', 'Davis')
Truncate table Calls
insert into Calls (contact_id, type, duration) values ('1', 'incoming', '120')
insert into Calls (contact_id, type, duration) values ('1', 'outgoing', '180')
insert into Calls (contact_id, type, duration) values ('2', 'incoming', '300')
insert into Calls (contact_id, type, duration) values ('2', 'outgoing', '240')
insert into Calls (contact_id, type, duration) values ('3', 'incoming', '150')
insert into Calls (contact_id, type, duration) values ('3', 'outgoing', '360')
insert into Calls (contact_id, type, duration) values ('4', 'incoming', '420')
insert into Calls (contact_id, type, duration) values ('4', 'outgoing', '200')
insert into Calls (contact_id, type, duration) values ('5', 'incoming', '180')
insert into Calls (contact_id, type, duration) values ('5', 'outgoing', '280')

Pandas Schema:
data = [[1, 'John', 'Doe'], [2, 'Jane', 'Smith'], [3, 'Alice', 'Johnson'], [4, 'Michael', 'Brown'], [5, 'Emily', 'Davis']]
contacts = pd.DataFrame(data, columns=['id', 'first_name', 'last_name']).astype({'id':'Int64', 'first_name':'object', 'last_name':'object'})

data = [[1, 'incoming', 120], [1, 'outgoing', 180], [2, 'incoming', 300], [2, 'outgoing', 240], [3, 'incoming', 150], [3, 'outgoing', 360], [4, 'incoming', 420], [4, 'outgoing', 200], [5, 'incoming', 180], [5, 'outgoing', 280]]
calls = pd.DataFrame(data, columns=['contact_id', 'type', 'duration']).astype({'contact_id': 'Int64', 'type': 'category', 'duration': 'Int64'})

#leetcodesolutions #datascience #sql