Compose a query to get the rating table

Question

MySQL

Hello, I would like to ask for more help in creating a request.
About the task: we store the history of chess games between the user and the computer in the database.
This is what the original table looks like:

CREATE TABLE IF NOT EXISTS `chess_historyui` (
  `userId` int(11) NOT NULL, /*id пользователя*/
  `numberSave` int(11) NOT NULL AUTO_INCREMENT, /*номер сохранения*/
  `historyArray` longtext NOT NULL, /*рудимент, уже не используется*/
  `timeUser` int(10) NOT NULL, /*время пользователя*/
  `timeUI` int(10) NOT NULL,  /*время компьютера*/
  `numberMove` int(11) NOT NULL,  /*номер хода*/
  `autoSave` tinyint(1) NOT NULL,  /*флаг автосохранения*/
  `timeSave` datetime NOT NULL,  /*время последнего сохранения*/
  `resultGame` int(11) NOT NULL,  /*результат игры*/
  `timeStart` datetime NOT NULL,  /*время начала игры*/
  `difficulty` tinyint(1) NOT NULL,  /*сложность*/
  `isWhite` tinyint(1) NOT NULL,  /*флаг цвета фигур пользователя*/
  PRIMARY KEY (`numberSave`),
  KEY `userId` (`userId`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=72360 ;

It includes tables:
user_activity (in it we store records about user activity for each session):
only records with gameVariationId=3 belong to our task

CREATE TABLE IF NOT EXISTS `user_activity` (
  `userId` int(11) NOT NULL,
  `sessionId` varchar(255) NOT NULL,
  `gameVariationId` int(11) NOT NULL,
  `lastBeacon` int(11) NOT NULL,
  `lastActivity` int(11) NOT NULL,
  PRIMARY KEY (`userId`,`gameVariationId`,`sessionId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

In the kosynka_users table, we store the actual users.
From it, we only need the following fields:
username - username
isGuest - guest flag (if = 1, then this is an unregistered user) It is

necessary to build a query that will issue a table with the following fields:
userId - user id,
winGame - number of games won 3),
drawGame is the number of draw games (resultGame = 2 OR =4 OR =5 OR =6),
loseGame is the number of games lost (resultGame = 2 OR =4 OR =5 OR =6),
startGame is the number of unfinished games ( resultGame = 0),
allGame — number of all games (resultGame NOT IS NULL),
online — current user status,
onlineActive - user activity,
countNewWinGame - number of new games won (new - played in the last 24 hours),
countNewDrawGame - number of new draw games,
countNewAllGame - number of all new games,
rate - position in the rating, in which all users are present,
rate1 - position in the rating, in which there are only registered users,
rate2 - position in the rating, in which there are only guests,
username - name,
isGuest - guest flag,
sumTimeGame - total solution time,
appearanceTS - registration date (from

We have:

Answer 1

A textbook example of when NOT to screw up.

winGame — number of games won (resultGame = 3),
drawGame — number of draw games (resultGame = 2 OR =4 OR =5 OR =6),
loseGame — number of games lost (resultGame = 2 OR =4 OR =5 OR =6 ),
startGame - the number of unfinished games (resultGame = 0),
allGame - the number of all games (resultGame NOT IS NULL),
and a bunch of other fields

This is all updated only at the end / start of the game, add these fields to the main table and update it during the end / start of the game. Yes, and the last time the user's activity and others are still possible. You don't need to select them dynamically .
In this case, things like “position in the ranking” will also be selected by a simple query, and not by this tricky one (sorry, there’s no other word for it).

Answer 2

Your player rating is formed by sorting

winGame, drawGame, allGame

This seems to be the simplest and most correct method, but it is not.
Not easy because sorting is a very resource-intensive task.
And not the right one because of the two players:
23 usr, 1 winGame, 0 drawGame, 50 drawGame
24 usr, 0 winGame, 10 drawGame, 10 drawGame
preference is given to the first 23 usr, although the second one clearly plays better.
It seems to me that it is better to do the calculation of the rating / points / points, i.e. if you have three parameters that determine the rating winGame, drawGame, allGame, then you can simply assign significance coefficients to them. For example winGame*3, drawGame*2, allGame*1, and then sort by points.

select *, (t2.winGame*3 + t2.drawGame*2 + t2.allGame*1) rating
from(
SELECT
temp.userId as userId,
SUM(CASE WHEN temp.resultGame IS NOT NULL THEN 1 ELSE 0 END) as allGame,
SUM(CASE WHEN temp.resultGame = 3  THEN 1 ELSE 0 END) as winGame,
SUM(CASE WHEN temp.resultGame in(2,4,5,6) THEN 1 ELSE 0 END) as drawGame,
SUM(CASE WHEN temp.resultGame = 1  THEN 1 ELSE 0 END) as loseGame,
SUM(CASE WHEN temp.resultGame = 0  THEN 1 ELSE 0 END) as startGame,
SUM(CASE WHEN temp.resultGame=3 AND UNIX_TIMESTAMP(temp.timeStart)>=UNIX_TIMESTAMP(CURDATE()) THEN 1 ELSE 0 END) as countNewWinGame,
SUM(CASE WHEN temp.resultGame in(2,4,5,6) AND UNIX_TIMESTAMP(temp.timeStart)>=UNIX_TIMESTAMP(CURDATE()) THEN 1 ELSE 0 END) as countNewDrawGame,
SUM(CASE WHEN UNIX_TIMESTAMP(temp.timeStart)>=UNIX_TIMESTAMP(CURDATE()) AND temp.numberMove>3 THEN 1 ELSE 0 END) as countNewAllGame,
SUM(temp.timeUser+temp.timeUI) as sumTimeGame

FROM `chess_historyui` temp
GROUP BY temp.userId
) t2
ORDER BY rating DESC