From 0f0025d1aed469c4e15e601a28506e7ac28d5569 Mon Sep 17 00:00:00 2001
From: emkael <emkael@tlen.pl>
Date: Thu, 28 Apr 2016 16:17:47 +0200
Subject:  * career length view altered to use helper view for first and last
 race

---
 views/README.md         |  2 ++
 views/career_length.sql | 29 +++++++++++++++++++++++++----
 2 files changed, 27 insertions(+), 4 deletions(-)

(limited to 'views')

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;
-- 
cgit v1.2.3