Classic Tetris Analysis

I discovered competitive Classic Tetris in late 2021 and immediately got hooked.

I’ve wondered who the most consistent players are overall, and the results surprised me a bit.

Examining Classic Tetris competitive data and filtering in SQL (BigQuery) gave me insights into how many wins each player has and the number of games they win in overall matches (you need to win three games to win a match). Finally, I assembled all of this into a Tableau dashboard to show how dominant Alex T. really is in Classic Tetris. The top 5 noted stay static in terms of sheer results.

%%html
<div class='tableauPlaceholder' id='viz1678043157228' style='position: relative'><noscript><a href='#'><img alt='Most Consistent Classic Tetris Players ' src='https:&#47;&#47;public.tableau.com&#47;static&#47;images&#47;Cl&#47;ClassicTetrisAnalysisv2&#47;MostConsistentClassicTetrisPlayers&#47;1_rss.png' style='border: none' /></a></noscript><object class='tableauViz'  style='display:none;'><param name='host_url' value='https%3A%2F%2Fpublic.tableau.com%2F' /> <param name='embed_code_version' value='3' /> <param name='site_root' value='' /><param name='name' value='ClassicTetrisAnalysisv2&#47;MostConsistentClassicTetrisPlayers' /><param name='tabs' value='no' /><param name='toolbar' value='yes' /><param name='static_image' value='https:&#47;&#47;public.tableau.com&#47;static&#47;images&#47;Cl&#47;ClassicTetrisAnalysisv2&#47;MostConsistentClassicTetrisPlayers&#47;1.png' /> <param name='animate_transition' value='yes' /><param name='display_static_image' value='yes' /><param name='display_spinner' value='yes' /><param name='display_overlay' value='yes' /><param name='display_count' value='yes' /><param name='language' value='en-US' /></object></div>                <script type='text/javascript'>                    var divElement = document.getElementById('viz1678043157228');                    var vizElement = divElement.getElementsByTagName('object')[0];                    if ( divElement.offsetWidth > 800 ) { vizElement.style.width='100%';vizElement.style.height=(divElement.offsetWidth*0.75)+'px';} else if ( divElement.offsetWidth > 500 ) { vizElement.style.width='100%';vizElement.style.height=(divElement.offsetWidth*0.75)+'px';} else { vizElement.style.width='100%';vizElement.style.height='1277px';}                     var scriptElement = document.createElement('script');                    scriptElement.src = 'https://public.tableau.com/javascripts/api/viz_v1.js';                    vizElement.parentNode.insertBefore(scriptElement, vizElement);                </script>

Comments on Process and Data Cleaning

Data obtained from publicly available Classic Tetris Masters ranking data found at https://docs.google.com/spreadsheets/d/1NUwqOotckIdSRH2FdfhBRHPU84N6EaRBM9BDkx5nDkU/edit#gid=1685146231.

I inspected data and found it to be mostly in good order. The following cleaning was to make things easier to follow and for my preference, mostly.

Cleaning steps were as follows:

• Navigate to 'Matches' sheet.
• Rename cells 'HW' and 'AW', symbolizing home and away wins to be 'p1w' and 'p2w' respectively. There is no "home" and "away" in Tetris, so this was an OCD change for me.
• Split cells B1:E1
• Name cells B1, C1, D1, and E1 as 'player1', 'p1s', 'ps2' (typo but I kept it!), and 'player2'.
• For my OCD, rename column A as match_id (we don't use it in analysis though).
• Remove all formatting from sheet.
• Delete all other sheets in workbook, rename current sheet to 'tetris_matches'.
• Delete columns I:Z and AC (keep date if you want; I simply used the Edition column. Some of this is to taste.
• Save resulting workbook as CSV in order to query in SQL.

From this point onwards, SQL is used before finally shifting to Tableau to create a Dashboard in the end.

SQL Queries

Data Exploration

SELECT
    player1,
    player2,
    p1s,
    ps2,
    p1w,
    p2w
FROM 'steel-cairn-358817.tetris_data.tetris_matches'
WHERE event = 'CTM Masters';

Calculating Matches Won

# This query is needed because Player 1 wins and Player 2 wins are not always recorded in-spreadsheet

WITH p1 AS
  (SELECT player1,
          SUM(CASE
                WHEN p1s > ps2 THEN 1
                ELSE 0
              END) AS p1wins
    FROM `steel-cairn-358817.tetris_data.tetris_matches`
    WHERE event = 'CTM Masters'
    GROUP BY player1),

p2 AS
  (SELECT player2,
          SUM(CASE
                WHEN ps2 > p1s THEN 1
                ELSE 0
              END) AS p2wins
    FROM `steel-cairn-358817.tetris_data.tetris_matches`
    WHERE event = 'CTM Masters'
    GROUP BY player2)
  
SELECT
  p1.player1,
  SUM(p1.p1wins + p2.p2wins) AS wins
FROM p1
INNER JOIN p2
  ON p1.player1 = p2.player2
GROUP BY p1.player1, p2.player2
ORDER BY wins DESC;

Calculating Games Won

# This query indicates how many games each player won in matches they played

WITH p1games AS
  (SELECT player1,
          SUM(p1s) AS p1games_w
    FROM `steel-cairn-358817.tetris_data.tetris_matches`
    WHERE event = 'CTM Masters'
    GROUP BY player1),

p2games AS
  (SELECT player2,
          SUM(ps2) AS p2games_w
    FROM `steel-cairn-358817.tetris_data.tetris_matches`
    WHERE event = 'CTM Masters'
    GROUP BY player2)
  
SELECT
  p1games.player1,
  SUM(p1games.p1games_w + p2games.p2games_w) AS games_won
FROM p1games
INNER JOIN p2games
  ON p1games.player1 = p2games.player2
GROUP BY p1games.player1, p2games.player2
ORDER BY games_won DESC;

Matches Won by Month

# This query is needed because Player 1 wins and Player 2 wins are not always recorded in-spreadsheet
# The difference here is that we group wins by months for later visualizations

WITH p1 AS
  (SELECT player1,
          SUM(CASE
                WHEN p1s > ps2 THEN 1
                ELSE 0
              END) AS p1wins,
          edition AS date
    FROM `steel-cairn-358817.tetris_data.tetris_matches`
    WHERE event = 'CTM Masters'
    GROUP BY player1, edition),

p2 AS
  (SELECT player2,
          SUM(CASE
                WHEN ps2 > p1s THEN 1
                ELSE 0
              END) AS p2wins,
          edition AS date
    FROM `steel-cairn-358817.tetris_data.tetris_matches`
    WHERE event = 'CTM Masters'
    GROUP BY player2, edition)
  
SELECT
  p1.player1,
  SUM(p1.p1wins + p2.p2wins) AS wins,
  p1.date
FROM p1
INNER JOIN p2
  ON p1.player1 = p2.player2
GROUP BY p1.player1, p2.player2, p1.date
ORDER BY wins DESC;

Games Won by Month

# This query indicates how many games each player won in matches they played and the dates they won them by month

WITH p1games AS
  (SELECT player1,
          SUM(p1s) AS p1games_w,
          edition AS date
    FROM `steel-cairn-358817.tetris_data.tetris_matches`
    WHERE event = 'CTM Masters'
    GROUP BY player1, edition),

p2games AS
  (SELECT player2,
          SUM(ps2) AS p2games_w
    FROM `steel-cairn-358817.tetris_data.tetris_matches`
    WHERE event = 'CTM Masters'
    GROUP BY player2)
  
SELECT
  p1games.player1,
  SUM(p1games.p1games_w + p2games.p2games_w) AS games_won,
  p1games.date
FROM p1games
INNER JOIN p2games
  ON p1games.player1 = p2games.player2
GROUP BY p1games.player1, p2games.player2, p1games.date
ORDER BY games_won DESC;

Results

Alex T is the most consistent player for the year with great match and game wins in results.

Andy, Fractal, Dog, and Huff each follow (in that order) and their results are consistent.

Interestingly, Alex T is not one of the players known as the "Big 6" so we can surmise that, although he wins a lot, some wins are ill-timed.

Tetris operates on a ranking system which is explained in the source data spreadsheet.

Dates reveal many Fractal and Dog absences near the beginning of the year (and a few for Huff) so it should be noted that this affects these results.

It's interesting to see how games and match wins don't necessarily tell the full story behind wider rankings.