Non aggregate columns, SUM, MAX and GROUP BY

Question

I have the following activity table.

CREATE TABLE IF NOT EXISTS activity(  
user_id int(11) unsigned NOT NULL,  
create_date date NOT NULL,  
start_time int(4) NOT NULL,
field_1 int(4),
field_2 int(4),  
field_3 int(4),
PRIMARY KEY (user_id, create_date, start_time)  
);

insert into activity (user_id, create_date,start_time, field_1, field_2, field_3) values  
(1, '2017-03-01', 0, 1, 1, 1),  
(1, '2017-03-01', 30, 2, 2, 2),  
(1, '2017-03-01', 60, 3, 3, 3),  
(1, '2017-03-02', 0, 4, 4, 4),  
(1, '2017-03-02', 30, 5, 5, 5),  
(1, '2017-03-02', 60, 6, 6, 6),  
(1, '2017-03-03', 0, 7, 7, 7),  
(1, '2017-03-03', 30, 8, 8, 8),  
(1, '2017-03-03', 60, 9, 9, 9),    
(2, '2017-03-04', 0, 1, 1, 1),  
(2, '2017-03-04', 30, 2, 2, 2),  
(2, '2017-03-04', 60, 3, 3, 3),  
(2, '2017-03-05', 0, 4, 4, 4),  
(2, '2017-03-05', 30, 5, 5, 5),  
(2, '2017-03-05', 60, 6, 6, 6),  
(2, '2017-03-06', 0, 7, 7, 7),  
(2, '2017-03-06', 30, 8, 8, 8),  
(2, '2017-03-06', 60, 9, 9, 9)  

select * from activity;

| user_id | create_date | start_time | field_1 | field_2 | field_3 |    
|       1 | 2017-03-01  |          0 |       1 |       1 |       1 |  
|       1 | 2017-03-01  |         30 |       2 |       2 |       2 |  
|       1 | 2017-03-01  |         60 |       3 |       3 |       3 |  
|       1 | 2017-03-02  |          0 |       4 |       4 |       4 |  
|       1 | 2017-03-02  |         30 |       5 |       5 |       5 |  
|       1 | 2017-03-02  |         60 |       6 |       6 |       6 |  
|       1 | 2017-03-03  |          0 |       7 |       7 |       7 |  
|       1 | 2017-03-03  |         30 |       8 |       8 |       8 |  
|       1 | 2017-03-03  |         60 |       9 |       9 |       9 | 
|       2 | 2017-03-04  |          0 |       1 |       1 |       1 |  
|       2 | 2017-03-04  |         30 |       2 |       2 |       2 |  
|       2 | 2017-03-04  |         60 |       3 |       3 |       3 |  
|       2 | 2017-03-05  |          0 |       4 |       4 |       4 |  
|       2 | 2017-03-05  |         30 |       5 |       5 |       5 |  
|       2 | 2017-03-05  |         60 |       6 |       6 |       6 |  
|       2 | 2017-03-06  |          0 |       7 |       7 |       7 |  
|       2 | 2017-03-06  |         30 |       8 |       8 |       8 |  
|       2 | 2017-03-06  |         60 |       9 |       9 |       9 |  

What I want is for each user_id, the date of which the sum of field_1, field_2, field_3 is maximum. In the example above, the query result should be

| user_id | create_date | best_active_time |  
|       1 | 2017-03-03  | 72               |  
|       2 | 2017-03-06  | 72               | 

The question is related to getting a non-aggregate column (create_date) and using the GROUP BY clause. Currently, I have my solution, and I just wonder whether there exist better solutions (I feel that they exist).

SELECT bb.user_id, bb.create_date, cc.best_active_time FROM   
(  
SELECT user_id, create_date, MAX(active_time) best_active_time FROM  
(SELECT user_id, create_date, (SUM(field_1) + SUM(field_2) + SUM(field_3)) as   active_time  FROM activity GROUP BY user_id, create_date) ACT GROUP BY user_id, create_date  
) bb  
INNER JOIN  
(  
SELECT user_id,  MAX(active_time) best_active_time FROM  
(SELECT user_id, (SUM(field_1) + SUM(field_2) + SUM(field_3)) as active_time    FROM activity GROUP BY user_id, create_date) ACT GROUP BY user_id  
) cc ON bb.user_id = cc.user_id AND bb.best_active_time = cc.best_active_time   

Answer 1

You can use variables to simulate row_number() in mysql.

SQL DEMO

SELECT *
FROM (
        SELECT a.*,
               @rn := if(@user = user_id, 
                         @rn + 1 ,
                         if(@user := user_id,1,1)
                        ) as rn
        FROM (        
                SELECT user_id, create_date, SUM(field_1 + field_2 + field_3) as total
                FROM activity a
                GROUP BY user_id, create_date
             ) a
        CROSS JOIN (SELECT @user := 0, @rn := 0) as t
        ORDER BY user_id, total DESC
    ) t
WHERE rn =1  

OUTPUT: