00:00:00

Description

Editorial

Employee Low Bonus Audit

EASY10 pts

Your HR analytics system stores two tables:

Employee(empId, name, supervisor, salary)
Bonus(empId, bonus)

Each employee appears exactly once in Employee. Bonus contains optional incentive data for some employees.

Build a report that returns employees who meet either condition:

Their bonus is strictly less than 1000.
They do not have any bonus record.

Return exactly these columns:

name
bonus

Notes:

If an employee has no bonus row, bonus should appear as null in output.
Output row order is not semantically important.

Solve this in both:

SQL (SQLite 3.27.2)
Pandas

Example

Input

Employee:
| empId | name   | supervisor | salary |
|-------|--------|------------|--------|
| 3     | Brad   | null       | 4000   |
| 1     | John   | 3          | 1000   |
| 2     | Dan    | 3          | 2000   |
| 4     | Thomas | 3          | 4000   |

Bonus:
| empId | bonus |
|-------|-------|
| 2     | 500   |
| 4     | 2000  |

Output

[
  {"name":"Brad","bonus":null},
  {"name":"Dan","bonus":500},
  {"name":"John","bonus":null}
]

Explanation

Brad and John have no bonus rows, so they are included with null bonuses. Dan has bonus 500 (<1000), so he is included. Thomas has 2000 and is excluded.

Example

Input

Employee:
| empId | name  | supervisor | salary |
|-------|-------|------------|--------|
| 7     | Asha  | null       | 5000   |
| 8     | Omar  | 7          | 3100   |
| 9     | Lina  | 7          | 2900   |

Bonus:
| empId | bonus |
|-------|-------|
| 7     | 999   |
| 8     | 1000  |
| 9     | 0     |

Output

[
  {"name":"Asha","bonus":999},
  {"name":"Lina","bonus":0}
]

Explanation

The threshold is strict. Bonus 999 and 0 qualify, while bonus 1000 does not.

Example

Input

Employee:
| empId | name  | supervisor | salary |
|-------|-------|------------|--------|
| 10    | Inez  | null       | 6000   |
| 11    | Ravi  | 10         | 3500   |

Bonus:
| empId | bonus |
|-------|-------|
| 10    | 1800  |
| 11    | 1400  |

Output

[]

Explanation

Both employees have bonus values at or above 1000, so no rows satisfy the condition.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Employee <= 50,000
0 <= number of rows in Bonus <= 50,000
Employee.empId is unique
Bonus.empId is unique
1 <= salary <= 1,000,000
0 <= bonus <= 1,000,000 when present
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: name, bonus

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

bonus =

[{"empId":2,"bonus":500},{"empId":4,"bonus":2000}]

employee =

[{"empId":3,"name":"Brad","supervisor":null,"salary":4000},{"empId":1,"name":"John","supervisor":3,"salary":1000},{"empId":2,"name":"Dan","supervisor":3,"salary":2000},{"empId":4,"name":"Thomas","supervisor":3,"salary":4000}]

Loading problem...

101

00:00:00

Description

Editorial

Employee Low Bonus Audit

EASY10 pts

Your HR analytics system stores two tables:

Employee(empId, name, supervisor, salary)
Bonus(empId, bonus)

Each employee appears exactly once in Employee. Bonus contains optional incentive data for some employees.

Build a report that returns employees who meet either condition:

Their bonus is strictly less than 1000.
They do not have any bonus record.

Return exactly these columns:

name
bonus

Notes:

If an employee has no bonus row, bonus should appear as null in output.
Output row order is not semantically important.

Solve this in both:

SQL (SQLite 3.27.2)
Pandas

Example

Input

Employee:
| empId | name   | supervisor | salary |
|-------|--------|------------|--------|
| 3     | Brad   | null       | 4000   |
| 1     | John   | 3          | 1000   |
| 2     | Dan    | 3          | 2000   |
| 4     | Thomas | 3          | 4000   |

Bonus:
| empId | bonus |
|-------|-------|
| 2     | 500   |
| 4     | 2000  |

Output

[
  {"name":"Brad","bonus":null},
  {"name":"Dan","bonus":500},
  {"name":"John","bonus":null}
]

Explanation

Brad and John have no bonus rows, so they are included with null bonuses. Dan has bonus 500 (<1000), so he is included. Thomas has 2000 and is excluded.

Example

Input

Employee:
| empId | name  | supervisor | salary |
|-------|-------|------------|--------|
| 7     | Asha  | null       | 5000   |
| 8     | Omar  | 7          | 3100   |
| 9     | Lina  | 7          | 2900   |

Bonus:
| empId | bonus |
|-------|-------|
| 7     | 999   |
| 8     | 1000  |
| 9     | 0     |

Output

[
  {"name":"Asha","bonus":999},
  {"name":"Lina","bonus":0}
]

Explanation

The threshold is strict. Bonus 999 and 0 qualify, while bonus 1000 does not.

Example

Input

Employee:
| empId | name  | supervisor | salary |
|-------|-------|------------|--------|
| 10    | Inez  | null       | 6000   |
| 11    | Ravi  | 10         | 3500   |

Bonus:
| empId | bonus |
|-------|-------|
| 10    | 1800  |
| 11    | 1400  |

Output

[]

Explanation

Both employees have bonus values at or above 1000, so no rows satisfy the condition.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Employee <= 50,000
0 <= number of rows in Bonus <= 50,000
Employee.empId is unique
Bonus.empId is unique
1 <= salary <= 1,000,000
0 <= bonus <= 1,000,000 when present
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: name, bonus

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

bonus =

[{"empId":2,"bonus":500},{"empId":4,"bonus":2000}]

employee =

Employee Low Bonus Audit

Hints

Employee Low Bonus Audit

Hints