Scribd Logo
Upload

Welcome to Scribd!

  • 46 views

    There Is A Company in Which Earns

    Uploaded by

    Dhanush Babu
    There is a Company in Which Earns

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    There Is A Company in Which Earns

    Uploaded by

    Dhanush Babu
    46 views1 page
    There is a Company in Which Earns

    Original Title

    There is a Company in Which Earns

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    There is a Company in Which Earns

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    46 views1 page

    There Is A Company in Which Earns

    Uploaded by

    Dhanush Babu
    There is a Company in Which Earns

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 1

    Ask Playground Code Dhanush Babu

    There is a company in which each employee earns a salary and has at


    most one direct boss. Each employee may also have multiple indirect
    bosses.
    Employ...

    A N S W E R | P H I N D I N S TA N T M O D E L

    To solve this problem, we need to write an SQL query that finds the lowest-ranked indirect boss
    for each employee who earns at least twice as much as the employee. This involves several
    steps:

    1. Find the Lowest-Ranked Indirect Boss: We need to identify the lowest-ranked indirect boss
    for each employee. This means finding the boss with the fewest number of bosses above
    them in the hierarchy.

    2. Filter Bosses Who Earn at Least Twice as Much: For each employee, we need to filter out
    the bosses who earn less than twice the employee's salary.

    3. Select the Employee and Their Lowest-Ranked Boss: Finally, we need to select each
    employee along with their lowest-ranked boss who meets the salary criteria.

    Here's how you can write the SQL query to achieve this:

    WITH RECURSIVE boss_hierarchy AS (


    SELECT id, name, salary, boss_id
    FROM employees
    WHERE boss_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.salary, e.boss_id
    FROM employees e
    INNER JOIN boss_hierarchy bh ON e.boss_id = bh.id
    ),
    ranked_bosses AS (
    SELECT id, name, salary, boss_id,
    ROW_NUMBER() OVER(PARTITION BY boss_id ORDER BY id) AS rank
    FROM boss_hierarchy
    )
    SELECT e.id AS employee_id, rb.id AS boss_id
    FROM employees e
    JOIN ranked_bosses rb ON e.boss_id = rb.id
    WHERE rb.salary >= 2 * e.salary AND rb.rank = 1;

    This query works as follows:

    The boss_hierarchy CTE (Common Table Expression) recursively builds the hierarchy of
    bosses, starting from the top-level bosses (those with boss_id NULL).
    The ranked_bosses CTE then ranks the bosses under each direct boss, partitioning by
    boss_id and ordering by id to ensure the lowest-ranked boss gets the rank of 1.
    Finally, the main query selects each employee ( e ) and their lowest-ranked boss ( rb ) who
    earns at least twice as much as the employee. It filters out bosses based on the salary
    condition and ensures that only the lowest-ranked boss under each direct boss is
    considered.

    This query will return a table with employee_id and boss_id , where boss_id contains the ID
    of the lowest-ranked indirect boss who earns at least twice as much as the employee, or NULL if
    no such boss exists.

    Phind Instant Shareable

    SUGGESTIONS

    Can you explain how the recursive CTE works in the query?

    How does the `ROW_NUMBER()` function help in ranking the bosses under each direct boss?

    Is it possible to modify the query to also include the names of the employees and their bosses in the
    result table?

    What happens if there are multiple lowest-ranked bosses who earn at least twice as much as the
    employee? Which one is selected in the result table?

    Can you provide an example of how the query would work for a different set of employee data?

    Ask a followup question

    You might also like