PHUSE Breakout Analysis

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 score
results <- tbl(con, "vw_results") # The results of each game
players <- tbl(con, "Players") # All the info of potential players
movements <- tbl(con, "Movements") # Each move made by the ball, including x, y, bearing, and velocity
collisions <- 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.

Code
games |>
    left_join(players, join_by(PlayerID == ID)) |>
    left_join(
        results |> select(GameID, ResultCreated = CreatedOn),
        join_by(ID == GameID)
    ) |>
    mutate(
        duration = DATEDIFF(sql('ss'), CreatedOn.x, ResultCreated)
    ) |>
    summarise(
        Games = n(),
        Players = n_distinct(PlayerID),
        Companies = n_distinct(Company),
        AverageDuration = mean(duration),
        TotalDuration = sum(duration)
    ) |>
    collect() |>
    mutate(across(contains("duration"), as.duration))
Games Players Companies AverageDuration TotalDuration
124 69 38 136s (~2.27 minutes) 13241s (~3.68 hours)

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.

Code
collisions |>
    group_by(Level) |>
    summarise(
        Bricks = sum(as.numeric(CollideWith == "Brick")),
        Paddles = sum(as.numeric(CollideWith == "paddle")),
        Points = sum(PointsScored)
    ) |>
    arrange(Level) |>
    collect()
Level Bricks Paddles Points
1 2561 2464 2561
2 1573 1290 2849
3 318 210 730

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…

Code
games |>
    left_join(players, join_by(PlayerID == ID)) |>
    left_join(results, join_by(ID == GameID)) |>
    filter(Score == max(Score)) |>
    select(
        FirstName,
        Company,
        Score
    ) |>
    collect()
FirstName Company Score
Ross Warman O’Brien 387

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.

Code
players |>
    select(PlayerID = ID, FirstName, Company) |>
    left_join(
        games |> select(GameID = ID, PlayerID),
        join_by(PlayerID)
    ) |>
    left_join(results, join_by(GameID)) |>
    filter(is.na(ID), FirstName == "Liam", Company %like% 'Southampton%') |>
    collect()
PlayerID FirstName Company GameID ID ResultCode Level CreatedOn ModifiedOn ModifiedBy Score GameNo
7AE3010F-E491-4FB6-BE7F-9B9DDBC81165 Liam Southampton Clinical Trials Unit 1DFC2B7F-6ED4-4D39-8595-0B0955023706 NA NA NA NA NA NA NA NA
7AE3010F-E491-4FB6-BE7F-9B9DDBC81165 Liam Southampton Clinical Trials Unit D9A2914E-6855-46EE-82FC-7F929625334E NA NA NA NA NA NA NA NA
7AE3010F-E491-4FB6-BE7F-9B9DDBC81165 Liam Southampton Clinical Trials Unit 8D31BDB3-A7C9-431A-86F5-9ABAE0AE3A24 NA NA NA NA NA NA NA NA

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 broken
    collect()
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!

Code
company_scores <- games |>
    left_join(players, join_by(PlayerID == ID)) |>
    left_join(results, join_by(ID == GameID)) |>
    group_by(Company) |>
    summarise(
        Games = n(),
        Players = n_distinct(PlayerID),
        AverageScore = mean(Score),
        SDScore = sd(Score)
    ) |>
    arrange(desc(AverageScore)) |>
    filter(!regexp_like(Company, 'katalyze', 'i'), Company != "ACME") |>
    collect()
Code
company_scores |>
    slice_max(Players, n = 5)
Company Games Players AverageScore SDScore
Roche 10 6 49 47.343539
Fortrea 4 4 70 52.990565
Bristol Myers Squibb 7 4 63 55.341365
elderbrook solutions 4 4 31 27.531800
Cytel 7 3 91 90.810242
Boehringer Ingelheim 5 3 19 6.757712
Code
company_scores |>
    slice_max(AverageScore, n = 5)
Company Games Players AverageScore SDScore
Warman O’Brien 2 1 387 NA
Devote Consulting 1 1 116 NA
CSL Vifor 1 1 113 NA
Cytel 7 3 91 90.81024
ClinChoice 3 2 89 62.93250

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.

Code
results |>
    filter(day(CreatedOn) != 16) |>
    collect() |>
    mutate(Time = as_hms(CreatedOn), Day = as.factor(day(CreatedOn))) |>
    ggplot(aes(Time, Score, colour = as.factor(Level))) +
    geom_point() +
    facet_wrap(~Day) +
    labs(
        title = "Scores by Day",
        colour = "Level"
    ) +
    guides(x = guide_axis(angle = 45))

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!

Code
games |>
    left_join(
        results |> select(GameID, Score, ResultCreated = CreatedOn),
        join_by(ID == GameID)
    ) |>
    mutate(
        Duration = DATEDIFF(sql('ss'), CreatedOn, ResultCreated)
    ) |>
    filter(Duration < 500) |>
    ggplot(aes(Duration, Score)) +
    geom_point()

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!)

Code
game_start_end <-
    collisions |>
    group_by(GameID) |>
    summarise(
        start_dt = min(CreatedOn),
        end_dt = max(CreatedOn)
    ) |>
    mutate(duration = DATEDIFF(sql('ss'), start_dt, end_dt))

game_deaths <-
    collisions |>
    group_by(GameID) |>
    mutate(
        last_life = lag(Life, order_by = CreatedOn)
    ) |>
    filter(Life != last_life) |>
    select(GameID, CreatedOn, death_num = last_life) |>
    pivot_wider(
        names_from = death_num,
        values_from = CreatedOn,
        names_glue = "death_{death_num}_dt"
    )

results_durations <-
    results |>
    left_join(game_start_end, join_by(GameID)) |>
    left_join(game_deaths, join_by(GameID)) |>
    mutate(death_3_dt = end_dt) |>
    select(
        GameID,
        Score,
        GameNo,
        Level,
        duration,
        start_dt,
        end_dt,
        death_1_dt,
        death_2_dt,
        death_3_dt
    ) |>
    filter(duration < 500) |> # Outlier of game left running for 25 minutes
    collect() |>
    mutate(
        duration = as.duration(duration),
        across(
            starts_with("death"),
            \(x) as.duration(x - start_dt),
            .names = "{.col}_dur"
        )
    )

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.

Code
results_durations |>
    ggplot() +
    geom_density(aes(death_1_dt_dur, colour = "Death 1")) +
    geom_density(aes(death_2_dt_dur, colour = "Death 2")) +
    geom_density(aes(death_3_dt_dur, colour = "Death 3")) +
    labs(
        x = "Time of Death",
        y = "Density"
    )

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.

Code
results_durations |>
    mutate(
        time_to_death_1 = death_1_dt_dur,
        time_to_death_2 = death_2_dt_dur - death_1_dt_dur,
        time_to_death_3 = death_3_dt_dur - death_2_dt_dur
    ) |>
    ggplot() +
    geom_density(aes(time_to_death_1, colour = "Death 1")) +
    geom_density(aes(time_to_death_2, colour = "Death 2")) +
    geom_density(aes(time_to_death_3, colour = "Death 3")) +
    labs(
        x = "Time to Death",
        y = "Density"
    )

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.

Code
results_durations |>
    filter(Level > 1) |>
    mutate(
        time_to_death_1 = death_1_dt_dur,
        time_to_death_2 = death_2_dt_dur - death_1_dt_dur,
        time_to_death_3 = death_3_dt_dur - death_2_dt_dur,
        high_score_flag = Score > 30,
    ) |>
    ggplot() +
    geom_density(aes(time_to_death_1, colour = "Death 1")) +
    geom_density(aes(time_to_death_2, colour = "Death 2")) +
    geom_density(aes(time_to_death_3, colour = "Death 3")) +
    labs(
        x = "Time to Death",
        y = "Density"
    )

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.

Code
collisions |>
    mutate(x_adj = round(x, -1), y_adj = round(y, -1)) |>
    group_by(x_adj, y_adj) |>
    summarise(PointsScored = sum(PointsScored)) |>
    filter(PointsScored > 0) |>
    ggplot(
        aes(x_adj, y_adj, fill = PointsScored)
    ) +
    geom_tile() +
    labs(
        x = "X",
        y = "Y",
        fill = "Points Scored"
    )

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.

Code
collisions |>
    filter(CollideWith == "Brick") |>
    mutate(x_adj = round(x, -1), y_adj = round(y, -1)) |>
    group_by(Level, x_adj, y_adj) |>
    summarise(CurrentScore = mean(CurrentScore)) |>
    ggplot(
        aes(x_adj, y_adj, fill = CurrentScore)
    ) +
    geom_tile() +
    facet_wrap(~Level) +
    labs(
        x = "X",
        y = "Y",
        fill = "Average Current Points"
    )

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!