* views for lifespan statistics

2 files changed, 34 insertions, 1 deletions

diff --git a/views/README.md b/views/README.md
index 2eee268..48825a9 100644
--- a/views/README.md
+++ b/views/README.md
@@ -22,7 +22,7 @@ Under the hood
 
 The convention for all the views is to use `underscored_lowercase_names` for views and newly added fields, to distinguish ergast-goodies input from the original structure (which uses `camelCase` naming).
 
-The views are split into files, which are fully independent, i.e. refer only to the original tables (or to each other, but only in order they're defined within the file), with the exception of `champion_teammates.sql`, which depends on both `champions.sql` and `teammates.sql`.
+The views are split into files, which are fully independent, i.e. refer only to the original tables (or to each other, but only in order they're defined within the file), with the exception of `champion_teammates.sql`, which depends on both `champions.sql` and `teammates.sql` and `dead_vs_living.sql`, which depends on `career_length.sql` and [`11_death_dates`](../import/11_death_dates.sql) extension.
 
 `career_length.sql`
 -------------------
@@ -45,6 +45,17 @@ Two views resulting in a list of World Drivers' Champions and World Constructors
 
 Views assume ascending chronological order of raceId values for races in each season, and could be improved (but probably with a drop of performance) to select "last race of the season" (for which the standings are taken) by date.
 
+`dead_vs_living.sql`
+--------------------
+
+Two views showing stats regarding driver lifespan.
+
+`dead_vs_living` counts the tallies of dead drivers vs. living drivers, at the time of each race date.
+
+`debut_age_vs_lifespan` shows average driver age (current age for living drivers, age of death for dead) vs. average debut age for each race date.
+
+Both views require importing death dates from the [`11_death_dates.sql`](../import/11_death_dates.sql) extension, as well as `debut_age` view from `career_length.sql`.
+
 `full_season_drivers.sql`
 -------------------------
 
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;