1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
-- dead drivers count vs living drivers count for each race
DROP VIEW IF EXISTS dead_vs_living;
CREATE VIEW dead_vs_living AS
SELECT CONCAT(races.year, " ", races.name) race, races.date,
COUNT(debut_dates.driverId) overall,
SUM(drivers.dod IS NULL OR drivers.dod >= races.date) living,
SUM(drivers.dod < races.date) dead
FROM races
JOIN debut_dates ON debut_dates.date <= races.date
JOIN drivers ON debut_dates.driverId = drivers.driverId
GROUP BY races.raceId;
-- average debut age vs average lifespan for each race
DROP VIEW IF EXISTS debut_age_vs_lifespan;
CREATE VIEW debut_age_vs_lifespan AS
SELECT CONCAT(races.year, " ", races.name) race, races.date,
AVG(DATEDIFF(debut_dates.date, drivers.dob)) debut_age,
AVG(DATEDIFF(LEAST(races.date, COALESCE(drivers.dod, races.date)), drivers.dob)) lifespan
FROM races
JOIN debut_dates ON debut_dates.date <= races.date
JOIN drivers ON debut_dates.driverId = drivers.driverId
GROUP BY races.raceId;
|
