Running Total for Different Genders - Problem

You are given a Scores table that tracks player scores in a competition between female and male teams.

Table: Scores

  • player_name (varchar): Name of the player
  • gender (varchar): 'F' for female team, 'M' for male team
  • day (date): Date when the score was recorded
  • score_points (int): Points scored by the player

The combination of (gender, day) forms the primary key.

Task: Find the total score for each gender on each day. Return results ordered by gender and day in ascending order.

Table Schema

Scores
Column Name Type Description
player_name varchar Name of the player
gender PK varchar 'F' for female team, 'M' for male team
day PK date Date when the score was recorded
score_points int Points scored by the player on that day
Primary Key: (gender, day)
Note: Each row represents a player's score on a specific day. Multiple players can score on the same day.

Input & Output

Example 1 — Basic Competition Scores
Input Table:
player_name gender day score_points
Alice F 2019-12-30 100
Bob M 2019-12-30 130
John M 2019-12-31 120
Mary F 2019-12-31 80
Output:
gender day total
F 2019-12-30 100
F 2019-12-31 80
M 2019-12-30 130
M 2019-12-31 120
💡 Note:

We group by gender and day, then sum the scores. Female team scored 100 on Dec 30 and 80 on Dec 31. Male team scored 130 on Dec 30 and 120 on Dec 31. Results are ordered by gender (F before M) and day (ascending).
Example 2 — Multiple Players Same Day
Input Table:
player_name gender day score_points
Alice F 2019-12-30 50
Betty F 2019-12-30 60
Bob M 2019-12-30 80
John M 2019-12-30 40
Output:
gender day total
F 2019-12-30 110
M 2019-12-30 120
💡 Note:

Multiple players from each team scored on the same day. Female team total: Alice (50) + Betty (60) = 110. Male team total: Bob (80) + John (40) = 120. Both teams competed on the same day.

Constraints

  • 1 ≤ player_name.length ≤ 20
  • gender is either 'F' or 'M'
  • day is a valid date
  • 1 ≤ score_points ≤ 1000

Visualization

Tap to expand
Running Total for Different Genders INPUT gender day score F 2024-01-01 10 F 2024-01-01 15 F 2024-01-02 20 M 2024-01-01 25 M 2024-01-02 30 M 2024-01-02 5 Scores Table Competition Data Multiple scores per gender and day Need: Running totals ALGORITHM STEPS 1 GROUP BY Group by gender, day 2 SUM Scores SUM(score) per group 3 Window Function Running total with PARTITION BY gender 4 ORDER BY Sort: gender, day ASC SELECT gender, day, SUM(score) AS score, SUM(SUM(score)) OVER( PARTITION BY gender ORDER BY day) GROUP BY gender, day FINAL RESULT gender day score total F 01-01 25 25 F 01-02 20 45 M 01-01 25 25 M 01-02 35 60 Running Total Calculation F: 25 --> 25+20 = 45 M: 25 --> 25+35 = 60 Separate totals per gender OK - Complete! Ordered by gender, day with cumulative scores Key Insight: Use SUM() twice: inner SUM for daily aggregation per gender, outer SUM() OVER() for running total. PARTITION BY gender ensures each gender has its own independent running total sequence. ORDER BY day in window function controls cumulative sum order within each partition. TutorialsPoint - Running Total for Different Genders | Optimal Solution

Related Problems

Asked in
Facebook 23 Amazon 18 Google 15
23.4K Views
Medium Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen