Daily Cumulative Scores by Gender

Medium

A competition analytics service records individual point events in this table: scores columns: player_name: participant name gender: participant group label (F or M) day: event date in YYYY-MM-DD format score_points: points scored on that day Data guarantee: (gender, day) is unique, so each gender has at most one row per date. Task: For every row, compute the cumulative score for that gender from its earliest recorded day up to the current day. Output requirements: Return exactly these columns: gender, day, total total must be the running sum of score_points within each gender by day Return rows ordered by gender ascending, then day ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

scores:
| 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           |

Output

[
  {"gender":"F","day":"2019-12-30","total":17},
  {"gender":"F","day":"2019-12-31","total":40},
  {"gender":"F","day":"2020-01-01","total":57},
  {"gender":"F","day":"2020-01-07","total":80},
  {"gender":"M","day":"2019-12-18","total":2},
  {"gender":"M","day":"2019-12-25","total":13},
  {"gender":"M","day":"2019-12-30","total":26},
  {"gender":"M","day":"2019-12-31","total":29},
  {"gender":"M","day":"2020-01-07","total":36}
]

Explanation

Compute cumulative totals independently for F and M after sorting each gender by date.

Example 2

Input

scores:
| player_name | gender | day        | score_points |
|-------------|--------|------------|--------------|
| Ana         | F      | 2023-04-02 | 5            |
| Bea         | F      | 2023-04-01 | 3            |
| Cara        | F      | 2023-04-05 | 7            |

Output

[
  {"gender":"F","day":"2023-04-01","total":3},
  {"gender":"F","day":"2023-04-02","total":8},
  {"gender":"F","day":"2023-04-05","total":15}
]

Explanation

Only one gender is present, so the result is one chronological running sequence.

Example 3

Input

scores:
| player_name | gender | day        | score_points |
|-------------|--------|------------|--------------|
| Max         | M      | 2022-08-03 | 10           |
| Mira        | F      | 2022-08-03 | 8            |
| Moe         | M      | 2022-08-04 | 0            |
| Faye        | F      | 2022-08-04 | 6            |

Output

[
  {"gender":"F","day":"2022-08-03","total":8},
  {"gender":"F","day":"2022-08-04","total":14},
  {"gender":"M","day":"2022-08-03","total":10},
  {"gender":"M","day":"2022-08-04","total":10}
]

Explanation

A zero score still contributes a valid row; cumulative totals remain non-decreasing when scores are non-negative.

Constraints

0 <= number of rows in scores <= 100000
player_name is a non-empty string
gender is either 'F' or 'M'
day is a valid ISO date in YYYY-MM-DD format
0 <= score_points <= 1000000
(gender, day) is unique
Return one output row per input row
Return columns exactly as: gender, day, total
Sort output by gender ascending, then day ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: gender, day, total

scores: | 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 |

[ {"gender":"F","day":"2019-12-30","total":17}, {"gender":"F","day":"2019-12-31","total":40}, {"gender":"F","day":"2020-01-01","total":57}, {"gender":"F","day":"2020-01-07","total":80}, {"gender":"M","day":"2019-12-18","total":2}, {"gender":"M","day":"2019-12-25","total":13}, {"gender":"M","day":"2019-12-30","total":26}, {"gender":"M","day":"2019-12-31","total":29}, {"gender":"M","day":"2020-01-07","total":36} ]

scores: | player_name | gender | day | score_points | |-------------|--------|------------|--------------| | Ana | F | 2023-04-02 | 5 | | Bea | F | 2023-04-01 | 3 | | Cara | F | 2023-04-05 | 7 |

scores: | player_name | gender | day | score_points | |-------------|--------|------------|--------------| | Max | M | 2022-08-03 | 10 | | Mira | F | 2022-08-03 | 8 | | Moe | M | 2022-08-04 | 0 | | Faye | F | 2022-08-04 | 6 |