XonStat data snapshot released

Hi everyone,

Over the years I have received several requests for raw statistics data. Today I'm happy to release a snapshot of that data for interested parties to use! You can obtain this here. This is a PostgreSQL database dump of XonStat on 2015-05-27 that was created using pg_dump. It contains every game, server, map, and player present in the database up to that time! You can load it by following the commands within the drop and load script in the xonstatdb git repository.

Once you are up and running, I'd recommend examining the table structures with the '\dt' command from within psql to get yourself familiar with the data model. You can filter out tables ending in q1, q2, q3, or q4 - these are child tables used to break up the volume. For example, the games_2015q1 table contains games from the first quarter of this year, but you can obtain the same information by querying the games table with a WHERE clause on the create_dt column. If you have any questions, you can always ask here.

Licensing
The database structure (DDL, for those familiar with database parlance) is under the GPLv3 license, with the full source available on GitLab. The data is licensed under the CC-BY license, with attribution to "Team Xonotic" and http://www.xonotic.org.

I look forward to seeing what you all do with this!

Thanks!
-AB

Great work Antibody, you're a very talented DataBase Admin and the requests for queries probably get a bit crazy. There is so much potential with the raw data being released like this.

Here's one exploration, mostly motivated by this comment.

I looked at DM game data between Jan to May of 2015. These are some findings on the elo scores, which I use to roughly represent the skill level of each player:

1. About 70% of players have lowest elo score (100). The average number of games they played is 11.8



2. The skill levels are actually not polarized as people may perceive. Here's the histogram of elo scores larger than 100 (i.e. the 70% of the players mentioned above are excluded):

   

It shows that we have all skill levels between 'noobs' and 'gods'.



3. Practice makes perfect. The more games one plays, the more likely that player has a higher elo score. (OK, it's correlation, not necessarily causal. It could just mean good players enjoy playing Xonotic so they play more. I do plan to do an analysis on improvement over time, some time in the future.)

   
(Green lines are average elo of each # of game bucket; the red curve is the regression)

Great work.

For those of you wondering why we have 70% of the playerbase at the lowest possible Elo value, consider the following:

1) New players who come online and play for a couple of games, not netting any positive points throughout.
2) Stagnant old records that have rotted down to the Elo "floor" level.

I don't ever clear out any of these things, so they basically stay that way forever. This isn't a problem in the database itself but it does pose some difficulty in doing analysis work like this. Namely - who has Elo 100 but is still active? There are ways to answer this but it is much easier to just chop off the lowest point of the Elo range to get a representative look at the shape of the data. That's exactly what BuddyFriendGuy has done here.

That's exactly what BuddyFriendGuy has done here.

Antibody should have the credit. That was his advice for me on IRC.

About the seemingly high percentage of players with low Elo, it's actually not a bad thing. Here's the density plot of these 70% of players:


  games_elo100.png (Size: 9.23 KB / Downloads: 190)

This chart only shows 95% of those players; the very long tail is chopped because it goes all the way up to 2135. Players under that long tail are likely to be the rotted ones Antibody talked about.

This chart tells us that the majority of those 70% players with Elo=100 are those who haven't played enough games. Actually, 80% of them play less than 10 games.

It's quite understandable that their skill level is low, given their limited experience.

On the other hand, 70%*80% = 56%. That means, during Jan-May, 2015, we have more than half DM players who played less than 10 games in total. That doesn't sound like a lot of games.

I didn't dig further. Say, maybe there was some article on Xonotic so suddenly we had a lot of people trying Xonotic at the end of May. However, we may want to explore more. For example, I'm quite curious to see who are usually in newbie's "very last game". Perhaps some experienced users are too intimidating?

Out of curiosity, does the data also include players who have disabled tracking?
And if not, what is the proportion of untracked players?

Out of curiosity, does the data also include players who have disabled tracking?
And if not, what is the proportion of untracked players?

That's a question for Antibody, but I just want to clarify that I won't reveal individual identities. I'm only interested in aggregated (sub-)group behavior.

The data set does include players who disabled tracking, but there is no way to track them over their playing history. They are represented as a series of unconnected game stat rows.

This was actually a design decision I came up against very quickly when I first started working on stats: how do I represent bots on the scoreboards and how do I represent anonymous players? My chosen implementation associates bots with player_id #1 and anonymous players with player_id #2. In this way I can maintain referential integrity within the database and still keep things in identifiable buckets.

I'm the next player ID beyond those first two psuedo-players. Samual and Mirio aren't too far behind, as they were my initial guinea pigs! Of course things were a bit bumpy with the code when we first went live, resulting in me being player #6 instead of player #3.

Thank you for the explanation.
That seems like a solid solution indeed.

Hello all! I made the SchemaSpy output for the xonstatdb database available online via my website today. You can browse it here: http://xonotic.antzucaro.com/xonstatdb/. What's SchemaSpy, you ask? To put it simply, it is a tool that extracts your database definitions and puts them into a nicely browsable and linkable format for the web. In other words, it makes it easier for you to examine the nuts and bolts of the database snapshot that I released in this thread.

I hope this makes it easier for you all to play around and make sense of this data release. As always, let me know if you have any questions. Happy data-spelunking!

In the interest of those who have requested more recent data I have created a new snapshot of the full database. The MD5 of this is b1d370cb9f3b59a3511ca6e1e20a857f. Unlike the last snapshot, this is a full dump of the whole database that is suitable for running/testing out your own installation of XonStat.

This release is loadable with the same commands mentioned previously, and under the same license. Cheers!