Page 1 of 2

DDNet SQL dumps analysis

Posted: Sat Jul 20, 2019 10:27 pm
by miamia
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 (viewtopic.php?f=3&t=1852).
Unfortunately, the latest try to analyze the data is quite old (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
matrose-runblue.png (21.91 KiB) Viewed 23916 times
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
fikmesan-nutrace6.png (18.2 KiB) Viewed 23916 times
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.

Re: DDNet SQL dumps analysis

Posted: Sat Jul 20, 2019 11:48 pm
by deen
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

Re: DDNet SQL dumps analysis

Posted: Sun Jul 21, 2019 12:21 am
by heinrich5991
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.

Re: DDNet SQL dumps analysis

Posted: Sun Jul 21, 2019 12:38 am
by stompie
i love you :D you did what my lazy ass tried (and failed) to do

Re: DDNet SQL dumps analysis

Posted: Sun Jul 21, 2019 11:29 am
by Ryozuki
average_time_over_time.png
average_time_over_time.png (99.91 KiB) Viewed 23818 times
map_stars_points.png
map_stars_points.png (122.5 KiB) Viewed 23818 times
rank_count.png
rank_count.png (86.34 KiB) Viewed 23818 times
sum_of_time.png
sum_of_time.png (91.96 KiB) Viewed 23818 times

Re: DDNet SQL dumps analysis

Posted: Sun Jul 21, 2019 11:52 am
by Ryozuki
rank_finished_over_day.png
rank_finished_over_day.png (62.17 KiB) Viewed 23811 times

Re: DDNet SQL dumps analysis

Posted: Sun Jul 21, 2019 12:03 pm
by Ryozuki
rank_finishes_over_year.png
rank_finishes_over_year.png (65.8 KiB) Viewed 23807 times
rank_finishes_over_month.png
rank_finishes_over_month.png (50.59 KiB) Viewed 23807 times

Re: DDNet SQL dumps analysis

Posted: Tue Jul 23, 2019 11:55 pm
by miamia
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
teamgraph.png (751.61 KiB) Viewed 23720 times
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).

Re: DDNet SQL dumps analysis

Posted: Wed Jul 24, 2019 10:52 am
by deen
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.

Re: DDNet SQL dumps analysis

Posted: Wed Jul 24, 2019 1:53 pm
by jao
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.