running-total-for-different-genders.sql

/*

Table: Scores

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| player_name   | varchar |
| gender        | varchar |
| day           | date    |
| score_points  | int     |
+---------------+---------+
(gender, day) is the primary key for this table.
A competition is held between females team and males team.
Each row of this table indicates that a player_name and with gender has scored score_point in someday.
Gender is 'F' if the player is in females team and 'M' if the player is in males team.
Write an SQL query to find the total score for each gender at each day.

Order the result table by gender and day

The query result format is in the following example:

Scores table:
+-------------+--------+------------+--------------+
| player_name | gender | day        | score_points |
+-------------+--------+------------+--------------+
| Aron        | F      | 2020-01-01 | 17           |
| Alice       | F      | 2020-01-07 | 23           |
| Bajrang     | M      | 2020-01-07 | 7            |
| Khali       | M      | 2019-12-25 | 11           |
| Slaman      | M      | 2019-12-30 | 13           |
| Joe         | M      | 2019-12-31 | 3            |
| Jose        | M      | 2019-12-18 | 2            |
| Priya       | F      | 2019-12-31 | 23           |
| Priyanka    | F      | 2019-12-30 | 17           |
+-------------+--------+------------+--------------+
Result table:
+--------+------------+-------+
| gender | day        | total |
+--------+------------+-------+
| F      | 2019-12-30 | 17    |
| F      | 2019-12-31 | 40    |
| F      | 2020-01-01 | 57    |
| F      | 2020-01-07 | 80    |
| M      | 2019-12-18 | 2     |
| M      | 2019-12-25 | 13    |
| M      | 2019-12-30 | 26    |
| M      | 2019-12-31 | 29    |
| M      | 2020-01-07 | 36    |
+--------+------------+-------+
For females team:
First day is 2019-12-30, Priyanka scored 17 points and the total score for the team is 17.
Second day is 2019-12-31, Priya scored 23 points and the total score for the team is 40.
Third day is 2020-01-01, Aron scored 17 points and the total score for the team is 57.
Fourth day is 2020-01-07, Alice scored 23 points and the total score for the team is 80.
For males team:
First day is 2019-12-18, Jose scored 2 points and the total score for the team is 2.
Second day is 2019-12-25, Khali scored 11 points and the total score for the team is 13.
Third day is 2019-12-30, Slaman scored 13 points and the total score for the team is 26.
Fourth day is 2019-12-31, Joe scored 3 points and the total score for the team is 29.
Fifth day is 2020-01-07, Bajrang scored 7 points and the total score for the 

*/


# V0
WITH _f AS
  (SELECT gender,
          DAY,
          SUM(score_points) OVER (PARTITION BY gender
                                  ORDER BY DAY) AS total
   FROM Scores
   WHERE gender = 'F'
   GROUP BY gender,
            DAY),
     _m AS
  (SELECT gender,
          DAY,
          SUM(score_points) OVER (PARTITION BY gender
                                  ORDER BY DAY) AS total
   FROM Scores
   WHERE gender = 'M'
   GROUP BY gender,
            DAY)
SELECT _f.*
UNION ALL _m.*
ORDER BY gender,
         DAY;

# V1
# https://zhuanlan.zhihu.com/p/262915226
SELECT gender, day, SUM(score_points) OVER(PARTITION BY gender ORDER BY day) AS total
FROM Scores
GROUP BY gender, day
ORDER BY gender, day;

# V1'
# https://code.dennyzhang.com/running-total-for-different-genders
select t1.gender, t1.day, sum(t2.score_points) as total
from Scores as t1 inner join Scores as t2
on t1.gender = t2.gender
and t1.day>=t2.day
group by t1.gender, t1.day

# V2
# Time:  O(nlogn)
# Space: O(n)
SELECT gender, 
       day, 
       CASE 
         WHEN gender = 'F' THEN @f_accu := @f_accu + score_points 
         ELSE @m_accu := @m_accu + score_points 
       END total 
FROM   scores, 
       (SELECT @f_accu := 0, 
               @m_accu := 0) init 
ORDER  BY gender, 
          day