How could I improve this SQL query query with sub querys?

Question

There are many sub selects, how could improve this query in Mysql and make a max SUM of total_points_period'?

Pick up the total points for a period from the "user_bigdata" table and the total points from the "olympic_players_levels" table according to the period.

The table user_bigdata, is a list where you have all points per user of the system, but I need to take and sum all the maximum points of the same relationship action_id, already the score of olympic_player_levels is the score of a particular competition by period, which is related to the active competition in the table Olympic, where it contains the initial and final period of this competition. I would like to relate both without needing a subquery to another subquery.

The current query:

SELECT `tab`.`user_id`, `tab`.`total_points`, 
                    (SELECT max(CASE WHEN ub.action_type = 'points' and ub.value <> 'giveup' and  ub.created between start_date and end_date THEN CAST(ub.value as UNSIGNED) ELSE 0 END ) AS pontos_obtidos
                        FROM user_bigdata ub
                        WHERE ub.usr_id = user_id 
                        AND (ub.action_type in ('points') and ub.type_utilization=2)
                        group by ub.action_id, ub.question_id) as total_points_period
                     FROM (
            SELECT 
                SUM(opn.points) AS `total_points`,
                ol.start_date AS start_date,
                ol.end_date AS end_date,
                u.ID as user_id,
                u.avatar_json

            FROM
                `olympic_players_niveis` AS `opn`
                    INNER JOIN
                olympics ol ON (ol.active = 1 AND ol.ID = opn.olympic_id AND ol.ID = 1 )
                   INNER JOIN users u ON(`opn`.`user_id`=u.ID)
            GROUP BY `opn`.`user_id`
            ORDER BY SUM(opn.points) DESC , `opn`.`created` DESC , FIELD(opn.tip_resolved, 1, NULL) DESC
            ) AS `tab` ORDER BY `total_points` DESC, `total_points_period` DESC

Tables:

CREATE TABLE `olympic_players_niveis` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `nivel_id` int(4) NOT NULL,
  `points` int(6) NOT NULL,
  `olympic_id` int(4) DEFAULT NULL,
  `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `tip_resolved` int(1) DEFAULT NULL,
  `updated` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `fk_olympic_players_Olympics1_idx` (`olympic_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


CREATE TABLE `olympics` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `active` int(11) NOT NULL,
  `title` text CHARACTER SET utf8 NOT NULL,
  `badge_id` int(11) NOT NULL,
  `person_1` int(3) DEFAULT NULL,
  `person_2` int(3) DEFAULT NULL,
  `initial_talk_id` int(3) DEFAULT NULL,
  `end_talk_id` int(3) DEFAULT NULL,
  `logo` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `regulation` text COLLATE utf8_unicode_ci,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `free_users_access` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `user_bigdata` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `usr_id` int(4) NOT NULL,
  `action_type` varchar(50) NOT NULL,
  `action_id` int(4) NOT NULL,
  `question_id` int(4) NOT NULL,
  `data_type` varchar(30) NOT NULL,
  `value` varchar(100) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `start` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `end` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `seconds` int(5) NOT NULL,
  `answer` varchar(5) DEFAULT NULL,
  `answer_correct` int(2) DEFAULT NULL,
  `type_utilization` int(2) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `indice1` (`value`,`data_type`,`action_id`,`usr_id`),
  KEY `type_value` (`data_type`,`value`,`usr_id`),
  KEY `indiceusuario` (`usr_id`),
  KEY `actiontimeid` (`action_type`,`action_id`,`question_id`),
  KEY `action_search` (`usr_id`,`action_type`,`data_type`,`action_id`),
  KEY `index_busca_atividade` (`usr_id`,`action_type`,`question_id`,`created`,`value`,`action_id`),
  KEY `index_recentes` (`usr_id`,`action_id`,`action_type`),
  KEY `index_data` (`created`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `users` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(200) CHARACTER SET utf8 NOT NULL,
  `role` varchar(50) CHARACTER SET utf8 NOT NULL,
  `password` varchar(500) CHARACTER SET utf8 NOT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `idx_role` (`ID`,`role`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The data would be basically these:

INSERT INTO `user_bigdata` (`ID`, `usr_id`, `action_type`, `action_id`, `question_id`, `value`, `type_utilization`) VALUES ('', 1, 'points', 1, 0, 200, 2);
INSERT INTO `user_bigdata` (`ID`, `usr_id`, `action_type`, `action_id`, `question_id`, `value`, `type_utilization`) VALUES ('', 1, 'points', 1, 0, 100, 2);
INSERT INTO `user_bigdata` (`ID`, `usr_id`, `action_type`, `action_id`, `question_id`, `value`, `type_utilization`) VALUES ('', 1, 'points_tips', 1, 0, 50, 2);

INSERT INTO `user_bigdata` (`ID`, `usr_id`, `action_type`, `action_id`, `question_id`, `value`, `type_utilization`) VALUES ('', 2, 'points', 1, 0, 100, 2);
INSERT INTO `user_bigdata` (`ID`, `usr_id`, `action_type`, `action_id`, `question_id`, `value`, `type_utilization`) VALUES ('', 2, 'points', 1, 0, 80, 2);
INSERT INTO `user_bigdata` (`ID`, `usr_id`, `action_type`, `action_id`, `question_id`, `value`, `type_utilization`) VALUES ('', 2, 'points_tips', 1, 0, 10, 2);

INSERT INTO `user_bigdata` (`ID`, `usr_id`, `action_type`, `action_id`, `question_id`, `value`, `type_utilization`) VALUES ('', 3, 'points', 1, 0, 'giveup', 2);

INSERT INTO  `olympics` (`ID`, `active`, `title`, `start_date`, `end_date`) VALUES (1, 1, "Jogo", 0, DATE_ADD(NOW(), INTERVAL -1 DAY), DATE_ADD(NOW(), INTERVAL +10 DAY));

INSERT INTO  `olympic_players_niveis` (`ID`, `user_id`, `nivel_id`, `points`, `olympic_id`, `created`, `tip_resolved`)
VALUES ('', 1, 1, 30, 1, now(), null); 

INSERT INTO  `olympic_players_niveis` (`ID`, `user_id`, `nivel_id`, `points`, `olympic_id`, `created`, `tip_resolved`)
VALUES ('', 2, 1, 130, 1, now(), null); 

INSERT INTO `users` (`ID`, `username`, `role`, `password`) VALUES (1, 'ononnoonon1', 'nonono', 'onnonono');
INSERT INTO `users` (`ID`, `username`, `role`, `password`) VALUES (2, 'ononnoonon2', 'nonono', 'onnonono');
INSERT INTO `users` (`ID`, `username`, `role`, `password`) VALUES (3, 'ononnoonon2', 'nonono', 'onnonono');

SQL Fiddle

Answer 1

Like I did to solve the problems:

select user_id_int, total_points , sum(pontos_obtidos_period) as pontos_periodo, u.avatar_json, ano from (

                    SELECT user_id_int, total_points , ub.action_id, ub.question_id, start_date, end_date, ano,
                    max(CAST(ub.value as UNSIGNED))  AS pontos_obtidos_period  

                    FROM (
                            SELECT 
                                SUM(opn.points) AS `total_points`,
                                ol.start_date AS start_date,
                                ol.end_date AS end_date,
                                opn.user_id as user_id_int,
                                (case when sc.scc_year is null then opi.opi_school_year else sc.scc_year end) as ano

                            FROM
                                `olympic_players_niveis` AS `opn`
                                    INNER JOIN
                                olympics ol ON ( ol.ID = opn.olympic_id AND ol.ID = 7 )

                                 left JOIN school_class_members as scm ON(scm.usr_id = opn.user_id) 
                                 left JOIN Schools as s ON(s.ID = scm.sch_id) 
                                 left JOIN school_classes as sc ON(sc.sch_id = s.ID and scm.cls_id = sc.scc_id and sc.scc_status = 1) 
                                 left join Planos as p on p.ID = s.plan and (p.data_exp > CURDATE())
                                 left join olympic_players_initial opi on opi.usr_id = opn.user_id


                            GROUP BY `opn`.`user_id`
                            ORDER BY `opn`.`created` DESC , FIELD(opn.tip_resolved, 1, NULL) DESC
                    ) AS `tab` 

                    left join user_bigdata ub 
                    on (ub.usr_id = user_id_int 
                    and ub.action_type = 'points' 
                    and ub.value <> 'giveup' 
                     and ub.created between start_date and end_date)

                    group by user_id_int, ub.action_id
) as tab4 
inner join Users u on u.ID = user_id_int

group by user_id_int
order by total_points desc, pontos_periodo desc;

Answer 2

@Ivan, I saw that you changed your table by adding a new table and changing the field of others. But like your data from INSERT are not updated, I could not test with the new structure.

But based on the structure of this example that you passed, created this SQL. I believe that this meets your need and also believe that it is easy to understand and adapt.

Follows the query:

SELECT 
SUM(opn.points) as points,
new.total_pontos,
ol.start_date AS start_d,
ol.end_date AS end_d,
opn.user_id
FROM olympic_players_niveis AS opn
INNER JOIN olympics ol ON (ol.active = 1 AND ol.ID = opn.olympic_id)
INNER JOIN (
    SELECT ub.usr_id, SUM(ub.value) as total_pontos
    FROM user_bigdata ub
    INNER JOIN olympic_players_niveis o ON (o.ID = ub.usr_id)
    INNER JOIN olympics ol ON (ol.id = o.olympic_id)
    WHERE ub.action_type IN ('points') AND ub.created BETWEEN ol.start_date AND ol.end_date AND ol.active = 1
    GROUP BY ub.usr_id
) new ON (new.usr_id = opn.user_id)
GROUP BY opn.user_id

If you want to return only a specific user, just put

WHERE opn.user_id = ?

Before the GROUP BY, where the ? you replaced by id user’s.