7/9 test cases, and stumped


  • 0
    S

    I'm trying to get my intuitive approach correct (prior to having looked at Solution)... can someone help me debug?

    SET @rank = 0;
    
    SELECT ranked.Id, ranked.Company, ranked.Salary
    FROM (
        SELECT CASE WHEN @rank >= (
                            SELECT COUNT(*)
                            FROM Employee e2
                            WHERE e2.Company = e1.Company
                            )
                THEN @rank:=1
                ELSE @rank:=@rank+1 END AS col_index,
            Id, Company, Salary
        FROM Employee e1
        ORDER BY Company, Salary, Id
    ) ranked
    
    LEFT JOIN
    
    (SELECT Company, FLOOR((COUNT(*)+1)/2) AS left_index, FLOOR((COUNT(*)+2)/2) AS right_index
    FROM Employee
    GROUP BY Company) indexes
    
    ON indexes.Company = ranked.Company 
    WHERE indexes.left_index = ranked.col_index OR indexes.right_index = ranked.col_index
    
    

    As a side note, I think the problem could be better specified.

    • When a list is even, the median should be the average of the two middle numbers.
    • What if there are more than two employees in a company with the median salary value? They way this problem is framed, we could not reliably come up with the same Id numbers (unless we sort by Id in the ranking, but this is an uncorrelated field).

Log in to reply
 

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