summaryrefslogtreecommitdiff
path: root/views
diff options
context:
space:
mode:
Diffstat (limited to 'views')
-rw-r--r--views/README.md2
-rw-r--r--views/career_length.sql29
2 files changed, 27 insertions, 4 deletions
diff --git a/views/README.md b/views/README.md
index 56e7562..2eee268 100644
--- a/views/README.md
+++ b/views/README.md
@@ -36,6 +36,8 @@ Simple view which lists career length stats for each driver:
[You can use these to produce such stats, for example.](http://www.reddit.com/r/formula1/comments/2xr3tw/who_do_you_think_will_get_their_first_win_next/cp2sjp2?context=1)
+Based on two other helper views: `debut_dates` and `last_race_dates`, listing race dates for each drivers' first and last entry.
+
`champions.sql`
---------------
diff --git a/views/career_length.sql b/views/career_length.sql
index e950f32..9f59797 100644
--- a/views/career_length.sql
+++ b/views/career_length.sql
@@ -1,10 +1,31 @@
+-- debut date for every driver (date of a race for the event driver was first entered)
+DROP VIEW IF EXISTS debut_dates;
+CREATE VIEW debut_dates AS
+ SELECT drivers.driverId, drivers.driverRef, MIN(races.date) AS `date`
+ FROM drivers
+ JOIN results ON results.driverId = drivers.driverId
+ JOIN races ON races.raceId = results.raceId
+ GROUP BY drivers.driverId;
+
+-- last race date for every driver (date of a race for the event driver was last entered)
+DROP VIEW IF EXISTS last_race_dates;
+CREATE VIEW last_race_dates AS
+ SELECT drivers.driverId, drivers.driverRef, MAX(races.date) AS `date`
+ FROM drivers
+ JOIN results ON results.driverId = drivers.driverId
+ JOIN races ON races.raceId = results.raceId
+ GROUP BY drivers.driverId;
+
+-- carrer length (in days) for each driver
+DROP VIEW IF EXISTS career_length;
CREATE VIEW career_length AS
SELECT drivers.driverId,
COUNT(DISTINCT(results.raceId)) AS career_races,
- DATEDIFF(MAX(races.date), MIN(races.date)) + 1 AS career_days,
- DATEDIFF(MIN(races.date), drivers.dob) AS first_race_age,
- DATEDIFF(MAX(races.date), drivers.dob) AS last_race_age
+ DATEDIFF(last_race_dates.date, debut_dates.date) + 1 AS career_days,
+ DATEDIFF(debut_dates.date, drivers.dob) AS first_race_age,
+ DATEDIFF(last_race_dates.date, drivers.dob) AS last_race_age
FROM results
- JOIN races ON races.raceId = results.raceId
JOIN drivers ON drivers.driverId = results.driverId
+ JOIN debut_dates ON drivers.driverId = debut_dates.driverId
+ JOIN last_race_dates ON drivers.driverId = last_race_dates.driverId
GROUP BY results.driverId;