1 files changed, 22 insertions, 0 deletions
diff --git a/views/dead_vs_living.sql b/views/dead_vs_living.sql
new file mode 100644
index 0000000..fd1c1d7
--- /dev/null
+++ b/views/dead_vs_living.sql
@@ -0,0 +1,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;