test_heroes_game_length (view)
24 rows
This data as json, CSV (advanced)
| Hero | Games | Wins | Losses | Winrate | Pickrate | Winrate_LowerBound_95CI | 1-13_Games | 1-13_WR_LB_95CI | 14-25_Games | 14-25_WR_LB_95CI | 26+_Games | 26+_WR_LB_95CI |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| PatFusty_Snowman | 27981 | 16664 | 11317 | 59.55% | 1.12% | 58.98% | 10343 | 59.52% | 11955 | 60.31% | 6221 | 52.74% |
| Adora_Fateweaver | 9750 | 5536 | 4214 | 56.78% | 0.39% | 55.80% | 5086 | 59.25% | 4112 | 52.03% | 641 | 41.08% |
| Jericho | 61336 | 34238 | 27098 | 55.82% | 2.44% | 55.43% | 33238 | 54.54% | 23908 | 56.02% | 4848 | 55.87% |
| Adora | 36037 | 19339 | 16698 | 53.66% | 1.44% | 53.15% | 18121 | 56.62% | 13386 | 47.73% | 4876 | 52.62% |
| Quincy_Cyber | 54937 | 29399 | 25538 | 53.51% | 2.19% | 53.10% | 27247 | 57.54% | 23221 | 49.80% | 5149 | 40.52% |
| Jericho_StarCaptain | 13645 | 7239 | 6406 | 53.05% | 0.54% | 52.22% | 7801 | 53.68% | 5231 | 51.58% | 717 | 30.83% |
| Jericho_Highwayman | 15322 | 8028 | 7294 | 52.40% | 0.61% | 51.60% | 8341 | 51.39% | 5993 | 52.92% | 1126 | 39.74% |
| Etienne_Bee | 71193 | 36227 | 34966 | 50.89% | 2.84% | 50.52% | 30886 | 47.16% | 28723 | 53.36% | 12749 | 51.24% |
| Obyn | 34409 | 17403 | 17006 | 50.58% | 1.37% | 50.05% | 16458 | 46.44% | 13074 | 51.69% | 5287 | 55.56% |
| Churchill_Sentai | 33372 | 16831 | 16541 | 50.43% | 1.33% | 49.90% | 15422 | 51.41% | 14052 | 48.16% | 4327 | 46.79% |
| Ezili | 20685 | 10430 | 10255 | 50.42% | 0.82% | 49.74% | 8977 | 48.28% | 8724 | 50.68% | 3287 | 49.22% |
| Benjamin | 27035 | 13568 | 13467 | 50.19% | 1.08% | 49.59% | 11859 | 44.96% | 11338 | 50.62% | 4228 | 58.15% |
| StrikerJones | 10768 | 5435 | 5333 | 50.47% | 0.43% | 49.53% | 4033 | 50.38% | 4831 | 47.77% | 2096 | 49.29% |
| StrikerJones_Biker | 10892 | 5462 | 5430 | 50.15% | 0.43% | 49.21% | 4317 | 44.75% | 4666 | 52.30% | 2103 | 48.74% |
| Quincy | 58535 | 29023 | 29512 | 49.58% | 2.33% | 49.18% | 30883 | 52.27% | 23530 | 47.36% | 4725 | 33.77% |
| Ezili_SmudgeCat | 5779 | 2866 | 2913 | 49.59% | 0.23% | 48.30% | 2444 | 45.32% | 2470 | 50.14% | 930 | 46.79% |
| Bonnie | 86768 | 41718 | 45050 | 48.08% | 3.46% | 47.75% | 29999 | 46.67% | 36875 | 48.12% | 21383 | 47.78% |
| Gwendolin_Science | 44658 | 21272 | 23386 | 47.63% | 1.78% | 47.17% | 16400 | 40.28% | 17293 | 45.30% | 11809 | 58.52% |
| Etienne | 131052 | 61053 | 69999 | 46.59% | 5.22% | 46.32% | 59622 | 47.13% | 53839 | 45.84% | 19599 | 43.69% |
| PatFusty | 9819 | 4623 | 5196 | 47.08% | 0.39% | 46.09% | 4171 | 44.83% | 3930 | 45.56% | 1889 | 45.97% |
| Churchill | 25852 | 11699 | 14153 | 45.25% | 1.03% | 44.65% | 12313 | 43.39% | 10418 | 46.65% | 3489 | 39.41% |
| Benjamin_DJ | 6304 | 2801 | 3503 | 44.43% | 0.25% | 43.21% | 2715 | 33.89% | 2238 | 44.63% | 1439 | 55.62% |
| Obyn_Ocean | 15137 | 6552 | 8585 | 43.28% | 0.60% | 42.50% | 6002 | 36.51% | 5904 | 41.47% | 3472 | 52.81% |
| Gwendolin | 25048 | 10751 | 14297 | 42.92% | 1.00% | 42.31% | 10996 | 39.78% | 9853 | 42.05% | 4610 | 46.45% |
Advanced export
JSON shape: default, array, newline-delimited
CREATE VIEW test_heroes_game_length AS
WITH Heroes AS (SELECT lHero AS Hero,
playerLeftWin AS Win,
endRound
FROM matches
UNION ALL
SELECT rHero AS Hero,
NOT playerLeftWin AS Win,
endRound
FROM matches)
SELECT Hero,
Games,
Wins,
Losses,
Winrate,
Pickrate,
Winrate_LowerBound_95CI,
"1-13_Games",
"1-13_WR_LB_95CI",
"14-25_Games",
"14-25_WR_LB_95CI",
"26+_Games",
"26+_WR_LB_95CI"
FROM (SELECT at.Hero,
at.Games AS Games,
at.Wins AS Wins,
at.Losses AS Losses,
at.Winrate AS Winrate,
at.Pickrate AS Pickrate,
at.Winrate_LowerBound_95CI AS Winrate_LowerBound_95CI,
seg13.Games AS "1-13_Games",
seg13.Winrate_LowerBound_95CI AS "1-13_WR_LB_95CI",
seg14_25.Games AS "14-25_Games",
seg14_25.Winrate_LowerBound_95CI AS "14-25_WR_LB_95CI",
seg26plus.Games AS "26+_Games",
seg26plus.Winrate_LowerBound_95CI AS "26+_WR_LB_95CI"
FROM (SELECT Hero,
Games,
Wins,
Losses,
printf('%.2f%%', WR * 100) AS Winrate,
printf('%.2f%%', PR * 100) AS Pickrate,
printf('%.2f%%', (WR - 1.96 * SQRT((WR * (1 - WR)) / Games)) * 100) AS Winrate_LowerBound_95CI
FROM (SELECT Hero,
SUM(Win) + SUM(NOT Win) AS Games,
SUM(Win) AS Wins,
SUM(NOT Win) AS Losses,
CAST(SUM(Win) AS REAL) / (SUM(Win) + SUM(NOT Win)) AS WR,
CAST((SUM(Win) + SUM(NOT Win)) AS REAL) / (
(SELECT COUNT(*)
FROM matches) * 6) AS PR
FROM Heroes
GROUP BY Hero)
WHERE Wins >= 10
AND Losses >= 10
AND LENGTH(Winrate_LowerBound_95CI) = 6) AS at
LEFT JOIN
(SELECT Hero,
Games,
Wins,
Losses,
printf('%.2f%%', WR * 100) AS Winrate,
printf('%.2f%%', (WR - 1.96 * SQRT((WR * (1 - WR)) / Games)) * 100) AS Winrate_LowerBound_95CI
FROM (SELECT Hero,
SUM(Win) + SUM(NOT Win) AS Games,
SUM(Win) AS Wins,
SUM(NOT Win) AS Losses,
CAST(SUM(Win) AS REAL) / (SUM(Win) + SUM(NOT Win)) AS WR
FROM Heroes
WHERE endRound <= 13
GROUP BY Hero)
WHERE Wins >= 10
AND Losses >= 10
AND LENGTH(Winrate_LowerBound_95CI) = 6) AS seg13 ON at.Hero = seg13.Hero
LEFT JOIN
(SELECT Hero,
Games,
Wins,
Losses,
printf('%.2f%%', WR * 100) AS Winrate,
printf('%.2f%%', (WR - 1.96 * SQRT((WR * (1 - WR)) / Games)) * 100) AS Winrate_LowerBound_95CI
FROM (SELECT Hero,
SUM(Win) + SUM(NOT Win) AS Games,
SUM(Win) AS Wins,
SUM(NOT Win) AS Losses,
CAST(SUM(Win) AS REAL) / (SUM(Win) + SUM(NOT Win)) AS WR
FROM Heroes
WHERE endRound BETWEEN 14 AND 26
GROUP BY Hero)
WHERE Wins >= 10
AND Losses >= 10
AND LENGTH(Winrate_LowerBound_95CI) = 6) AS seg14_25 ON at.Hero = seg14_25.Hero
LEFT JOIN
(SELECT Hero,
Games,
Wins,
Losses,
printf('%.2f%%', WR * 100) AS Winrate,
printf('%.2f%%', (WR - 1.96 * SQRT((WR * (1 - WR)) / Games)) * 100) AS Winrate_LowerBound_95CI
FROM (SELECT Hero,
SUM(Win) + SUM(NOT Win) AS Games,
SUM(Win) AS Wins,
SUM(NOT Win) AS Losses,
CAST(SUM(Win) AS REAL) / (SUM(Win) + SUM(NOT Win)) AS WR
FROM Heroes
WHERE endRound >= 26
GROUP BY Hero)
WHERE Wins >= 10
AND Losses >= 10
AND LENGTH(Winrate_LowerBound_95CI) = 6) AS seg26plus ON at.Hero = seg26plus.Hero)
ORDER BY Winrate_LowerBound_95CI DESC;