1 files changed, 138 insertions, 0 deletions
diff --git a/ergast-views.sql b/ergast-views.sql
new file mode 100644
index 0000000..a3aa8b8
--- /dev/null
+++ b/ergast-views.sql
@@ -0,0 +1,138 @@
+-- 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);
+
+-- 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);
+
+-- 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,
+      SUM(teammates.d1position < teammates.d2position) wins,
+      SUM(teammates.d1position = teammates.d2position) draws,
+      SUM(teammates.d1position > teammates.d2position) 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;
+
+-- 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 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 cosntructors' positions for the former 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;
+
+CREATE VIEW podium_sequences AS
+   SELECT raceId, GROUP_CONCAT(driverId ORDER BY position ASC, driverId ASC) AS podium
+   FROM results
+   WHERE position IN (1,2,3)
+   GROUP BY raceId;
+
+CREATE VIEW podium_sets AS
+   SELECT raceId, GROUP_CONCAT(driverId ORDER BY driverId) AS podium
+   FROM results
+   WHERE position IN (1,2,3)
+   GROUP BY raceId;
+
+CREATE VIEW non_champion_nationality_podiums AS
+   SELECT * FROM races
+   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)
+   );