Yes, you heard right.

I don’t always write bots. But when I do, it’s for a good reason!

Some background: Runescape is one of the most popular online multiplayer role playing games. It has a plethora of skills, adventures and activities one can pursue. Recently, a new skill was released – archeology. While we can always tell who the FIRST person was to get maximum experience in the skill, I thought it would be pretty cool to see the journey in doing so when compared to their top competitors in the final days.

The stack used in this ad-hoc project was a combination of PHP, MySQL and Tableau.

I’ve written several bots before for the hiscores on this game, so what I did was salvage a lot of the code in PHP and store it in a separate MySQL table. The script was set to run on a cron job hourly indefinitely until stopped. It would store up to the top 100 players of the skill on each run.

I let several days (or weeks) pass before I decided to actually look at the data and do something with it, and now was the time! Here’s what the table looks like:

Each record represents a player, and the rank, xp and record insertion timestamp at the time the data is captured. Unfortunately, no primary keys or players ID were configured as this was meant to be an ad-hoc analysis and I was lazy.

The next step was to decipher the data and prep it for analysis. What I really would like to know ultimately was who first got to 200M XP, and what their days leading up to it looked like. Were they consistently first place? Or was it a neck and neck battle for days?

We’re well past the day when the first player got to 200M XP. First, I had to figure out what date to look back from:

Turns out that day was 4/14/2020. Now, we want to identify the top 50 players when the first person reached 200M XP, so we can filter out all the other players that may have been top 50 at one point or another, but not on this 4/14/2020 run. I later re-learned the hard way that I couldn’t use limits in a sub query, so I created a temp table which we’ll use later on.

Now, the other piece to the puzzle we need is pulling just one record per user per day. To do this, we’ll identify the latest capture of each day so that we can filter based on just those exact times. In doing so, we won’t get any duplicate records for any users on any day – just the highest, latest data that was recorded for them for that day.

And now, we can finally write our beautiful query that will give us what we need. We want a data-set of all of those final top 50 players, and what their latest daily positions were.

We get 514 records (we have fallouts because those final 50 players may not have been in the top 100 the entire time). Then, we store these results in an excel file and add a connector in Tableau for ingestion and build some quick views.

Yikes! That’s messy…perhaps we should limit it to the top 5 players instead (I went through all that SQL trouble for nothing!)

That’s much better. And we see something interesting…

Le Me got first place, congratulations! It appears that he and several others have consistently been the top 1-3. What we do notice is Omid came out of nowhere for a very close finish. They were around rank 40 in early April, and made a huge comeback, securing the 3rd spot as of April 14th.

This bot may have been a waste of time, but it only took about an hour to set up and the trend I witnessed was fulfilling and made it all worth it! Who knows, maybe I’ll do more with this data later!