DDNet SQL dumps analysis

Read and discuss official announcements, information and news about the DDRaceNetwork.
Forum rules
Please consider wheter there is a more appropiate subforum before creating a new thread here.
This is for offical DDRaceNetwork-related information and discussion only.
miamia
Posts: 3
Joined: Thu Jul 18, 2019 12:31 am

DDNet SQL dumps analysis

Post 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 23853 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 23853 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.
User avatar
deen
TECHNICAL Team
Posts: 3575
Joined: Mon May 05, 2014 2:30 pm
Player profile: https://ddnet.org/players/deen/
Discord: deen#5910

Re: DDNet SQL dumps analysis

Post 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
heinrich5991
Posts: 47
Joined: Sat Sep 05, 2015 7:18 pm

Re: DDNet SQL dumps analysis

Post 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.
User avatar
stompie
Posts: 700
Joined: Thu Jun 18, 2015 2:40 pm
Player profile: http://ddnet.tw/players/stompie/
Mapper profile: https://ddnet.tw/mappers/stompie/

Re: DDNet SQL dumps analysis

Post by stompie »

i love you :D you did what my lazy ass tried (and failed) to do
\,,/(◣_◢)\,,/
User avatar
Ryozuki
Posts: 1748
Joined: Tue Feb 24, 2015 7:28 am
Location: Catalonia
Player profile: http://ddnet.tw/players/Ryozuki/
Mapper profile: http://ddnet.tw/mappers/Ryozuki/
Clan: Unique
Website: https://edgarluque.com
Discord: Ryozuki#2188

Re: DDNet SQL dumps analysis

Post by Ryozuki »

average_time_over_time.png
average_time_over_time.png (99.91 KiB) Viewed 23755 times
map_stars_points.png
map_stars_points.png (122.5 KiB) Viewed 23755 times
rank_count.png
rank_count.png (86.34 KiB) Viewed 23755 times
sum_of_time.png
sum_of_time.png (91.96 KiB) Viewed 23755 times
User avatar
Ryozuki
Posts: 1748
Joined: Tue Feb 24, 2015 7:28 am
Location: Catalonia
Player profile: http://ddnet.tw/players/Ryozuki/
Mapper profile: http://ddnet.tw/mappers/Ryozuki/
Clan: Unique
Website: https://edgarluque.com
Discord: Ryozuki#2188

Re: DDNet SQL dumps analysis

Post by Ryozuki »

rank_finished_over_day.png
rank_finished_over_day.png (62.17 KiB) Viewed 23748 times
User avatar
Ryozuki
Posts: 1748
Joined: Tue Feb 24, 2015 7:28 am
Location: Catalonia
Player profile: http://ddnet.tw/players/Ryozuki/
Mapper profile: http://ddnet.tw/mappers/Ryozuki/
Clan: Unique
Website: https://edgarluque.com
Discord: Ryozuki#2188

Re: DDNet SQL dumps analysis

Post by Ryozuki »

rank_finishes_over_year.png
rank_finishes_over_year.png (65.8 KiB) Viewed 23744 times
rank_finishes_over_month.png
rank_finishes_over_month.png (50.59 KiB) Viewed 23744 times
miamia
Posts: 3
Joined: Thu Jul 18, 2019 12:31 am

Re: DDNet SQL dumps analysis

Post 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 23657 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).
User avatar
deen
TECHNICAL Team
Posts: 3575
Joined: Mon May 05, 2014 2:30 pm
Player profile: https://ddnet.org/players/deen/
Discord: deen#5910

Re: DDNet SQL dumps analysis

Post 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.
jao
Posts: 1274
Joined: Wed Jul 06, 2016 1:18 pm

Re: DDNet SQL dumps analysis

Post 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.
Post Reply

Who is online

Users browsing this forum: Ahrefs [Bot] and 1 guest