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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
|
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;
- 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;
|
