DDNet SQL dumps analysis

More serious discussions, information about official DDNet servers, important announcements.
miamia
User
Posts: 3
Joined: Thu Jul 18, 2019 12:31 am

DDNet SQL dumps analysis

Post by miamia » Sat Jul 20, 2019 10:27 pm

Hi everyone,

We all know that under the Freedom Of Information Act, the Congress have indicted the ddnet admin to release the SQL dumps of the race records (https://forum.ddnet.tw/viewtopic.php?f=3&t=1852).
Unfortunately, the latest try to analyze the data is quite old (https://forum.ddnet.tw/viewtopic.php?f=3&t=1921).

I thought that it might be interesting to see if we can find some interesting data in the dumps.


Worst time

Let's start with the basics. We often focus on the best time, but who has the worst time of all maps ?

Code: Select all

SELECT R.Map, R.Name, SEC_TO_TIME(FLOOR(R.Time)) AS Time
FROM record_race R JOIN record_maps M ON R.Map = M.Map
WHERE R.Time > 24*60*60 ORDER BY R.Time DESC;

Code: Select all

+--------------------+-----------------+-----------+
| Map                | Name            | Time      |
+--------------------+-----------------+-----------+
| Care for your Time | dfceaef         | 379:52:40 |
| Care for your Time | 妈的智障        | 379:01:30 |
| Care for your Time | snailx3         | 166:40:00 |
| Care for your Time | BooNi           | 83:20:00  |
| Care for your Time | l26             | 82:40:00  |
| Binary             | Dareka          | 40:00:25  |
| Binary             | Pulsar          | 40:00:25  |
| Care for your Time | ĄvąpiX          | 38:30:44  |
| Care for your Time | fikmyson        | 33:36:28  |
| Care for your Time | ☆♕☛Mew☚         | 33:20:20  |
| Ravillion          | nopea           | 30:15:21  |
| Ravillion          | nopea [D]       | 30:15:21  |
| Care for your Time | :parking:oiuyt  | 30:05:00  |
| Binary             | Xi              | 27:56:30  |
| Binary             | gL. | Kenzo     | 27:56:30  |
| Epix               | nameless tee    | 27:40:10  |
| Epix               | brainless tee   | 27:40:08  |
| Binary             | HaHAxD*         | 27:12:00  |
| Binary             | Tropo           | 27:12:00  |
| Arcade 2           | AssasinMaster   | 27:06:14  |
| Arcade 2           | MasterMind      | 26:53:00  |
| Lost 2             | AssasinMaster   | 26:44:08  |
| run_world_war_zero | RedPig          | 26:33:33  |
| Naufrage 3         | M Emile         | 24:25:02  |
| Care for your Time | Savander-Long   | 24:00:13  |
+--------------------+-----------------+-----------+
We can see that the worst time is ~16 days on Care for your Time. And it's not just the one person, 10 people finished in more than a day !
Given the name of the map, this is a little... ironic.
Anyway, if someone has an explanation, I'd be glad to hear it !


Biggest team

While we are trying to find the largest, let's find the largest teams.

Code: Select all

SELECT Map, COUNT(*) AS Count, SEC_TO_TIME(FLOOR(Time)) AS Time
FROM record_teamrace
GROUP BY Id HAVING Count > 10
ORDER BY Count DESC;

Code: Select all

+-----------------+-------+----------+
| Map             | Count | Time     |
+-----------------+-------+----------+
| Halloween Night |    41 | 00:22:19 |
| 4u              |    16 | 05:06:24 |
| Sopella         |    11 | 00:17:12 |
| AiP-Gores       |    11 | 00:24:36 |
| NUT_short_race4 |    11 | 00:04:18 |
+-----------------+-------+----------+
Well, 41 tees, this looks legit ! Apparently, it was ~10 tees + dummies.
Once again, I guess they attempted to do something smart, but I can't figure out what.


Most finishes on the same map

Now let's give the OCD award to the tee with the most finishes on the same map :).

Code: Select all

SELECT Map, Name, COUNT(*) AS Finishes
FROM record_race
GROUP BY Map, Name
ORDER BY Count DESC LIMIT 10;

Code: Select all

+-----------------+---------------+----------+
| Map             | Name          | Finishes |
+-----------------+---------------+----------+
| run_blue        | Matrose l_I   |     2455 |
| run_g6          | Matrose l_I   |     2057 |
| Just2Easy       | brainless tee |     1555 |
| NUT_short_race6 | fikmesån      |     1342 |
| run_g6          | Hawkeye88     |     1274 |
| NUT_short_race6 | Tropo         |     1236 |
| run_g6          | mrs.Smith     |     1027 |
| NUT_short_race6 | snailx3       |      960 |
| run_g6          | Nyanto        |      949 |
| NUT_short_race6 | Tropo[D]      |      867 |
+-----------------+---------------+----------+
It looks like "Matrose l_I" is our winner by far !
Given the large number of samples of run_blue, we can plot the time of each run and see if it gets better :
matrose-runblue.png
We can see that we have a slow but real improvement until the ~1800th run.

For some other players, we can see other results, for example fikmesån on NUT_short_race6 :
fikmesan-nutrace6.png
After getting the 1 second record on the map after the 1283th attempt, it's pretty clear that he started to drink.


Database inconsistencies

Finally (for now), we can see some interesting incoherencies in the database. For example, when a team finishes a map, a record is created per teammate on the tables record_race (for scoring of individuals and best time) and record_teamrace (for scoring of team time).
However, we can see that some records in record_teamrace do not have a corresponding entry in record_race :

Code: Select all

SELECT COUNT(*) FROM (SELECT Map, Name, Time FROM record_teamrace WHERE (Map, Name, Time) NOT IN (SELECT Map, Name, Time FROM record_race)) T;

Code: Select all

+----------+
| COUNT(*) |
+----------+
|      100 |
+----------+
This means that some tees have finished a map but they don't have the points.


Also, we can look at the records on a map that doesn't exist :

Code: Select all

SELECT Map, COUNT(*) FROM record_race WHERE Map NOT IN (SELECT Map FROM record_maps) GROUP BY Map;

Code: Select all

+-------------+----------+
| Map         | COUNT(*) |
+-------------+----------+
| DontMove    |        7 |
| Heartcore 2 |        2 |
| Lunar Base  |       22 |
+-------------+----------+
From what I understand, DontMove was removed, but some records still exist. Heartcore 2 has been renamed "Heartcore II". And Lunar Base was also removed ?



So this is what I have so far. I'll try to do another post about graph analysis (using the record_teamrace table) in the near future.
Anyway, I hoped you liked it !

Cheers.

User avatar
deen
Retired Administrator
Posts: 3292
Joined: Mon May 05, 2014 2:30 pm
Player profile: deen

Re: DDNet SQL dumps analysis

Post by deen » Sat Jul 20, 2019 11:48 pm

I like the plots! Thank you.
miamia wrote:
Sat Jul 20, 2019 10:27 pm
We all know that under the Freedom Of Information Act, the Congress have indicted the ddnet admin to release the SQL dumps of the race records
:D

heinrich5991
Technical Team: Coder
Posts: 46
Joined: Sat Sep 05, 2015 7:18 pm

Re: DDNet SQL dumps analysis

Post by heinrich5991 » Sun Jul 21, 2019 12:21 am

Care for your Time manipulates your race time when you fail (it adds some seconds). So the 380 hours on that map are not actual playtime, you might want to exclude this map for the worst time award.

User avatar
stompie
User
Posts: 700
Joined: Thu Jun 18, 2015 2:40 pm
Player profile: stompie
Mapper profile: stompie

Re: DDNet SQL dumps analysis

Post by stompie » Sun Jul 21, 2019 12:38 am

i love you :D you did what my lazy ass tried (and failed) to do
\,,/(◣_◢)\,,/

User avatar
Ryozuki
User
Posts: 1743
Joined: Tue Feb 24, 2015 7:28 am
Location: Catalonia
Player profile: Ryozuki
Mapper profile: Ryozuki
Clan: Unique
Website: https://ryozuki.xyz/

Re: DDNet SQL dumps analysis

Post by Ryozuki » Sun Jul 21, 2019 11:29 am

average_time_over_time.png
map_stars_points.png
rank_count.png
sum_of_time.png

User avatar
Ryozuki
User
Posts: 1743
Joined: Tue Feb 24, 2015 7:28 am
Location: Catalonia
Player profile: Ryozuki
Mapper profile: Ryozuki
Clan: Unique
Website: https://ryozuki.xyz/

Re: DDNet SQL dumps analysis

Post by Ryozuki » Sun Jul 21, 2019 11:52 am

rank_finished_over_day.png

User avatar
Ryozuki
User
Posts: 1743
Joined: Tue Feb 24, 2015 7:28 am
Location: Catalonia
Player profile: Ryozuki
Mapper profile: Ryozuki
Clan: Unique
Website: https://ryozuki.xyz/

Re: DDNet SQL dumps analysis

Post by Ryozuki » Sun Jul 21, 2019 12:03 pm

rank_finishes_over_year.png
rank_finishes_over_month.png

miamia
User
Posts: 3
Joined: Thu Jul 18, 2019 12:31 am

Re: DDNet SQL dumps analysis

Post by miamia » Tue Jul 23, 2019 11:55 pm

heinrich5991 wrote:
Sun Jul 21, 2019 12:21 am
Care for your Time manipulates your race time when you fail (it adds some seconds). So the 380 hours on that map are not actual playtime, you might want to exclude this map for the worst time award.
Oh right ! I didn't know these tiles existed. So yes, the official "play time" record is 40 hours in Binary.

Ryozuki: thanks for your graphs ! The rank count over time is a little depressing though :).


Team races graph
teamgraph.png
This is the "team" graph from the database.
It shows how the different players (nodes) are connected with each other : there is an edge between two players if they have finished a map together.
This only shows the biggest connected component (41k players out of the 50k players ranked in team).

The colors represent the favorite server of each player. We can see the cruel laws of ping time in the graph : players tend to often play on the same server, creating big clusters. However, there are some exceptions (GER/RUS, USA/CAN).

User avatar
deen
Retired Administrator
Posts: 3292
Joined: Mon May 05, 2014 2:30 pm
Player profile: deen

Re: DDNet SQL dumps analysis

Post by deen » Wed Jul 24, 2019 10:52 am

miamia wrote:
Tue Jul 23, 2019 11:55 pm
heinrich5991 wrote:
Sun Jul 21, 2019 12:21 am
Care for your Time manipulates your race time when you fail (it adds some seconds). So the 380 hours on that map are not actual playtime, you might want to exclude this map for the worst time award.
Oh right ! I didn't know these tiles existed. So yes, the official "play time" record is 40 hours in Binary.
These tiles should honestly not exist anymore, they are quite annoying, especially when you want to look at data like you are. Originally they were a hack for the Flappy Bird tournament.
miamia wrote:
Tue Jul 23, 2019 11:55 pm
Ryozuki: thanks for your graphs ! The rank count over time is a little depressing though :).
It's not that bad if you exclude the last month, which is probably not finished yet.

jao
Testing Team: Team Leader
Posts: 1254
Joined: Wed Jul 06, 2016 1:18 pm

Re: DDNet SQL dumps analysis

Post by jao » Wed Jul 24, 2019 1:53 pm

miamia wrote:
Sat Jul 20, 2019 10:27 pm
From what I understand, DontMove was removed, but some records still exist. Heartcore 2 has been renamed "Heartcore II". And Lunar Base was also removed ?
Not sure why there were still some DontMove and Heartcore 2 ranks (which was an april fools map, not to be confused with Heartcore II). Deleted them now. Lunar Base is temporarily removed as it is currently unfinishable due to a breaking change.

Post Reply

Who is online

Users browsing this forum: No registered users and 4 guests