From b6fb6d2788897ebb393b07eec26e744052159f2a Mon Sep 17 00:00:00 2001
From: emkael <emkael@tlen.pl>
Date: Sun, 23 Nov 2014 18:04:48 +0100
Subject:  * some SQLs and ticking off SQL coverage for chart/table ideas

---
 doc/ideas.txt | 22 +++++++-------
 doc/sql.md    | 96 ++++++++++++++++++++++++++++++++++++++++++++++++++++-------
 2 files changed, 97 insertions(+), 21 deletions(-)

(limited to 'doc')

diff --git a/doc/ideas.txt b/doc/ideas.txt
index d79957e..ae0b701 100644
--- a/doc/ideas.txt
+++ b/doc/ideas.txt
@@ -1,13 +1,13 @@
 Tables:
- - all-time peak ratings
- - top exit ratings (date < 2014)
- - peak rating progression
- - highest/lowest ranked podiums
- - races with biggest changes
- - highest rating at the end of rookie season (or next season if < 3 races in rookie season)
+ + all-time peak ratings
+ + top exit ratings (date < 2014)
+ + peak rating progression
+ + year-by-year top peak/average ratings
+ + highest/lowest ranked podiums
+ + races with biggest changes
+ + highest rating at the end of rookie season (= first season with at least 7 sessions) / highest average rating over rookie season
 Charts:
- - rolling average and deviation of rankings (average of averages groupped by driver)
- - average ranking at the beginning of each championship season (for first championship season race grid)
- - average ranking at the end of each championship season (for drivers from championship races)
- - peak season ranking (MAX, MIN, AVG, STDDEV GROUP BY YEAR(date))
- - top 5 of every season (on average) by decade
+ + rolling average and deviation of rankings (average of averages groupped by driver)
+ + average ranking at the beginning and the end of each championship season (first and last championship races)
+ + peak season ranking (MAX peak rating, MIN bottom rating, AVG average, STDDEV average GROUP BY YEAR(date), _driver)
+ + top 3 of every season (on average) by half-decade
diff --git a/doc/sql.md b/doc/sql.md
index 1f4f08b..293fe94 100644
--- a/doc/sql.md
+++ b/doc/sql.md
@@ -43,7 +43,7 @@ ORDER BY rankings.ranking DESC;
 ```
 SELECT drivers.driver,
        rankings.ranking,
-         rankings.rank_date
+       rankings.rank_date
 FROM rankings
 INNER JOIN (
       SELECT MAX(rank_date) rank_date, _driver FROM rankings GROUP BY _driver
@@ -56,13 +56,89 @@ ORDER BY rankings.ranking DESC;
  * year-by-year rating inflation
 
 ```
-SELECT YEAR(rank_date),
-       MAX(ranking),
-       MIN(ranking),
-       AVG(ranking),
-       AVG(ranking)+STDDEV(ranking),
-       AVG(ranking)-STDDEV(ranking)
-FROM rankings
-GROUP BY YEAR(rank_date)
-ORDER BY YEAR(rank_date) ASC;
+CREATE OR REPLACE VIEW driver_yearly_rankings AS
+    SELECT MAX(rankings.ranking) max_ranking,
+           AVG(rankings.ranking) avg_ranking,
+           MIN(rankings.ranking) min_ranking,
+           YEAR(rankings.rank_date) date, COUNT(rankings.id) count,
+           championship.position, rankings._driver
+    FROM rankings
+    LEFT OUTER JOIN championship ON rankings._driver = championship._driver
+                                 AND YEAR(rankings.rank_date) = championship.year
+    GROUP BY YEAR(rankings.rank_date), rankings._driver;
+
+SELECT date,
+       MAX(max_ranking),
+       MIN(min_ranking),
+       AVG(avg_ranking),
+       AVG(avg_ranking)+STDDEV(avg_ranking),
+       AVG(avg_ranking)-STDDEV(avg_ranking)
+FROM driver_yearly_rankings
+GROUP BY date
+ORDER BY date ASC;
 ```
+
+
+    CREATE OR REPLACE VIEW top_yearly_rankings AS
+        SELECT MAX(max_ranking) peak, MAX(avg_ranking) average, date
+        FROM driver_yearly_rankings
+        WHERE count > 10
+        GROUP BY date;
+
+    CREATE OR REPLACE VIEW top_peak_rankings AS
+        SELECT top_yearly_rankings.date, drivers.driver, rankings.ranking, rankings.rank_date,
+               driver_yearly_rankings.position
+        FROM rankings
+        JOIN top_yearly_rankings ON YEAR(rankings.rank_date) = top_yearly_rankings.date
+                                 AND rankings.ranking = top_yearly_rankings.peak
+        JOIN drivers ON rankings._driver = drivers.id
+        JOIN driver_yearly_rankings ON rankings._driver = driver_yearly_rankings._driver
+                                    AND top_yearly_rankings.date = driver_yearly_rankings.date
+        GROUP BY top_yearly_rankings.date;
+
+    CREATE OR REPLACE VIEW top_average_rankings AS
+        SELECT top_yearly_rankings.date, drivers.driver,
+               top_yearly_rankings.average, driver_yearly_rankings.position
+        FROM top_yearly_rankings
+        JOIN driver_yearly_rankings ON driver_yearly_rankings.avg_ranking = top_yearly_rankings.average
+                                    AND driver_yearly_rankings.date = top_yearly_rankings.date
+        JOIN drivers ON driver_yearly_rankings._driver = drivers.id
+        WHERE driver_yearly_rankings.count > 10;
+
+    CREATE OR REPLACE VIEW champions AS
+        SELECT championship.year, drivers.driver, championship.points
+        FROM championship
+        JOIN drivers ON drivers.id = championship._driver
+        WHERE position = 1;
+
+    CREATE OR REPLACE VIEW driver_seasons_count AS
+        SELECT YEAR(rank_date) year, _driver, COUNT(rank_date) count
+        FROM rankings
+        GROUP BY _driver, YEAR(rank_date);
+
+    CREATE OR REPLACE VIEW rookie_seasons AS
+        SELECT MIN(year) year, _driver
+        FROM driver_seasons_count
+        WHERE count > 6
+        GROUP BY _driver;
+
+    SELECT *
+    FROM races
+    JOIN (
+        SELECT MIN(date) min_date
+        FROM races
+        WHERE _type = 3
+        GROUP BY YEAR(date)
+    ) m ON m.min_date = races.date
+    WHERE _type = 3;
+
+    SELECT *
+    FROM races
+    JOIN (
+        SELECT MAX(date) max_date
+        FROM races
+        WHERE _type = 4
+        GROUP BY YEAR(date)
+    ) m ON m.max_date = races.date
+    WHERE _type = 4;
+
-- 
cgit v1.2.3