* top ranking progression optimized

1 files changed, 21 insertions, 4 deletions

diff --git a/doc/sql.txt b/doc/sql.txt
index 3a67dd3..32e7fa3 100644
--- a/doc/sql.txt
+++ b/doc/sql.txt
@@ -2,12 +2,29 @@ Useful queries:
 
  - selects top rating progression
 
+CREATE OR REPLACE VIEW max_date_rankings AS
+       SELECT MAX(ranking) max_ranking,
+              rank_date max_rank_date
+       FROM rankings
+       GROUP BY rank_date;
+SELECT drivers.driver,
+       max_date_rankings.max_rank_date,
+       max_date_rankings.max_ranking
+FROM max_date_rankings
+INNER JOIN rankings ON (rankings.ranking = max_date_rankings.max_ranking)
+                    AND (rankings.rank_date = max_date_rankings.max_rank_date)
+LEFT JOIN drivers ON rankings._driver = drivers.id
+WHERE max_ranking > (
+      SELECT MAX(mr.max_ranking) FROM max_date_rankings mr
+      WHERE mr.max_rank_date < max_date_rankings.max_rank_date
+);
+
 SELECT * FROM rankings
 JOIN drivers ON rankings._driver = drivers.id
 WHERE ranking >= (
       SELECT MAX(r.ranking) FROM rankings r
       WHERE r.rank_date <= rankings.rank_date
-) ORDER BY rank_date ASC
+) ORDER BY rank_date ASC;
 
  - overall top peak ratings
 
@@ -20,7 +37,7 @@ INNER JOIN (
       GROUP BY _driver
 ) r ON r.ranking=rankings.ranking AND r._driver=rankings._driver
 JOIN drivers ON rankings._driver = drivers.id
-ORDER BY rankings.ranking DESC
+ORDER BY rankings.ranking DESC;
 
  - highest exit ratings
 
@@ -33,7 +50,7 @@ INNER JOIN (
 ) r ON r.rank_date=rankings.rank_date AND r._driver=rankings._driver
 JOIN drivers ON rankings._driver = drivers.id
 WHERE rankings.rank_date < CONCAT(YEAR(CURDATE()),'-01-01')
-ORDER BY rankings.ranking DESC
+ORDER BY rankings.ranking DESC;
 
  - year-by-year rating inflation
 
@@ -45,4 +62,4 @@ SELECT YEAR(rank_date),
        AVG(ranking)-STDDEV(ranking)
 FROM rankings
 GROUP BY YEAR(rank_date)
-ORDER BY YEAR(rank_date) ASC
+ORDER BY YEAR(rank_date) ASC;