As the old saying goes, the only code which is released 100% bug free is the code which is never written; and so far I'm pleased with this new code.
We have received only one bug report so far, which was related I believe to a JSON formatting / Javascript error due to some characters in the database which did not play well with JSON and Javascript. I wrote and testing a lot of code to filter out the potential gotchas when a new question was submitted to the DB and manually cleaned unprintable chars and line breaks from the questions. The results are that the error seems to have gone away and I have not seen it again.
So far so good. Currently, here are the user interactions:
mysql> select count(*) from neo_trivia_scoreboard;
+----------+
| count(*) |
+----------+
| 6305 |
+----------+
1 row in set (0.01 sec)
The first and last entries by users:
mysql> select dateline from neo_trivia_scoreboard order by dateline desc limit 1;
+------------+
| dateline |
+------------+
| 1572919422 |
+------------+
1 row in set (0.00 sec)
mysql> select dateline from neo_trivia_scoreboard order by dateline asc limit 1;
+------------+
| dateline |
+------------+
| 1572328203 |
+------------+
1 row in set (0.01 sec)
Or 1572919422 - 1572328203 = 59,1219 seconds lifetime so far, or 9854 minutes.
Translating to 9854 / 6305 or about one user answer submitted every 1.4 minutes (minutes/question) since I released this new code was released.
Over the past 24 hours, the number is higher:
mysql> select count(*)/(24*60) as "questions per minute" from neo_trivia_scoreboard where dateline > UNIX_TIMESTAMP() -86000;
+----------------------+
| questions per minute |
+----------------------+
| 0.9938 |
+----------------------+
1 row in set (0.00 sec)
Translating to about 1 question per minute answered on average over the past 24 hours.
The total number of questions in the DB is:
mysql> select count(*) from neo_open_trivia;
+----------+
| count(*) |
+----------+
| 908 |
+----------+
1 row in set (0.00 sec)
The vast majority of these questions (maybe 99%+) were derived from Computer Science textbooks or Wikipedia.
As far as countries, here are the top 40 (Neo, excluded):
mysql> select country, count(country) from neo_trivia_scoreboard where uid !=1 group by country order by count(country) desc LIMIT 40;
+---------+----------------+
| country | count(country) |
+---------+----------------+
| US | 1707 |
| IN | 742 |
| DE | 278 |
| GB | 276 |
| IT | 202 |
| BR | 149 |
| CA | 144 |
| FR | 138 |
| NL | 125 |
| PH | 110 |
| RU | 107 |
| AU | 97 |
| BG | 93 |
| ES | 88 |
| TH | 81 |
| SE | 70 |
| AR | 70 |
| PL | 65 |
| UA | 57 |
| CH | 51 |
| HR | 49 |
| MX | 48 |
| HU | 48 |
| FI | 44 |
| MA | 38 |
| AT | 38 |
| ID | 37 |
| XX | 31 |
| RO | 29 |
| BE | 28 |
| RS | 24 |
| PT | 19 |
| GR | 19 |
| AE | 18 |
| SG | 18 |
| PK | 18 |
| ZA | 18 |
| IE | 15 |
| HK | 15 |
| KH | 14 |
+---------+----------------+
40 rows in set (0.01 sec)
and based on hash (masked), IP addresses:
mysql> select left(MD5(ipaddress),12) as hash, count(ipaddress),username, country from neo_trivia_scoreboard where uid != 1 group by ipaddress,username,country order by count(ipaddress) desc limit 40;
+--------------+------------------+--------------+---------+
| hash | count(ipaddress) | username | country |
+--------------+------------------+--------------+---------+
| 2308b749ee0c | 279 | rdrtx1 | US |
| da7c5eae2b00 | 126 | Unregistered | US |
| 9beb7eda3ada | 103 | Unregistered | PH |
| 4769267eed25 | 91 | Unregistered | IT |
| 0c8adcc201b2 | 60 | Unregistered | NL |
| 857d22f278f2 | 55 | KD_999 | BG |
| 952b0c66e2af | 53 | Unregistered | IN |
| 145322cf921e | 53 | Unregistered | BR |
| 9c6892813c0f | 45 | Unregistered | US |
| 0b122fbf8494 | 45 | Unregistered | US |
| 50796d179daf | 44 | Unregistered | DE |
| 7d735e9d9eea | 43 | Unregistered | IN |
| 496c1ebb49bb | 37 | Unregistered | AR |
| 7a42d8abcbc6 | 35 | Unregistered | MA |
| 26b37bb15612 | 34 | Unregistered | US |
| 67b4b3c0a97e | 33 | Unregistered | SE |
| 8bfc0fbb7d31 | 31 | Unregistered | IN |
| 2c676bfbff0e | 31 | Unregistered | US |
| 4e628f3442d1 | 31 | Unregistered | BR |
| 73aaa345d005 | 30 | Unregistered | AU |
| 18a9b4c29552 | 29 | milhan | US |
| 13b4d6b7b692 | 29 | Unregistered | US |
| e40c9db86f87 | 28 | Unregistered | IN |
| 91cb2d037eee | 28 | Unregistered | NL |
| 96bd912beb5d | 27 | Unregistered | IN |
| 299767cc5bf2 | 27 | Unregistered | ID |
| 11330bb5bd01 | 27 | Unregistered | US |
| 47a14a853303 | 26 | Unregistered | GB |
| 8a98f1985c12 | 25 | Unregistered | FR |
| 11656f2e9795 | 25 | Unregistered | GB |
| 698190045add | 25 | Unregistered | IT |
| 99f15d8b770f | 24 | Unregistered | UA |
| a4798823ea4d | 23 | Unregistered | GB |
| ed3af5326e07 | 23 | Unregistered | US |
| ef1e0f8ec62a | 23 | Unregistered | PL |
| 1977a7cd1d10 | 23 | CyberStems | CA |
| f6f617c97919 | 22 | Unregistered | IT |
| 401f4643e20c | 22 | Unregistered | AR |
| 5fd3ff8783f5 | 22 | Unregistered | US |
| a7a86f27afe5 | 22 | Unregistered | DE |
+--------------+------------------+--------------+---------+
40 rows in set (0.02 sec)
Basically, this is going very well and seems to be running close to as bug and error free as one could expect after slightly less than one week after coding it and releasing it to the public.
The greatest percentage of those who answer questions are guests, not registered users:
mysql> select count(*) from neo_trivia_scoreboard where uid < 1; select count(*) from neo_trivia_scoreboard where uid != 1;
+----------+
| count(*) |
+----------+
| 4931 |
+----------+
1 row in set (0.00 sec)
+----------+
| count(*) |
+----------+
| 5451 |
+----------+
1 row in set (0.00 sec)
So, 90% of the users who use this new feature are not registered users, which is exactly what I expected and hoped for.
As far as the average score to date for all these users (sans Neo):
mysql> select count(*) from neo_trivia_scoreboard where uid != 1 and results='right'; select count(*) from neo_trivia_scoreboard where uid != 1;
+----------+
| count(*) |
+----------+
| 3625 |
+----------+
1 row in set (0.00 sec)
+----------+
| count(*) |
+----------+
| 5451 |
+----------+
1 row in set (0.00 sec)
Overall CS trivia score: 67%.
For only guests:
mysql> select count(*) from neo_trivia_scoreboard where uid < 1 and results='right'; select count(*) from neo_trivia_scoreboard where uid < 1;
+----------+
| count(*) |
+----------+
| 3258 |
+----------+
1 row in set (0.01 sec)
+----------+
| count(*) |
+----------+
| 4931 |
+----------+
1 row in set (0.00 sec)
Overall CS trivia score guests: 66%.
and for only registered users (sans Neo):
mysql> select count(*) from neo_trivia_scoreboard where uid > 1 and results='right'; select count(*) from neo_trivia_scoreboard where uid > 1;
+----------+
| count(*) |
+----------+
| 367 |
+----------+
1 row in set (0.00 sec)
+----------+
| count(*) |
+----------+
| 520 |
+----------+
1 row in set (0.00 sec)
Overall CS trivia score members: ~71%.
Neo does not count, since I entered all the questions and also click randomly for testing, but just for fun:
mysql> select count(*) from neo_trivia_scoreboard where uid = 1 and results='right'; select count(*) from neo_trivia_scoreboard where uid = 1;
+----------+
| count(*) |
+----------+
| 740 |
+----------+
1 row in set (0.00 sec)
+----------+
| count(*) |
+----------+
| 856 |
+----------+
1 row in set (0.00 sec)
Neo's testing score: 86%
856... that is a lot of testing... but I play our trivia when I am at the gym and resting and just hanging out killing time, LOL... which means I should be playing trivia less and working out more.
So, that's about it. I will use these kinds of mysql queries to create some scoreboards and leadership boards later; but I'm months behind of my annual tax filing paper work due to some problem with my bill paying service back in the US; and need to get back to solving this problem before the tax man gets mad at me for being behind on my annual paperwork!
Believe, me I would much rather prefer to be writing Vue.js code and creating some scoreboards than doing tax paperwork; but alas, life is not always fun and coding!
As far as registered members who submitted answers so far (sans Neo):
mysql> select count(username),username, country from neo_trivia_scoreboard where uid > 1 group by username,country order by count(username) desc limit 40;
+-----------------+---------------+---------+
| count(username) | username | country |
+-----------------+---------------+---------+
| 279 | rdrtx1 | US |
| 55 | KD_999 | BG |
| 46 | Peasant | HR |
| 29 | milhan | US |
| 26 | 000vikas | TH |
| 23 | CyberStems | CA |
| 9 | prvnrk | SE |
| 8 | Akshay Hegde | IN |
| 6 | gull04 | GB |
| 6 | mauregato | ES |
| 4 | zxmaus | US |
| 4 | anbu23 | US |
| 3 | MadeInGermany | DE |
| 3 | hicksd8 | GB |
| 3 | apmcd47 | GB |
| 2 | NKaede | DE |
| 2 | Sagar Singh | IN |
| 2 | penchev | BG |
| 2 | yirgacheffe | AU |
| 1 | MadeInGermany | US |
| 1 | Guest | BG |
| 1 | kshitij | IN |
| 1 | deeppal | IN |
| 1 | mcgvaer | RU |
| 1 | stomp | DE |
| 1 | alexcol | CO |
| 1 | wisecracker | GB |
+-----------------+---------------+---------+
27 rows in set (0.00 sec)
