summaryrefslogtreecommitdiff
path: root/views
diff options
context:
space:
mode:
authoremkael <emkael@tlen.pl>2017-03-26 23:12:04 +0200
committeremkael <emkael@tlen.pl>2017-03-26 23:12:04 +0200
commit5fb37e7fbf61b8a9a998fa42c948e417d5f94604 (patch)
tree4f27e2adfba3bcb6010190d294f724208e598e73 /views
parent5570c6c0583fdb4fdf13d8400206ba20d3a4755b (diff)
* champions views refactored
Diffstat (limited to 'views')
-rw-r--r--views/champions.sql36
1 files changed, 24 insertions, 12 deletions
diff --git a/views/champions.sql b/views/champions.sql
index f4fee6e..de547ec 100644
--- a/views/champions.sql
+++ b/views/champions.sql
@@ -1,24 +1,36 @@
+-- WDC standings at the end of each year
+DROP VIEW IF EXISTS driver_championship_results;
+CREATE VIEW driver_championship_results AS
+ SELECT * FROM driverStandings
+ WHERE raceId IN (
+ SELECT MAX(raceId) FROM races GROUP BY year
+ );
+
-- all drivers listed in the standings at position = 1
-- for last race of each season
DROP VIEW IF EXISTS world_drivers_champions;
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);
+ FROM driver_championship_results
+ JOIN races ON races.raceId = driver_championship_results.raceId
+ JOIN drivers ON driver_championship_results.driverId = drivers.driverId
+ WHERE driver_championship_results.position = 1;
+
+-- WCC standings at the end of each year
+DROP VIEW IF EXISTS constructor_championship_results;
+CREATE VIEW constructor_championship_results AS
+ SELECT * FROM constructorStandings
+ WHERE raceId IN (
+ SELECT MAX(raceId) FROM races GROUP BY year
+ );
-- all constructors listed in the standings at position = 1
-- for last race of each season
DROP VIEW IF EXISTS world_constructors_champions;
CREATE VIEW world_constructors_champions AS
SELECT constructors.*, races.year
- FROM constructorStandings
- JOIN races ON races.raceId = constructorStandings.raceId
+ FROM constructor_championship_results
+ JOIN races ON races.raceId = constructor_championship_results.raceId
JOIN constructors
- ON constructorStandings.constructorId = constructors.constructorId
- WHERE constructorStandings.raceId IN (
- SELECT MAX(raceId) FROM races GROUP BY year
- )
- AND (constructorStandings.position = 1);
+ ON constructor_championship_results.constructorId = constructors.constructorId
+ WHERE constructor_championship_results.position = 1;