Description

Table: Scores

Column NameType
player_namevarchar
gendervarchar
daydate
score_pointsint
  • (gender, day) is the primary key (combination of columns with unique values) for this table.
  • A competition is held between the female team and the male 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 the female team and ‘M’ if the player is in the male team.

Problem Statement

Write a solution to find the total score for each gender on each day.

Return the result table ordered by gender and day in ascending order.

The result format is in the following example.

Example 1:

Input:

  • Scores table:
player_namegenderdayscore_points
AronF2020-01-0117
AliceF2020-01-0723
BajrangM2020-01-077
KhaliM2019-12-2511
SlamanM2019-12-3013
JoeM2019-12-313
JoseM2019-12-182
PriyaF2019-12-3123
PriyankaF2019-12-3017

Output:

genderdaytotal
F2019-12-3017
F2019-12-3140
F2020-01-0157
F2020-01-0780
M2019-12-182
M2019-12-2513
M2019-12-3026
M2019-12-3129
M2020-01-0736

Explanation:

  1. For the female team:

    • The first day is 2019-12-30, Priyanka scored 17 points and the total score for the team is 17.
    • The second day is 2019-12-31, Priya scored 23 points and the total score for the team is 40.
    • The third day is 2020-01-01, Aron scored 17 points and the total score for the team is 57.
    • The fourth day is 2020-01-07, Alice scored 23 points and the total score for the team is 80.

  2. For the male team:

    • The first day is 2019-12-18, Jose scored 2 points and the total score for the team is 2.
    • The second day is 2019-12-25, Khali scored 11 points and the total score for the team is 13.
    • The third day is 2019-12-30, Slaman scored 13 points and the total score for the team is 26.
    • The fourth day is 2019-12-31, Joe scored 3 points and the total score for the team is 29.
    • The fifth day is 2020-01-07, Bajrang scored 7 points and the total score for the team is 36.

Solution

This problem essentially requires us to find the running sum of score_points. The result needs to be per gender and it should be running sum per day (i.e. order by day). This can be achieved using window functions.

1SELECT 
2    gender, day, 
3    SUM(score_points) OVER (PARTITION BY gender ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total
4    FROM Scores
5    GROUP BY gender, day
6    ORDER BY gender, day;

In above query, I have used the ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW but you do not need to explicitly specify that. So, the query can be simplified to below.

1SELECT gender, day, 
2    SUM(score_points) OVER (PARTITION BY gender ORDER BY day) AS total
3    FROM Scores
4    GROUP BY gender, day
5    ORDER BY gender, day;