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


  • 0
    P

    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]]}


  • 0
    This post is deleted!

  • 0
    H
    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.

    0_1497244870896_Screen Shot 2017-06-11 at 10.20.30 PM.png


  • 0
    D

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


  • 0
    Z

    I encountered the same issue:
    0_1497838437911_d93341a2-45ba-4eda-8175-469109d66967-image.png


  • 0
    L

    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


  • 0
    Z

    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
    

Log in to reply
 

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