summaryrefslogtreecommitdiff
path: root/views
diff options
context:
space:
mode:
authoremkael <emkael@tlen.pl>2015-04-01 11:47:37 +0200
committeremkael <emkael@tlen.pl>2015-04-01 11:47:37 +0200
commitf1b4ce11d35ed9fcc254b365fa2b9ae3a908c027 (patch)
tree708cd60a8af49d8504e514593db4a92d61cb12cf /views
parent4a48faa9e048d07211763a538c350b504162d42c (diff)
* podium count view for drivers
Diffstat (limited to 'views')
-rw-r--r--views/README.md2
-rw-r--r--views/podiums.sql10
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;