From e487a4338ac483a2374852ab8a26013a350c6626 Mon Sep 17 00:00:00 2001
From: emkael <emkael@tlen.pl>
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

(limited to '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

