From e487a4338ac483a2374852ab8a26013a350c6626 Mon Sep 17 00:00:00 2001 From: emkael Date: Tue, 31 Mar 2015 10:32:44 +0200 Subject: * summary view of WDC teammates --- views/champion_teammates.sql | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) create mode 100644 views/champion_teammates.sql diff --git a/views/champion_teammates.sql b/views/champion_teammates.sql new file mode 100644 index 0000000..ac965ae --- /dev/null +++ b/views/champion_teammates.sql @@ -0,0 +1,17 @@ +-- summary of double WDC teammate pairings +-- requires: world_drivers_champions (champions.sql), +-- teammates (teammates.sql) +DROP VIEW IF EXISTS champion_champion_teammates; +CREATE VIEW champion_champion_teammates AS + SELECT races.year, CONCAT(wdc.forename, ' ', wdc.surname) driver1, + COUNT(DISTINCT(wdc.year)) wdc_count1, + CONCAT(wdc2.forename, ' ', wdc2.surname) driver2, + COUNT(DISTINCT(wdc2.year)) wdc_count2 + FROM teammates + JOIN races ON races.raceId = teammates.raceId + JOIN world_drivers_champions wdc + ON wdc.driverId = teammates.driver1 AND wdc.year < races.year + JOIN races r2 ON r2.raceId = teammates.raceId + JOIN world_drivers_champions wdc2 + ON wdc2.driverId = teammates.driver2 AND wdc2.year < races.year + GROUP BY races.year, wdc.forename, wdc.surname, wdc2.forename, wdc2.surname; -- cgit v1.2.3