Last week was another incredible PHUSE EU Connect, with lots of interesting talks, great keynotes, and tons of wonderful people to meet. At the Katalyze Data stand we (as always) had a retro game - and this year it was Breakout. People could play this game and win prizes, and as they played it each move sent data to a SQL Server Database in Azure which was then picked up by an R Shiny dashboard to display leaderboards and a more detailed analysis of a selected game.
The dashboard in action, on the leaderboard page
Now that the Connect is over we can take a closer look at all of the data gathered, and see if any interesting trends pop up!
Summary Stats
We’ll be using {dbplyr}, an excellent package that makes it easy to write R code and have all the computation happen in the database. First things first, we need to connect and setup some links to the tables & views in the database that we’ll be using. To see the details for any of the tables or graphs produced, just expand the code blocks like the one below.
Code
library(dplyr)library(dbplyr)library(ggplot2)library(hms)library(lubridate)library(stringr)library(tidyr)con <- DBI::dbConnect( odbc::odbc(),Driver ="ODBC Driver 18 for SQL Server",Server =Sys.getenv("SERVER"),Database ="db-breakout-01",UID =Sys.getenv("UID"),PWD =Sys.getenv("PWD"),Port =1433)games <-tbl(con, "vw_games") # All started games and player IDs, top level and scoreresults <-tbl(con, "vw_results") # The results of each gameplayers <-tbl(con, "Players") # All the info of potential playersmovements <-tbl(con, "Movements") # Each move made by the ball, including x, y, bearing, and velocitycollisions <-tbl(con, "Collisions") # Each collision of the ball with the paddle, walls, or bricks
The first thing we can check is how many different games, players, and companies we had, and how long they played.
So a total of ~70 players across ~40 companies playing for around 2.3 minutes each, a total of 3.7 hours! That’s about 70% of the total social & networking time (breaks between talks, lunch, etc) spent with the game active.
Next, we can look at how many total bricks were broken, paddles bounced on, and points scored - split up by level.
4.5K bricks broken - mostly in the earlier levels. Interestingly we can see that the ratio of paddle bounces to brick breaks decreases as the levels increase, clearly in later levels people were better at getting more bricks in a single bounce!
Top Scorers
Now for the key question, who was the top scorer over the 3 days. Drumroll please…
To no surprise (for those at the event), it’s Ross from Warman O’Brien. He played the game early on the first day and left a score that was unbeaten for the rest of the Connect, along with winning top prize on the first day.
Unfortunately, we had a couple of games where the server disconnected briefly, thanks to the combo of a VPN and the conference wifi. Due to some very bad luck, 3 of those happened to our next top scorer! Luckily with some sleuthing through the database, we can re-construct their results.
Firstly, let’s select some of the incomplete games (i.e. with no results) that we are interested in.
So we have games recorded, but clearly no results. This is expected, we know that these games didn’t get a chance to record the final result to the server.
If we instead join to collisions, and then summarise, we can see that Liam actually scored second best overall, and was probably in with a chance at nabbing the top spot! He still won the top prize for the second day, so didn’t lose out.
Code
players |>select(PlayerID = ID, FirstName, Company) |>filter(FirstName =="Liam", Company %like%'Southampton%') |>left_join( games |>select(GameID = ID, PlayerID),join_by(PlayerID) ) |>left_join(collisions, join_by(GameID)) |>group_by(GameID) |>summarise(Date =max(CreatedOn),Score =sum(PointsScored),Level =max(Level) ) |>filter(!is.na(Score)) |># Remove instance of game which was quit before any blocks brokencollect()
GameID
Date
Score
Level
D9A2914E-6855-46EE-82FC-7F929625334E
2025-11-17 14:48:53
355
3
1DFC2B7F-6ED4-4D39-8595-0B0955023706
2025-11-18 10:12:36
73
2
BEFA0F0F-4FDC-4F72-8EB7-77A7752F34FA
2025-11-17 14:42:52
19
1
Company Performance
Now we know how the individuals performed, which company did the best? And who spent more of their time at the Connect playing a game when they should have been networking!
Most top average scorers have only 1 player, if we restrict it to those companies where multiple people played:
Code
# Most top average scorers have only 1 player, what if we restrict that?company_scores |>filter(Players >2) |>slice_max(AverageScore, n =5)
Company
Games
Players
AverageScore
SDScore
Cytel
7
3
91
90.81024
Fortrea
4
4
70
52.99057
Bristol Myers Squibb
7
4
63
55.34136
Roche
10
6
49
47.34354
elderbrook solutions
4
4
31
27.53180
Code
company_scores |>slice_max(SDScore, n =5)
Company
Games
Players
AverageScore
SDScore
Cytel
7
3
91
90.81024
Entimo
2
1
56
75.66043
ClinChoice
3
2
89
62.93250
Bristol Myers Squibb
7
4
63
55.34136
Fortrea
4
4
70
52.99057
Visualisations
And finally, what would an analysis be without some visualisations of the data. Some trends we expected didn’t occur - people surprisingly still performed as well (statistically) the morning after the connect dinner - but other interesting patterns appeared.
We can see all of our scores by day and level, including that impressive top score early on the Monday.
We can also check our scores by duration, as expected we can see a clear correlation between longer playtimes and higher scores. One interesting facet here is the clear cap at the end of level one, as people spend quite a while trying to get the final few bricks without increasing their score, often dying in the attempt!
We can also do a little bit more data wrangling to get some interesting stats about the relationships between the different deaths in a game. You get 3 lives, and we want to see when people tend to die in their games, and how long the wait is between each death (i.e. are people more careful with their last life!)
The distributions of time of death occur mostly as you’d expect, each death tending to occur later in the durations, but with a wider distribution as players learn the mechanics of the game and take longer to die on later deaths.
An unpexpected pattern appears if we look at the distributions of time to death, so how long does it take after the game starting or after dying for a player to die again. I had expected death 3 to have a peak after deaths 1 and 2, but instead it’s the opposite! This means players tend to die more quickly on the last life, which could be due to that last life being later in the game when it gets more difficult.
However, if we focus in on only those players who reach levels 2 & 3, we can see a pattern more clearly. These players have a similar overall distribution of first, second, and third time to deaths, except for a sub-population who take a lot longer to die for the third time.
Finally, we can also take a look at some graphs of the points scored by brick. The first looks at how many times each brick was broken, showing a mostly even heatmap with a cluster on that first central brick.
If we look at an average of current score by position, this tells us at what point in the game a brick was broken (higher means later in the game). Interestingly on level 3 it looks like the bricks are broken from top left to bottom right.
There’s quite a lot of interesting analysis and graphs we could do with this data - but we’re already thinking about what the game might be for next year!