DDNet SQL dumps analysis
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.
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.
DDNet SQL dumps analysis
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 ?
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.
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 .
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 :
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 :
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 :
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 :
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.
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 |
+--------------------+-----------------+-----------+
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 |
+-----------------+-------+----------+
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 |
+-----------------+---------------+----------+
Given the large number of samples of run_blue, we can plot the time of each run and see if it gets better :
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 :
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 |
+----------+
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 |
+-------------+----------+
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.
-
- Posts: 47
- Joined: Sat Sep 05, 2015 7:18 pm
Re: DDNet SQL dumps analysis
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.
- stompie
- Posts: 700
- Joined: Thu Jun 18, 2015 2:40 pm
- Player profile: http://ddnet.org/players/stompie/
- Mapper profile: https://ddnet.tw/mappers/stompie/
Re: DDNet SQL dumps analysis
i love you you did what my lazy ass tried (and failed) to do
\,,/(◣_◢)\,,/
Re: DDNet SQL dumps analysis
Oh right ! I didn't know these tiles existed. So yes, the official "play time" record is 40 hours in Binary.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.
Ryozuki: thanks for your graphs ! The rank count over time is a little depressing though .
Team races graph
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).
- deen
- Posts: 3579
- Joined: Mon May 05, 2014 2:30 pm
- Player profile: https://ddnet.org/players/deen/
- Discord: deen#5910
Re: DDNet SQL dumps analysis
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 pmOh right ! I didn't know these tiles existed. So yes, the official "play time" record is 40 hours in Binary.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.
It's not that bad if you exclude the last month, which is probably not finished yet.
Re: DDNet SQL dumps analysis
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.
Who is online
Users browsing this forum: No registered users and 1 guest