diff options
| -rw-r--r-- | views/champion_teammates.sql | 17 |
1 files changed, 17 insertions, 0 deletions
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; |