diff options
Diffstat (limited to 'views')
| -rw-r--r-- | views/README.md | 7 | ||||
| -rw-r--r-- | views/oldest_youngest.sql | 49 |
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; |