* oldest/youngest drivers views

2 files changed, 56 insertions, 0 deletions

diff --git a/views/README.md b/views/README.md
index 48825a9..9bcb901 100644
--- a/views/README.md
+++ b/views/README.md
@@ -110,3 +110,10 @@ A single view presenting double-WDC teammate pairings (for drivers with WDC titl
 [Example use](http://www.reddit.com/r/formula1/comments/2yfm53/ive_matured_says_hamilton_as_he_uses_ferrari_as_a/cp95w6y?context=1).
 
 Depends on both `champions.sql` (`world_drivers_champions` view) and `teammates.sql` (`teammates` view) being present in the database.
+
+`youngest_oldest.sql`
+---------------------
+
+A summary of youngest and oldest drivers entered in each season, along with combining it with drivers' first/last seasons.
+
+Note that this relies on birth dates for the drivers, which are a bit wonky in the original DB, but are corrected, as much as it's possible and reliable, in the [DB fixes](../import/10_fixes.sql).
diff --git a/views/oldest_youngest.sql b/views/oldest_youngest.sql
new file mode 100644
index 0000000..320ad6c
--- /dev/null
+++ b/views/oldest_youngest.sql
@@ -0,0 +1,49 @@
+-- first and last season (not full, any entry counts) for each driver
+CREATE VIEW first_last_season AS
+       SELECT MIN(races.year) AS first_season, MAX(races.year) AS last_season,
+              drivers.driverId AS driverId,
+              CONCAT(drivers.forename, ' ', drivers.surname) AS driver
+       FROM races
+            JOIN results ON results.raceId = races.raceId
+            JOIN drivers drivers.driverId = results.driverId
+       GROUP BY drivers.driverId;
+
+-- earliest and latest birth dates in each season among all the drivers
+-- entered in a season, so in 1991 Schumacher is the youngest,
+-- despite Hakkinen being younger than him on their respective debut races
+CREATE VIEW youngest_oldest_birth_dates_in_season AS
+       SELECT races.year AS year,
+              MIN(drivers.dob) AS oldest, MAX(drivers.dob) AS youngest
+       FROM results
+            JOIN drivers ON drivers.driverId = results.driverId
+            JOIN races ON results.raceId = races.raceId
+       GROUP BY races.year;
+
+-- youngest and oldest drivers in each season
+CREATE VIEW youngest_oldest_in_season AS
+       SELECT tmp.year AS year,
+              tmp.oldest AS oldest,
+              CONCAT(d1.forename, ' ', d1.surname) AS oldest_name,
+              tmp.youngest AS youngest,
+              CONCAT(d2.forename, ' ', d2.surname) AS youngest_name
+       FROM youngest_oldest_birth_dates_in_season tmp
+            JOIN drivers d1 ON d1.dob = tmp.oldest
+            JOIN drivers d2 ON d2.dob = tmp.youngest;
+
+-- list of drivers who were youngest in their debut season
+CREATE VIEW youngest_in_first_season AS
+       SELECT first_last_season.first_season AS first_season,
+              first_last_season.driver AS driver
+       FROM first_last_season
+            JOIN youngest_oldest_in_season
+                 ON youngest_oldest_in_season.year = first_last_season.first_season
+                    AND youngest_oldest_in_season.youngest_name = first_last_season.driver;
+
+-- list of drivers who were oldest in their last season
+CREATE VIEW oldest_in_last_season AS
+       SELECT first_last_season.last_season AS last_season,
+              first_last_season.driver AS driver
+       FROM first_last_season
+            JOIN youngest_oldest_in_season
+                 ON youngest_oldest_in_season.year = first_last_season.last_season
+                    AND youngest_oldest_in_season.oldest_name = first_last_season.driver;