summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--ergast-views.sql138
-rw-r--r--views/champions.sql22
-rw-r--r--views/full_season_drivers.sql30
-rw-r--r--views/podiums.sql38
-rw-r--r--views/team_switches.sql34
-rw-r--r--views/teammates.sql28
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;