* podium count view for drivers

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;