Leetcode MEDIUM 3308 - Find Top Performing Driver - ROW_NUMBER() in SQL | Everyday Data Science

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

SQL Schema:
CREATE TABLE If not exists Drivers (
driver_id INT ,
name VARCHAR(100),
age INT,
experience INT,
accidents INT
)
CREATE TABLE If not exists Vehicles (
vehicle_id INT ,
driver_id INT,
model VARCHAR(100),
fuel_type VARCHAR(50),
mileage INT)
CREATE TABLE If not exists Trips (
trip_id INT ,
vehicle_id INT,
distance INT,
duration INT,
rating INT
)
Truncate table Drivers
insert into Drivers (driver_id, name, age, experience, accidents) values ('1', 'Alice', '34', '10', '1')
insert into Drivers (driver_id, name, age, experience, accidents) values ('2', 'Bob', '45', '20', '3')
insert into Drivers (driver_id, name, age, experience, accidents) values ('3', 'Charlie', '28', '5', '0')
Truncate table Vehicles
insert into Vehicles (vehicle_id, driver_id, model, fuel_type, mileage) values ('100', '1', 'Sedan', 'Gasoline', '20000')
insert into Vehicles (vehicle_id, driver_id, model, fuel_type, mileage) values ('101', '2', 'SUV', 'Electric', '30000')
insert into Vehicles (vehicle_id, driver_id, model, fuel_type, mileage) values ('102', '3', 'Coupe', 'Gasoline', '15000')
Truncate table Trips
insert into Trips (trip_id, vehicle_id, distance, duration, rating) values ('201', '100', '50', '30', '5')
insert into Trips (trip_id, vehicle_id, distance, duration, rating) values ('202', '100', '30', '20', '4')
insert into Trips (trip_id, vehicle_id, distance, duration, rating) values ('203', '101', '100', '60', '4')
insert into Trips (trip_id, vehicle_id, distance, duration, rating) values ('204', '101', '80', '50', '5')
insert into Trips (trip_id, vehicle_id, distance, duration, rating) values ('205', '102', '40', '30', '5')
insert into Trips (trip_id, vehicle_id, distance, duration, rating) values ('206', '102', '60', '40', '5')

Pandas Schema:
data = [[1, 'Alice', 34, 10, 1], [2, 'Bob', 45, 20, 3], [3, 'Charlie', 28, 5, 0]]
drivers = pd.DataFrame({
'driver_id': pd.Series(dtype='int'),
'name': pd.Series(dtype='str'),
'age': pd.Series(dtype='int'),
'experience': pd.Series(dtype='int'),
'accidents': pd.Series(dtype='int')
})
data = [[100, 1, 'Sedan', 'Gasoline', 20000], [101, 2, 'SUV', 'Electric', 30000], [102, 3, 'Coupe', 'Gasoline', 15000]]
vehicles = pd.DataFrame({
'vehicle_id': pd.Series(dtype='int'),
'driver_id': pd.Series(dtype='int'),
'model': pd.Series(dtype='str'),
'fuel_type': pd.Series(dtype='str'),
'mileage': pd.Series(dtype='int')
})
data = [[201, 100, 50, 30, 5], [202, 100, 30, 20, 4], [203, 101, 100, 60, 4], [204, 101, 80, 50, 5], [205, 102, 40, 30, 5], [206, 102, 60, 40, 5]]
trips = pd.DataFrame({
'trip_id': pd.Series(dtype='int'),
'vehicle_id': pd.Series(dtype='int'),
'distance': pd.Series(dtype='int'),
'duration': pd.Series(dtype='int'),
'rating': pd.Series(dtype='int')
})

#leetcode #datascience #tutorial