Incorrect Test case .......Eat lot of my time

• Hi moderators Test case are not correct Please correct the same
INPUT
{"headers": {"Employee": ["Id", "Month", "Salary"]}, "rows": {"Employee": [[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,5,70]]}}
OUTPUT:
{"headers": ["Id", "Month", "Salary"], "values": [[1, 4, 150], [1, 3, 90], [1, 2, 50], [1, 1, 20], [2, 1, 20], [3, 3, 100], [3, 2, 40]]}
EXPECTED :
{"headers": ["Id", "Month", "Salary"], "values": [[1, 4, 130], [1, 3, 90], [1, 2, 50], [1, 1, 20], [2, 1, 20], [3, 3, 100], [3, 2, 40]]}

• This post is deleted!

• ``````select id 'Id', month 'Month', csum div 1 'Salary'
from (
select id, month,
@csum:=if(@prev <> id, salary, @csum:= @csum + salary) csum,
@prev:=id
from (
select e.id, month, salary
from employee e,
(select id, max(month) recent_m, count(1) cnt from employee group by 1) m
where e.id = m.id
and e.month < recent_m and e.month >= if (cnt < 3, recent_m - 1, recent_m - 3)
order by 1, 2
) emp, (select @csum:=0, @prev:='') i
) f
order by 1,2 desc
``````

Here is my sql but the expected output is not correct for employee #1
Input
[1, 1, 20],
[1, 2, 30],
[1,3,40],
[1,4,60],
[1,5,70]

Month 2,3,4 should be displayed with accum salary as:
30, 70, 130 which is what my sql returns but the expected output is
20, 50, 90, 130 which is incorrect in my opinion.

• @helensolau I encountered similar case. My test case is the example in the question. The answer doesn't have (2,2,50)

• I encountered the same issue:

• Description is "Write a SQL to get the cumulative sum of an employee's salary over a period of 3 months but exclude the most recent month.".
For id=1 and month=4, the list of salary is

• (id=1,month=2,salary=30),

• (id=1,month=3,salary=40) and

• (id=1,month=4,salary=60)

so, the sum is 130 not 150

• Ran into the same problem here.

``````# Write your MySQL query statement below

select
t1.Id as id,
t1.Month as month,
sum(t2.Salary) as Salary
from
(
select
t1.Id,Month,Salary
from Employee as t1
join (select Id, max(Month) as max_month from Employee group by 1) as t2 on t1.Id = t2.Id and t1.Month <> t2.max_month
) as t1
left join
(
select
t1.Id,Month,Salary
from Employee as t1
join (select Id, max(Month) as max_month from Employee group by 1) as t2 on t1.Id = t2.Id and t1.Month <> t2.max_month
) as t2 on t1.Id = t2.Id and t1.Month>=t2.Month
group by 1,2
order by Id, Month desc
``````

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.