Leetcode HARD 3384 - SELF JOIN in SQL Explained - Team Dominance by Pass | Everyday Data Science

SQL Schema:
CREATE TABLE If not exists Teams (
player_id INT,
team_name VARCHAR(100)
)

CREATE TABLE if not exists Passes (
pass_from INT,
time_stamp VARCHAR(5),
pass_to INT
)
Truncate table Teams
insert into Teams (player_id, team_name) values ('1', 'Arsenal')
insert into Teams (player_id, team_name) values ('2', 'Arsenal')
insert into Teams (player_id, team_name) values ('3', 'Arsenal')
insert into Teams (player_id, team_name) values ('4', 'Chelsea')
insert into Teams (player_id, team_name) values ('5', 'Chelsea')
insert into Teams (player_id, team_name) values ('6', 'Chelsea')
Truncate table Passes
insert into Passes (pass_from, time_stamp, pass_to) values ('1', '00:15', '2')
insert into Passes (pass_from, time_stamp, pass_to) values ('2', '00:45', '3')
insert into Passes (pass_from, time_stamp, pass_to) values ('3', '01:15', '1')
insert into Passes (pass_from, time_stamp, pass_to) values ('4', '00:30', '1')
insert into Passes (pass_from, time_stamp, pass_to) values ('2', '46:00', '3')
insert into Passes (pass_from, time_stamp, pass_to) values ('3', '46:15', '4')
insert into Passes (pass_from, time_stamp, pass_to) values ('1', '46:45', '2')
insert into Passes (pass_from, time_stamp, pass_to) values ('5', '46:30', '6')

Pandas Schema:
data = [[1, 'Arsenal'], [2, 'Arsenal'], [3, 'Arsenal'], [4, 'Chelsea'], [5, 'Chelsea'], [6, 'Chelsea']]
teams = pd.DataFrame(columns=["player_id", "team_name"]).astype({"player_id": "int", "team_name": "string"})

data = [[1, '00:15', 2], [2, '00:45', 3], [3, '01:15', 1], [4, '00:30', 1], [2, '46:00', 3], [3, '46:15', 4], [1, '46:45', 2], [5, '46:30', 6]]
passes = pd.DataFrame(columns=["pass_from", "time_stamp", "pass_to"]).astype({"pass_from": "int", "time_stamp": "string", "pass_to": "int"})

#leetcode #datascience #tutorial

[Mahesh_]

We requested so many time, but still you are making video on sql only . There are already tons of questions available..we want end to end ML projects