Data visualization of level 200 characters

Tis the season for new echo mules -- at least that what the past few weeks of people reaching level 200 has felt like. In that spirit, I collected some data and put together visualizations of level 200 characters as of 2021-01-01 from the rankings page. It turns out that there were 13 new capped players in December, a record high for the server. I hope you find this data as interesting as I do.

https://ml-ranking.geospiza.me

The JSON data can be found here:

• ranking.json
• leveling.json

The source code can be found here.

One feature of the site is that there's a fully baked SQL engine running locally in the browser. If you wanted to see the time of the most recent level 200 players, run this query.

Code:

SELECT rank, name, timestamp
FROM ranking
JOIN leveling USING (name)
WHERE level = 200
ORDER BY rank DESC

Despite it being small (ranking @ 147 rows, leveling @ 27475 rows), you can answer quite a few questions. Here's a look at the average time between each level in log hours, with each level represented with a boxplot. There's a lot of variance because of long breaks, but you can get a sense for how long it actually takes to get to level 200 with the data if you look at something like the minimum or median.



The built in query editor is useful for finding out the answer. For example, how many hours does it take to level to 200 based on the minimum level times of all the current level 200 players? Try pasting in this query:

Spoiler: Query - How many hours to reach level 200?

Code:

-- Minimum hours to reach level 200
-- First calculate the previous level timestamp using the lag window function
with extracted as (
  select
    name,
    level,
    timestamp,
    lag(timestamp) over (
      partition by name
      order by
        timestamp asc
    ) as prev_timestamp
  from
    leveling
),
-- Group the time to level from each player by the level
time_to_level as (
  select
    level,
    date_diff_js(timestamp, prev_timestamp, "hours") as gap
  from
    extracted
  where
    prev_timestamp is not null
),
-- Take the minimum time to level
min_time_to_level as (
  select
    level,
    min(gap) as min_gap
  from
    time_to_level
  group by
    level
)
-- Sum up the results
select
  sum(min_gap) as min_time_to_200
from
  min_time_to_level

Spoiler: Answer

It would take at least 815.67 hours to get a new character to level 200.

Congratulations to all the new level 200 players, and enjoy the data.