Running Total for Different Genders

Running Total for Different Genders - Problem

Database Medium

You're organizing a competitive scoring system for a mixed-gender tournament! 🏆 Each day, players from both the female team ('F') and male team ('M') score points in various competitions.

Your task is to calculate the cumulative running total for each gender on each day. This means you need to track how the total score builds up over time for both teams separately.

📊 Table Structure: Scores

Column Name	Type
player_name	varchar
gender	varchar
day	date
score_points	int

Key Points:

The combination (gender, day) forms the primary key
Gender is either 'F' (female) or 'M' (male)
Multiple players can score on the same day, but we aggregate by gender per day
You need to return results ordered by gender and day in ascending order

Goal: Calculate the running total score for each gender across all days, showing how the cumulative score grows over time.

Input & Output

example_1.sql — Basic Tournament Scores

$ 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 | 2020-01-07 | 18 | | Slaman | M | 2020-01-11 | 2 | +-------------+--------+------------+-------------+

› Output: +--------+------------+-------+ | gender | day | total | +--------+------------+-------+ | F | 2020-01-01 | 17 | | F | 2020-01-07 | 40 | | M | 2020-01-07 | 25 | | M | 2020-01-11 | 27 | +--------+------------+-------+

💡 Note: Female team: Day 1 has 17 points (total: 17). Day 7 adds 23 points (running total: 17+23=40). Male team: Day 7 has 7+18=25 points (total: 25). Day 11 adds 2 points (running total: 25+2=27).

example_2.sql — Same Day Multiple Players

$ Input: Scores table: +-------------+--------+------------+-------------+ | player_name | gender | day | score_points| +-------------+--------+------------+-------------+ | John | M | 2023-01-01 | 10 | | Jane | F | 2023-01-01 | 15 | | Bob | M | 2023-01-01 | 20 | | Alice | F | 2023-01-02 | 25 | +-------------+--------+------------+-------------+

› Output: +--------+------------+-------+ | gender | day | total | +--------+------------+-------+ | F | 2023-01-01 | 15 | | F | 2023-01-02 | 40 | | M | 2023-01-01 | 30 | +--------+------------+-------+

💡 Note: On 2023-01-01: Female team scores 15, Male team scores 10+20=30. On 2023-01-02: Female running total becomes 15+25=40, Male team has no new scores so stays at 30.

example_3.sql — Single Gender Tournament

$ Input: Scores table: +-------------+--------+------------+-------------+ | player_name | gender | day | score_points| +-------------+--------+------------+-------------+ | Sarah | F | 2023-01-01 | 30 | | Emma | F | 2023-01-02 | 20 | | Lisa | F | 2023-01-03 | 25 | +-------------+--------+------------+-------------+

› Output: +--------+------------+-------+ | gender | day | total | +--------+------------+-------+ | F | 2023-01-01 | 30 | | F | 2023-01-02 | 50 | | F | 2023-01-03 | 75 | +--------+------------+-------+

💡 Note: Only female players participate. Running totals: Day 1: 30, Day 2: 30+20=50, Day 3: 50+25=75. This tests edge case where only one gender is present.

Constraints

1 ≤ Number of rows in Scores table ≤ 1000
1 ≤ score_points ≤ 1000
gender is either 'F' or 'M'
day is a valid date string in format 'YYYY-MM-DD'
The combination (gender, day) is unique in the table

Visualization

Tap to expand

Understanding the Visualization

Daily Scores Collection

Each day, players compete and their scores are recorded with gender and date

Gender Partitioning

Separate the scores into male (M) and female (F) partitions

Chronological Processing

Process each gender's scores in date order to maintain running totals

Cumulative Display

Display running totals showing how each team's score grows over time

Key Takeaway

🎯 Key Insight: SQL window functions with PARTITION BY gender and ORDER BY day allow efficient calculation of running totals in a single pass, making this an optimal O(n log n) solution.

Asked in

f Meta 35 a Amazon 28 ⊞ Microsoft 22 G Google 18

This problem requires calculating running totals for each gender across multiple days. The optimal approach uses SQL window functions with SUM() OVER(PARTITION BY gender ORDER BY day) to efficiently compute cumulative sums. For non-SQL implementations, sort by gender and day, then maintain running totals with a single pass algorithm achieving O(n log n) time complexity.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Subquery for Each Row)	O(n²)	O(n)	Use correlated subqueries to calculate running total for each row
Window Function (Optimal SQL)	O(n log n)	O(n)	Use SQL window functions to calculate running totals in one pass

Brute Force (Subquery for Each Row) — Algorithm Steps

First, group scores by gender and day to get daily totals
For each gender-day combination, use a correlated subquery
Sum all scores for the same gender where date <= current date
Order results by gender and day

Visualization

Tap to expand

Step-by-Step Walkthrough

Group Daily Scores

First group all scores by gender and day to get daily totals

For Each Row

For each gender-day combination, start calculating running total

Nested Loop

Scan through all rows again to find matching gender with date <= current date

Sum Previous

Sum up all qualifying previous scores to get running total

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

typedef struct {
    char gender[2];
    char day[11];
    int scorePoints;
} DailyScore;

typedef struct {
    char gender[2];
    char day[11];
    int total;
} ResultRow;

int compare(const void *a, const void *b) {
    DailyScore *scoreA = (DailyScore *)a;
    DailyScore *scoreB = (DailyScore *)b;
    
    int genderCmp = strcmp(scoreA->gender, scoreB->gender);
    if (genderCmp != 0) return genderCmp;
    return strcmp(scoreA->day, scoreB->day);
}

ResultRow* solution(char scoresData[][4][20], int numRows, int *returnSize) {
    // Simple implementation for demonstration
    // Group by gender and day (simplified)
    DailyScore dailyScores[1000];
    int dailyCount = 0;
    
    // This is a simplified version - in reality would need proper grouping
    for (int i = 0; i < numRows; i++) {
        strcpy(dailyScores[dailyCount].gender, scoresData[i][1]);
        strcpy(dailyScores[dailyCount].day, scoresData[i][2]);
        dailyScores[dailyCount].scorePoints = atoi(scoresData[i][3]);
        dailyCount++;
    }
    
    qsort(dailyScores, dailyCount, sizeof(DailyScore), compare);
    
    ResultRow *result = malloc(dailyCount * sizeof(ResultRow));
    *returnSize = dailyCount;
    
    // Brute force running totals
    for (int i = 0; i < dailyCount; i++) {
        int runningTotal = 0;
        for (int j = 0; j < dailyCount; j++) {
            if (strcmp(dailyScores[j].gender, dailyScores[i].gender) == 0 &&
                strcmp(dailyScores[j].day, dailyScores[i].day) <= 0) {
                runningTotal += dailyScores[j].scorePoints;
            }
        }
        
        strcpy(result[i].gender, dailyScores[i].gender);
        strcpy(result[i].day, dailyScores[i].day);
        result[i].total = runningTotal;
    }
    
    return result;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each of n rows, we execute a subquery that scans up to n rows

⚠ Quadratic Growth

Space Complexity

O(n)

Storage for intermediate results and final output

⚡ Linearithmic Space

23.4K Views

Medium Frequency

~15 min Avg. Time

856 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