season_27_matches: up_on_the_roof

23 rows

descending

Hero	Games	Wins	Losses	Winrate	Pickrate	Winrate_LowerBound_95CI
Gwendolin_Science	465	282	183	60.65%	10.13%	56.20%
PatFusty_Snowman	149	89	60	59.73%	3.24%	51.86%
Jericho_StarCaptain	70	44	26	62.86%	1.52%	51.54%
Adora	721	386	335	53.54%	15.70%	49.90%
Bonnie	701	360	341	51.36%	15.27%	47.66%
Benjamin	223	115	108	51.57%	4.86%	45.01%
Jericho	161	84	77	52.17%	3.51%	44.46%
Obyn_Ocean	124	65	59	52.42%	2.70%	43.63%
Etienne_Bee	237	118	119	49.79%	5.16%	43.42%
StrikerJones	47	26	21	55.32%	1.02%	41.11%
Ezili	68	36	32	52.94%	1.48%	41.08%
Gwendolin	213	100	113	46.95%	4.64%	40.25%
Adora_Fateweaver	33	18	15	54.55%	0.72%	37.56%
Quincy	220	97	123	44.09%	4.79%	37.53%
Obyn	88	42	46	47.73%	1.92%	37.29%
Churchill	133	57	76	42.86%	2.90%	34.45%
Etienne	571	216	355	37.83%	12.43%	33.85%
Ezili_SmudgeCat	34	17	17	50.00%	0.74%	33.19%
PatFusty	44	21	23	47.73%	0.96%	32.97%
Churchill_Sentai	109	46	63	42.20%	2.37%	32.93%
Quincy_Cyber	86	34	52	39.53%	1.87%	29.20%
Benjamin_DJ	41	18	23	43.90%	0.89%	28.71%
Jericho_Highwayman	39	17	22	43.59%	0.85%	28.03%

Advanced export

JSON shape: default, array, newline-delimited

CREATE VIEW up_on_the_roof_heroes AS 
WITH up_on_the_roof AS
    (SELECT *
    FROM matches
    WHERE map = 'up_on_the_roof')
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,
             Games,
             Wins,
             Losses,
             CAST(Wins AS REAL) / Games AS WR,
             CAST(Games AS REAL) / (
                 (SELECT COUNT(*)
                  FROM up_on_the_roof) * 2)    AS PR
      FROM (SELECT Hero,
                   SUM(Win) + SUM(NOT Win) AS Games,
                   SUM(Win)                AS Wins,
                   SUM(NOT Win)            AS Losses
            FROM (SELECT lHero             AS Hero,
                         playerLeftWin     AS Win
                  FROM up_on_the_roof
                  UNION ALL
                  SELECT rHero             AS Hero,
                         NOT playerLeftWin AS Win
                  FROM up_on_the_roof)
            GROUP BY Hero))
WHERE Wins >= 10 AND Losses >= 10 AND LENGTH(Winrate_LowerBound_95CI) = 6
ORDER BY Winrate_LowerBound_95CI DESC;

up_on_the_roof_heroes (view)

23 rows

Advanced export