Solving a Formula 1 challenge with SQL in Snowflake ❄️
1. Return a list of F1 drivers (limit to 20 , sort by driverid)
SELECT driverid,
forename as "First Name",
surname as "Last Name"
FROM drivers
ORDER BY driverid ASC
Limit 20;
2. Return the list of the top 10 drivers and their fastest lap speed (no aggregation)
SELECT d.driverid,
d.forename as "First Name",
d.surname as "Last Name",
ROUND(fastestlapspeed, 2) as "Fastest Lap Speed"
FROM drivers as d
JOIN results as r
ON d.driverid = r.driverid
WHERE "Fastest Lap Speed" IS NOT NULL
ORDER BY "Fastest Lap Speed" DESC
LIMIT 10;
3. Return the list of the top 10 drivers and their fastest lap speed ever
SELECT d.driverid,
d.forename as "First Name",
d.surname as "Last Name",
ROUND(MAX(r.fastestlapspeed), 2) as "Fastest Lap Speed Ever"
FROM drivers as d
JOIN results as r
ON d.driverid = r.driverid
WHERE r.fastestlapspeed IS NOT NULL
GROUP BY d.driverid, "First Name", "Last Name"
ORDER BY "Fastest Lap Speed Ever" DESC
LIMIT 10;
4. Return the list of drivers and their fastest lap speed in Monaco in 2021
SELECT d.driverid,
d.forename as "First Name",
d.surname as "Last Name",
ROUND(MAX(r.fastestlapspeed), 2) as "Fastest Lap Speed in Monaco"
FROM drivers as d
JOIN results as r
ON d.driverid = r.driverid
JOIN races as ra
ON r.raceid = ra.raceid
WHERE r.fastestlapspeed IS NOT NULL
AND ra.name LIKE '%Monaco%'
AND ra.year = 2021
GROUP BY d.driverid, "First Name", "Last Name"
ORDER BY "Fastest Lap Speed in Monaco" DESC;
5. Return the list of drivers and fastest lap speed of all time (no JOINS)
SELECT forename as First_Name,
surname as Last_Name,
(SELECT MAX(fastestlapspeed)
FROM results) as All_Time_Fastest_Lap_Speed
FROM drivers
LIMIT 10;
6. Return the list of drivers and their fastest lap speed in Monaco in 2021, as well as the fastest lap speed of all time
SELECT d.driverid
forename,
surname,
ROUND(MAX(fastestlapspeed), 2) as Fastest_Lap_Speed_in_Monaco_2021,
(SELECT MAX(fastestlapspeed)
FROM results) as All_Time_Fastest_Lap_Speed
FROM drivers as d
JOIN results as r
ON d.driverid = r.driverid
JOIN races as ra
ON r.raceid = ra.raceid
WHERE r.fastestlapspeed IS NOT NULL
AND ra.name LIKE '%Monaco%'
AND ra.year = 2021
GROUP BY d.driverid, forename, surname
ORDER BY Fastest_Lap_Speed_in_Monaco_2021 DESC;
7. Return the list of drivers, their fastest lap speed in Monaco in 2021, as well as how many times they have won in Monaco in total
SELECT driverid,
name,
SUM(wins) as num_of_wins
FROM driver_standings as ds
JOIN races as ra
ON ra.raceid = ds.raceid
WHERE name LIKE '%Monaco%'
GROUP BY driverid, name
ORDER BY num_of_wins DESC
LIMIT 15;
SELECT d.driverid,
forename,
surname,
w.num_of_wins,
MAX(r.fastestlapspeed) as Fastest_Lap_Speed_in_Monaco_2021
FROM drivers as d
JOIN results as r
ON d.driverid = r.driverid
JOIN races as ra
ON r.raceid = ra.raceid
JOIN (SELECT driverid,
name,
SUM(wins) as num_of_wins
FROM driver_standings as ds
JOIN races as ra
ON ra.raceid = ds.raceid
WHERE name LIKE '%Monaco%'
GROUP BY driverid, name
ORDER BY num_of_wins DESC) as w
ON d.driverid = w.driverid
WHERE fastestlapspeed IS NOT NULL
AND ra.year = 2021
AND ra.name LIKE '%Monaco%'
GROUP BY d.driverid, forename, surname, w.num_of_wins
ORDER BY Num_of_wins DESC, Fastest_Lap_Speed_in_Monaco_2021 DESC
LIMIT 15;
8. Return the list of drivers, their fastest lap speed in Monaco in 2021, as well as how many times they have won in Monaco before (use a CTE)
WITH Monaco_Wins as (SELECT driverid,
name,
SUM(wins) as num_of_wins
FROM driver_standings as ds
JOIN races as ra
ON ra.raceid = ds.raceid
WHERE name LIKE '%Monaco%'
GROUP BY driverid, name
ORDER BY num_of_wins DESC)
SELECT d.driverid,
d.forename,
d.surname,
mw.num_of_wins,
MAX(r.fastestlapspeed) as Fastest_Lap_Speed_in_Monaco_2021
FROM drivers as d
JOIN results as r
ON d.driverid = r.driverid
JOIN races as ra
ON r.raceid = ra.raceid
JOIN Monaco_Wins as mw
ON d.driverid = mw.driverid
WHERE fastestlapspeed IS NOT NULL
AND ra.year = 2021
AND ra.name LIKE '%Monaco%'
GROUP BY d.driverid, forename, surname, mw.num_of_wins
ORDER BY Num_of_wins DESC, Fastest_Lap_Speed_in_Monaco_2021 DESC;
9. Return the list of drivers, their fastest lap speeds in Monaco in 2021 and 2019 (use CTE)
WITH Monaco_2021 as (SELECT d.driverid,
d.forename,
d.surname,
MAX(r.fastestlapspeed) as Fastest_Lap_Speed_in_Monaco_2021
FROM drivers as d
JOIN results as r
ON d.driverid = r.driverid
JOIN races as ra
ON r.raceid = ra.raceid
WHERE r.fastestlapspeed IS NOT NULL
AND ra.name LIKE '%Monaco%'
AND ra.year = 2021
GROUP BY d.driverid, forename, surname
ORDER BY Fastest_Lap_Speed_in_Monaco_2021 DESC),
Monaco_2019 as (SELECT d.driverid,
d.forename,
d.surname,
MAX(r.fastestlapspeed) as Fastest_Lap_Speed_in_Monaco_2019
FROM drivers as d
JOIN results as r
ON d.driverid = r.driverid
JOIN races as ra
ON r.raceid = ra.raceid
WHERE r.fastestlapspeed IS NOT NULL
AND ra.name LIKE '%Monaco%'
AND ra.year = 2019
GROUP BY d.driverid, forename, surname
ORDER BY Fastest_Lap_Speed_in_Monaco_2019 DESC)
SELECT Monaco_2021.forename,
Monaco_2021.surname,
Fastest_Lap_Speed_in_Monaco_2019,
Fastest_Lap_Speed_in_Monaco_2021
FROM Monaco_2021
JOIN Monaco_2019
ON Monaco_2019.driverid = Monaco_2021.driverid
ORDER BY Fastest_Lap_Speed_in_Monaco_2019 DESC, Fastest_Lap_Speed_in_Monaco_2021 DESC;