Skip to main content
Talentd

Running Total for Different Genders - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min read
Practice this problem

Problem Statement

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.

 

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_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 | 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    |
+--------+------------+-------+
Explanation: 
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.

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.

Approach Overview

Problem Overview: The table stores daily score updates for players, including gender, day, and score_points. The task is to compute a running (cumulative) total of scores for each gender as days progress. Each row should show the total score accumulated by that gender up to that specific day.

Approach 1: Window Function with SUM() OVER (Optimal) (Time: O(n log n), Space: O(1) additional)

The cleanest solution uses a SQL window function. Partition the dataset by gender so each gender maintains its own running calculation. Within each partition, order rows by day, then apply SUM(score_points) OVER(PARTITION BY gender ORDER BY day). The database engine computes a cumulative sum as it scans the ordered rows. This avoids joins and keeps the query compact and efficient. Window functions are designed exactly for analytics problems like running totals and ranking. If you frequently solve SQL interview questions involving cumulative metrics, mastering window functions is essential. Related concepts appear often in SQL, database, and window functions practice.

Approach 2: Self Join for Cumulative Sum (Time: O(n^2), Space: O(1))

Before window functions were widely supported, cumulative totals were often computed using a self join. For each row s1, join all rows s2 with the same gender where s2.day <= s1.day. Summing s2.score_points produces the running total up to that day. While logically straightforward, this approach scales poorly because every row may join with many earlier rows. Large datasets quickly make this solution impractical.

Recommended for interviews: The window function approach is what interviewers expect today. It demonstrates familiarity with modern analytical SQL features and produces concise, readable queries. Mentioning the self‑join alternative shows you understand how cumulative aggregates work internally, but the window function solution shows stronger SQL fluency.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Window Function (SUM OVER PARTITION)O(n log n)O(1)Preferred modern SQL approach for running totals grouped by a category
Self Join Cumulative SumO(n^2)O(1)Useful when window functions are unavailable in older SQL engines

Video Solution

LeetCode Medium 1308 Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 6,520 views views

Watch 2 more video solutions →

Frequently Asked Questions

Is Running Total for Different Genders easy or hard?
The problem is generally considered Medium because it requires familiarity with SQL window functions and cumulative aggregation patterns. Developers comfortable with SUM() OVER(PARTITION BY ... ORDER BY ...) will find the solution straightforward.
Running Total for Different Genders Python/Java solution
This problem is a database query task, so the primary solution is written in SQL (commonly MySQL or PostgreSQL). Python or Java would typically only be used to execute the query against the database. The core logic remains the SQL window function computing the cumulative sum.
How to solve Running Total for Different Genders in O(n)?
Pure O(n) time is unlikely because rows must be ordered by day within each gender before computing the cumulative total. With proper indexing on (gender, day), the database can reduce sorting overhead and approach linear scanning. The query still uses SUM(score_points) OVER(PARTITION BY gender ORDER BY day) to generate the running totals.
What is the best approach for Running Total for Different Genders?
The best approach uses a SQL window function: SUM(score_points) OVER(PARTITION BY gender ORDER BY day). Partitioning separates calculations per gender, while ordering by day allows the database to compute a cumulative total. This approach is concise, efficient, and commonly expected in SQL interviews.
Is Running Total for Different Genders asked at Google/Amazon/Meta?
Running total and cumulative aggregation questions appear frequently in SQL interviews across companies like Amazon, Meta, and Google. Variations often involve computing rolling sums, daily cumulative metrics, or partitioned aggregates using window functions.
What data structure is used in Running Total for Different Genders?
The problem relies on relational table operations and SQL window functions rather than traditional in-memory data structures. Internally, the database engine uses sorted partitions and streaming aggregation to maintain the cumulative sum for each gender group.
What is the time complexity of Running Total for Different Genders?
The window function solution typically runs in O(n log n) time because the database must sort rows by gender and day before computing the cumulative sum. The running aggregation itself is linear after sorting. Space complexity is O(1) additional since the computation happens within the query execution pipeline.

Ready to solve this problem?

Practice Running Total for Different Genders with our built-in code editor and test cases.

Practice on FleetCode
Previous

Last Person to Fit in the Bus