Knowledge Guide
Home › Databases › SQL Practice Problems

Running Total for Different Genders

Problem

Table: Scores

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| player_name   | varchar |
| gender        | varchar |
| day           | date    |
| score_points  | int     |
+---------------+---------+
(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 Definition

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.

Example

Image
Image

Output

Image
Image

Try It Yourself

java
-- TODO: Write your user queries here

Solution

To determine the total score for each gender on each day in the competition, we need to aggregate the score_points earned by players of each gender on each specific day. This involves summing up the scores of all players belonging to the same gender for each day.

SQL Query

SELECT s1.gender,
       s1.day,
       Sum(s2.score_points) AS total
FROM   Scores s1
       LEFT JOIN Scores s2
              ON s1.gender = s2.gender
                 AND s1.day >= s2.day
GROUP  BY 1,
          2
ORDER  BY 1,
          2

Step-by-Step Approach

Step 1: Understanding the Self-Join

To calculate the cumulative score for each gender up to each day, we perform a self-join on the Scores table. This allows us to pair each record (s1) with all records (s2) of the same gender that occurred on or before the same day.

SQL Snippet:

FROM   Scores s1
       LEFT JOIN Scores s2
              ON s1.gender = s2.gender
                 AND s1.day >= s2.day

Explanation:

Step 2: Aggregating the Scores

Sum the score_points from all relevant s2 records for each gender and day to compute the cumulative total score up to that day.

SQL Snippet:

SELECT s1.gender,
       s1.day,
       Sum(s2.score_points) AS total
FROM   Scores s1
       LEFT JOIN Scores s2
              ON s1.gender = s2.gender
                 AND s1.day >= s2.day
GROUP  BY 1,
          2

Explanation:

Intermediate Output After Step 2:

+--------+------------+-------+
| gender | day        | total |
+--------+------------+-------+
| F      | 2020-01-01 | 57    |
| F      | 2020-01-07 | 80    |
| M      | 2020-01-07 | 36    |
| M      | 2019-12-25 | 13    |
| M      | 2019-12-30 | 26    |
| M      | 2019-12-31 | 29    |
| M      | 2019-12-18 | 2     |
| F      | 2019-12-31 | 40    |
| F      | 2019-12-30 | 17    |
+--------+------------+-------+

Explanation of Aggregated Scores:

Step 3: Ordering the Results

Sort the aggregated results first by gender and then by day, both in ascending order, to present the data systematically.

SQL Snippet:

ORDER  BY 1,
          2

Explanation:

Final Output:

+--------+------------+-------+
| 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    |
+--------+------------+-------+
🤖 Don't fully get this? Learn it with Claude

Stuck on Running Total for Different Genders? Open Claude, copy a block below, and it'll teach you this exact concept — visually and interactively.

🎨 Explain it visually

Build the mental picture, not memorization.

I just read a lesson on **Running Total for Different Genders** (Databases) and want to truly understand it. Explain Running Total for Different Genders from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.
🤔 Walk me through it (interactive)

Socratic — adapts to where you're stuck.

Teach me **Running Total for Different Genders** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.
🧪 Quiz me & fix my gaps

Active recall exposes what you missed.

Quiz me on **Running Total for Different Genders** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.
🧠 Make it stick

Intuition + hook + flashcards for long-term memory.

Help me remember **Running Total for Different Genders** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

📝 My notes

← Page RecommendationsEvaluate Boolean Expression →