LeetCode SQL Medium

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

Question: https://leetcode.com/problems/find-longest-calls/description/ 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