* useful views, separated by theme

5 files changed, 152 insertions, 0 deletions

diff --git a/views/champions.sql b/views/champions.sql
new file mode 100644
index 0000000..d8e4d18
--- /dev/null
+++ b/views/champions.sql
@@ -0,0 +1,22 @@
+-- all drivers listed in the standings at position = 1
+-- for last race of each season
+CREATE VIEW world_drivers_champions AS
+   SELECT drivers.*, races.year
+   FROM driverStandings
+      JOIN races ON races.raceId = driverStandings.raceId
+      JOIN drivers ON driverStandings.driverId = drivers.driverId
+   WHERE driverStandings.raceId IN (SELECT MAX(raceId) FROM races GROUP BY year)
+      AND (driverStandings.position = 1);
+
+-- all constructors listed in the standings at position = 1
+-- for last race of each season
+CREATE VIEW world_constructors_champions AS
+   SELECT constructors.*, races.year
+   FROM constructorStandings
+      JOIN races ON races.raceId = constructorStandings.raceId
+      JOIN constructors
+         ON constructorStandings.constructorId = constructors.constructorId
+   WHERE constructorStandings.raceId IN (
+         SELECT MAX(raceId) FROM races GROUP BY year
+      )
+      AND (constructorStandings.position = 1);
diff --git a/views/full_season_drivers.sql b/views/full_season_drivers.sql
new file mode 100644
index 0000000..ae36cc8
--- /dev/null
+++ b/views/full_season_drivers.sql
@@ -0,0 +1,30 @@
+-- view holds distinct driver-race entries
+-- (eliminating duplicate race entries from shared drives)
+CREATE VIEW driver_races AS
+   SELECT driverId, raceId
+   FROM results
+   GROUP BY driverId, raceId;
+
+-- view aggregates races by season for each driver
+CREATE VIEW driver_season_races AS
+   SELECT driver_races.driverId, races.year,
+      GROUP_CONCAT(driver_races.raceId
+         ORDER BY driver_races.raceId ASC)
+      AS races
+   FROM driver_races
+      JOIN races ON races.raceId = driver_races.raceId
+   GROUP BY driver_races.driverId, races.year;
+
+-- view aggregates races by season
+CREATE VIEW season_races AS
+   SELECT year, GROUP_CONCAT(raceId ORDER BY raceId ASC) AS races
+   FROM races
+   GROUP BY year;
+
+-- selecting only driver-season pairs with all races in a season
+CREATE VIEW full_season_drivers AS
+   SELECT driver_season_races.driverId, driver_season_races.year
+   FROM season_races
+      JOIN driver_season_races
+         ON (season_races.races = driver_season_races.races)
+            AND (season_races.year = driver_season_races.year);
diff --git a/views/podiums.sql b/views/podiums.sql
new file mode 100644
index 0000000..6e677da
--- /dev/null
+++ b/views/podiums.sql
@@ -0,0 +1,38 @@
+-- podium for each race, in order of finishing position

+CREATE VIEW podium_sequences AS

+   SELECT races.*,

+      GROUP_CONCAT(CONCAT(drivers.forename, " ", drivers.surname)

+         ORDER BY results.position ASC, results.driverId ASC)

+      AS podium

+   FROM results

+   JOIN races ON results.raceId = races.raceId

+   JOIN drivers ON results.driverId = drivers.driverId

+   WHERE results.position IN (1,2,3)

+   GROUP BY results.raceId;

+

+-- podium for each race, in alphabetical order

+CREATE VIEW podium_sets AS

+   SELECT races.*,

+      GROUP_CONCAT(CONCAT(drivers.forename, " ", drivers.surname)

+         ORDER BY drivers.surname ASC, drivers.forename ASC,

+            drivers.driverRef ASC)

+      AS podium

+   FROM results

+   JOIN races ON results.raceId = races.raceId

+   JOIN drivers ON results.driverId = drivers.driverId

+   WHERE results.position IN (1,2,3)

+   GROUP BY results.raceId;

+

+-- races in which the entire podium consisted of driver of nationality

+-- yet to produce a drivers' world champion

+CREATE VIEW non_champion_nationality_podiums AS

+   SELECT raceId, year, name, podium FROM podium_sequences

+   WHERE raceId NOT IN (

+      SELECT results.raceId FROM results

+         JOIN drivers ON drivers.driverId = results.driverId

+         JOIN races ON races.raceId = results.raceId

+         WHERE drivers.nationality IN (

+            SELECT nationality FROM world_drivers_champions

+            WHERE year < races.year

+         ) AND position IN (1,2,3)

+   );

diff --git a/views/team_switches.sql b/views/team_switches.sql
new file mode 100644
index 0000000..2514911
--- /dev/null
+++ b/views/team_switches.sql
@@ -0,0 +1,34 @@
+-- constructors' standings at the end of each season
+CREATE VIEW constructor_championship_results AS
+   SELECT * FROM constructorStandings
+   WHERE raceId IN (
+      SELECT MAX(raceId) FROM races GROUP BY year
+   );
+
+-- every driver-constructor pair for each season
+CREATE VIEW season_driver_constructor AS
+   SELECT results.driverId, results.constructorId, races.year
+   FROM results
+      JOIN races ON races.raceId = results.raceId
+   GROUP BY results.driverId, results.constructorId, races.year;
+
+-- every team each driver would go to drive for in the following year
+CREATE VIEW next_season_driver_constructor AS
+   SELECT sdc.*, sdc2.constructorId AS nextConstructor
+   FROM season_driver_constructor sdc
+      LEFT JOIN season_driver_constructor sdc2
+         ON (sdc.year = (sdc2.year - 1)) AND (sdc.driverId = sdc2.driverId);
+
+-- every driver team switch with both cosntructors' positions
+-- for the initial season
+CREATE VIEW driver_team_switches AS
+   SELECT nsdc.driverId, nsdc.year, nsdc.constructorId, nsdc.nextConstructor,
+      cs.position AS prevPosition, cs2.position AS nextPosition
+   FROM next_season_driver_constructor nsdc
+      JOIN constructor_championship_results cs
+         ON nsdc.constructorId = cs.constructorId
+      JOIN races ON (cs.raceId = races.raceId) AND (races.year = nsdc.year)
+      JOIN constructor_championship_results cs2
+         ON nsdc.nextConstructor = cs2.constructorId
+      JOIN races r2 ON (r2.year = nsdc.year) and (r2.raceId = cs2.raceId)
+   WHERE nsdc.constructorId <> nsdc.nextConstructor;
diff --git a/views/teammates.sql b/views/teammates.sql
new file mode 100644
index 0000000..772d59d
--- /dev/null
+++ b/views/teammates.sql
@@ -0,0 +1,28 @@
+-- all driver pairs listed in the results as driving for the same constructor
+-- in the same race, for every race
+CREATE VIEW teammates AS
+   SELECT r1.raceId, r1.constructorId,
+      r1.driverId driver1, r1.position d1position,
+      r1.positionText d1posText, r1.statusId d1status,
+      r2.driverId driver2, r2.position d2position,
+      r2.positionText d2posText, r2.statusId d2status
+   FROM results r1
+      JOIN results r2
+         ON r1.raceId = r2.raceId
+         AND r1.constructorId = r2.constructorId
+         AND r1.driverId <> r2.driverId;
+
+-- tally of results from the previous view
+CREATE VIEW teammate_tally AS
+   SELECT CONCAT(d1.forename, " ", d1.surname) driver,
+      COALESCE(SUM(teammates.d1position < teammates.d2position), 0) wins,
+      COALESCE(SUM(teammates.d1position = teammates.d2position), 0) draws,
+      COALESCE(SUM(teammates.d1position > teammates.d2position), 0) loses,
+      CONCAT(d2.forename, " ", d2.surname) against,
+      SUM(teammates.d1position IS NOT NULL
+          AND teammates.d2position IS NOT NULL) compare_count,
+      COUNT(teammates.raceId) overall_count
+   FROM teammates
+      JOIN drivers d1 ON d1.driverId = teammates.driver1
+      JOIN drivers d2 ON d2.driverId = teammates.driver2
+   GROUP BY teammates.driver1, teammates.driver2;