Find Cumulative Salary of an Employee - Solution & Explanation

HardPremiumFree on FleetCodeDatabase5 min readAsked at: Amazon

Problem Statement

Table: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| month       | int  |
| salary      | int  |
+-------------+------+
(id, month) is the primary key (combination of columns with unique values) for this table.
Each row in the table indicates the salary of an employee in one month during the year 2020.

Write a solution to calculate the cumulative salary summary for every employee in a single unified table.

The cumulative salary summary for an employee can be calculated as follows:

For each month that the employee worked, sum up the salaries in that month and the previous two months. This is their 3-month sum for that month. If an employee did not work for the company in previous months, their effective salary for those months is 0.
Do not include the 3-month sum for the most recent month that the employee worked for in the summary.
Do not include the 3-month sum for any month the employee did not work.

Return the result table ordered by id in ascending order. In case of a tie, order it by month in descending order.

The result format is in the following example.

Example 1:

Input: 
Employee table:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 1  | 2     | 30     |
| 2  | 2     | 30     |
| 3  | 2     | 40     |
| 1  | 3     | 40     |
| 3  | 3     | 60     |
| 1  | 4     | 60     |
| 3  | 4     | 70     |
| 1  | 7     | 90     |
| 1  | 8     | 90     |
+----+-------+--------+
Output: 
+----+-------+--------+
| id | month | Salary |
+----+-------+--------+
| 1  | 7     | 90     |
| 1  | 4     | 130    |
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 3  | 3     | 100    |
| 3  | 2     | 40     |
+----+-------+--------+
Explanation: 
Employee '1' has five salary records excluding their most recent month '8':
- 90 for month '7'.
- 60 for month '4'.
- 40 for month '3'.
- 30 for month '2'.
- 20 for month '1'.
So the cumulative salary summary for this employee is:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 1  | 7     | 90     |  (90 + 0 + 0)
| 1  | 4     | 130    |  (60 + 40 + 30)
| 1  | 3     | 90     |  (40 + 30 + 20)
| 1  | 2     | 50     |  (30 + 20 + 0)
| 1  | 1     | 20     |  (20 + 0 + 0)
+----+-------+--------+
Note that the 3-month sum for month '7' is 90 because they did not work during month '6' or month '5'.

Employee '2' only has one salary record (month '1') excluding their most recent month '2'.
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 2  | 1     | 20     |  (20 + 0 + 0)
+----+-------+--------+

Employee '3' has two salary records excluding their most recent month '4':
- 60 for month '3'.
- 40 for month '2'.
So the cumulative salary summary for this employee is:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 3  | 3     | 100    |  (60 + 40 + 0)
| 3  | 2     | 40     |  (40 + 0 + 0)
+----+-------+--------+

Approach Overview

Problem Overview: You have an Employee table with id, month, and salary. For every employee and month, compute the cumulative salary for that month plus the previous two months. The most recent month for each employee is excluded because future months may change the rolling total.

Approach 1: Self Join Rolling Window (O(n log n) time, O(1) extra space)

Join the table with itself to simulate a rolling three‑month window. Treat one instance (e1) as the current row and the other (e2) as rows contributing to the cumulative sum. Match rows where e1.id = e2.id and e2.month falls in the range e1.month - 2 to e1.month. Aggregate with SUM(e2.salary) and group by e1.id and e1.month. Finally, filter out the latest month for each employee using a subquery that checks MAX(month). This approach works in nearly every SQL engine and demonstrates how to simulate a sliding window using joins. It relies on efficient indexing and grouping operations commonly discussed in database query optimization.

Approach 2: Window Function with SUM OVER (O(n log n) time, O(1) space)

Modern SQL engines support window functions, which simplify rolling calculations. Partition the rows by id and order them by month. Use SUM(salary) OVER (PARTITION BY id ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) to compute the cumulative salary for the last three months. After computing the rolling total, exclude the most recent month per employee using a condition comparing month with the partition's maximum month. This version avoids explicit joins and is usually easier to read and maintain. Window functions are a common advanced pattern in SQL and analytical queries involving database aggregations.

Recommended for interviews: The self‑join solution shows you understand how to build rolling aggregates without advanced SQL features. Interviewers often accept it because it works across many database systems. The window function version is cleaner and closer to how production analytics queries are written. Demonstrating both approaches signals strong SQL fundamentals and familiarity with modern query capabilities.

Solution

Code

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

Approach	Time	Space	When to Use
Self Join Rolling Window	O(n log n)	O(1)	When window functions are unavailable or when demonstrating SQL fundamentals in interviews
Window Function (SUM OVER)	O(n log n)	O(1)	Preferred for modern SQL engines like MySQL 8+, PostgreSQL, and analytical queries

Video Solution

Leetcode HARD 579 - Cumulative Salary of Employee RANGE vs ROW BETWEEN in SQL - Explained by EDS • Everyday Data Science • 1,343 views views

Watch 4 more video solutions →

Frequently Asked Questions

Is Find Cumulative Salary of an Employee easy or hard?

The problem is classified as Hard because it combines rolling window logic, grouping, and filtering of the latest month per employee. Many candidates struggle with expressing sliding window logic purely in SQL without procedural loops.

Find Cumulative Salary of an Employee Python/Java solution

This problem is designed for SQL rather than general‑purpose languages. On LeetCode and similar platforms, the expected answer is a database query written in MySQL or another SQL dialect using joins or window functions to compute rolling salary totals.

How to solve Find Cumulative Salary of an Employee in O(n)?

In practice, SQL engines internally sort partitions, so the query behaves closer to O(n log n). Conceptually, using a window function like SUM(salary) OVER (PARTITION BY id ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) processes each row once while maintaining a rolling window of three months.

What is the best approach for Find Cumulative Salary of an Employee?

The most practical approach uses a SQL window function with SUM() OVER and a frame of two preceding rows to the current row. It directly computes the rolling three‑month salary for each employee partition. In environments where window functions are unavailable, a self‑join with a month range condition produces the same result.

Is Find Cumulative Salary of an Employee asked at Google/Amazon/Meta?

Rolling aggregation and window function problems frequently appear in SQL interviews at companies like Amazon, Google, and Meta. Variants often involve calculating moving averages, cumulative totals, or time‑window metrics over event tables.

What data structure is used in Find Cumulative Salary of an Employee?

The problem primarily relies on relational database operations rather than traditional data structures. Internally, the solution uses SQL constructs such as partitions, ordered windows, grouping, and joins to simulate a sliding window over monthly salary records.

What is the time complexity of Find Cumulative Salary of an Employee?

Most SQL solutions run in roughly O(n log n) time due to sorting by employee and month during grouping or window processing. The query scans rows once per partition and aggregates at most three months of data for each result row. Space complexity is typically O(1) outside of the database engine’s internal execution structures.

Ready to solve this problem?

Practice Find Cumulative Salary of an Employee with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyHard

Acceptance47.8%

Approaches1

Reading time5 min

Asked at

Amazon

Practice this problem

Open in Editor