* useful views, separated by theme

6 files changed, 152 insertions, 138 deletions

diff --git a/ergast-views.sql b/ergast-views.sql
deleted file mode 100644
index a3aa8b8..0000000
--- a/ergast-views.sql
+++ /dev/null
@@ -1,138 +0,0 @@
--- 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)

-   );

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;