DDNet SQL dumps analysis
Posted: 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 (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.