doc/sql.txt


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

Useful queries:

 - selects top rating progression

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

 - overall top peak ratings

SELECT drivers.driver,
       rankings.ranking,
       rankings.rank_date
FROM rankings
INNER JOIN (
      SELECT MAX(ranking) ranking, _driver FROM rankings
      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

 - highest exit ratings

SELECT drivers.driver,
       rankings.ranking,
         rankings.rank_date
FROM rankings
INNER JOIN (
      SELECT MAX(rank_date) rank_date, _driver FROM rankings GROUP BY _driver
) 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

 - 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