diff options
| -rw-r--r-- | views/README.md | 2 | ||||
| -rw-r--r-- | views/podiums.sql | 10 |
2 files changed, 12 insertions, 0 deletions
diff --git a/views/README.md b/views/README.md index 5677eea..56e7562 100644 --- a/views/README.md +++ b/views/README.md @@ -65,6 +65,8 @@ The third view, `non_champion_nationality_podiums` , produces a complete lists o [The last one was created to answer a question on r/F1Statistics](http://www.reddit.com/r/F1Statistics/comments/2kb6z3/question_for_the_last_16_years_the_championship/clk2wsl?context=2), as it's often the case with these snippets. +There's also a summary view, `podium_count`, listing drivers by their podium count. + `teammates.sql` --------------- diff --git a/views/podiums.sql b/views/podiums.sql index ec0bf63..8054689 100644 --- a/views/podiums.sql +++ b/views/podiums.sql @@ -39,3 +39,13 @@ CREATE VIEW non_champion_nationality_podiums AS WHERE year < races.year ) AND position IN (1,2,3) ); + +-- driver list by podium count +DROP VIEW IF EXISTS podium_count; +CREATE VIEW podium_count AS + SELECT drivers.driverId, CONCAT(drivers.forename, ' ', drivers.surname) name, + COUNT(DISTINCT(results.raceId)) podium_count + FROM results JOIN drivers ON results.driverId = drivers.driverId + WHERE position IN (1,2,3) + GROUP BY results.driverId + ORDER BY COUNT(DISTINCT(results.raceId)) DESC; |