diff options
| -rw-r--r-- | views/champions.sql | 36 |
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; |