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 playergender(varchar): 'F' for female team, 'M' for male teamday(date): Date when the score was recordedscore_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 -
genderis either'F'or'M' -
dayis a valid date -
1 ≤ score_points ≤ 1000
Visualization
Tap to expand
Understanding the Visualization
1
Input
Individual player scores by gender and day
2
GROUP BY
Aggregate scores by gender and day
3
Output
Daily team totals ordered by gender and day
Key Takeaway
🎯 Key Insight: Use GROUP BY to partition data by multiple columns and aggregate values within each group
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code