2010. The Number of Seniors and Juniors to Join the Company II
Solved
Hard
Topics
SQL Schema
Pandas Schema
Table: Candidates
+-------------+------+
| Column Name | Type |
+-------------+------+
| employee_id | int |
| experience | enum |
| salary | int |
+-------------+------+
employee_id is the column with unique values for this table.
experience is an ENUM (category) of types ('Senior', 'Junior').
Each row of this table indicates the id of a candidate, their monthly salary, and their experience.
The salary of each candidate is guaranteed to be unique.
A company wants to hire new employees. The budget of the company for the salaries is $70000. The company's criteria for hiring are:
Keep hiring the senior with the smallest salary until you cannot hire any more seniors.
Use the remaining budget to hire the junior with the smallest salary.
Keep hiring the junior with the smallest salary until you cannot hire any more juniors.
Write a solution to find the ids of seniors and juniors hired under the mentioned criteria.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input:
Candidates table:
+-------------+------------+--------+
| employee_id | experience | salary |
+-------------+------------+--------+
| 1 | Junior | 10000 |
| 9 | Junior | 15000 |
| 2 | Senior | 20000 |
| 11 | Senior | 16000 |
| 13 | Senior | 50000 |
| 4 | Junior | 40000 |
+-------------+------------+--------+
Output:
+-------------+
| employee_id |
+-------------+
| 11 |
| 2 |
| 1 |
| 9 |
+-------------+
Explanation:
We can hire 2 seniors with IDs (11, 2). Since the budget is $70000 and the sum of their salaries is $36000, we still have $34000 but they are not enough to hire the senior candidate with ID 13.
We can hire 2 juniors with IDs (1, 9). Since the remaining budget is $34000 and the sum of their salaries is $25000, we still have $9000 but they are not enough to hire the junior candidate with ID 4.
Example 2:
Input:
Candidates table:
+-------------+------------+--------+
| employee_id | experience | salary |
+-------------+------------+--------+
| 1 | Junior | 25000 |
| 9 | Junior | 10000 |
| 2 | Senior | 85000 |
| 11 | Senior | 80000 |
| 13 | Senior | 90000 |
| 4 | Junior | 30000 |
+-------------+------------+--------+
Output:
+-------------+
| employee_id |
+-------------+
| 9 |
| 1 |
| 4 |
+-------------+
Explanation:
We cannot hire any seniors with the current budget as we need at least $80000 to hire one senior.
We can hire all three juniors with the remaining budget.
# 1. 计算每个员工在自己等级（Senior/Junior）里的累积薪水
WITH RunningTotals AS (
SELECT
employee_id,
experience,
salary,
SUM(salary) OVER(PARTITION BY experience ORDER BY salary) AS cum_salary
FROM Candidates
),
# 2. 找出能入职的 Senior
SeniorsHired AS (
SELECT employee_id, salary, cum_salary
FROM RunningTotals
WHERE experience = 'Senior' AND cum_salary <= 70000
),
# 3. 算出招完 Senior 后剩多少钱
# 使用 COALESCE 处理没有 Senior 能入职的情况，保证预算还是 70000
BudgetLeft AS (
SELECT 70000 - COALESCE(SUM(salary), 0) AS remaining
FROM SeniorsHired
)
# 4. 找出能入职的 Junior 并与 Senior 的 ID 合并
SELECT employee_id
FROM SeniorsHired
UNION ALL
SELECT employee_id
FROM RunningTotals
WHERE experience = 'Junior'
AND cum_salary <= (SELECT remaining FROM BudgetLeft);
3. SQL 代码实现 (MySQL)
SQL
# 1. 计算每个员工在自己等级（Senior/Junior）里的累积薪水
WITH RunningTotals AS (
SELECT
employee_id,
experience,
salary,
SUM(salary) OVER(PARTITION BY experience ORDER BY salary) AS cum_salary
FROM Candidates
),
# 2. 找出能入职的 Senior
SeniorsHired AS (
SELECT employee_id, salary, cum_salary
FROM RunningTotals
WHERE experience = 'Senior' AND cum_salary <= 70000
),
# 3. 算出招完 Senior 后剩多少钱
# 使用 COALESCE 处理没有 Senior 能入职的情况，保证预算还是 70000
BudgetLeft AS (
SELECT 70000 - COALESCE(SUM(salary), 0) AS remaining
FROM SeniorsHired
)
# 4. 找出能入职的 Junior 并与 Senior 的 ID 合并
SELECT employee_id
FROM SeniorsHired
UNION ALL
SELECT employee_id
FROM RunningTotals
WHERE experience = 'Junior'
AND cum_salary <= (SELECT remaining FROM BudgetLeft);
4. 关键点剖析
SUM(salary) OVER(PARTITION BY experience ORDER BY salary)
LC. 1689. Partitioning Into Minimum Number Of Deci-Binary Numbers
Solved
Medium
Topics
conpanies icon
Companies
Hint
A decimal number is called deci-binary if each of its digits is either 0 or 1 without any leading zeros. For example, 101 and 1100 are deci-binary, while 112 and 3001 are not.
Given a string n that represents a positive decimal integer, return the minimum number of positive deci-binary numbers needed so that they sum up to n.
Example 1:
Input: n = "32"
Output: 3
Explanation: 10 + 11 + 11 = 32
Example 2:
Input: n = "82734"
Output: 8
Example 3:
Input: n = "27346209830709182346"
Output: 9
Constraints:
1 <= n.length <= 105
n consists of only digits.
n does not contain any leading zeros and represents a positive integer.
LC. 1667. Fix Names in a Table
Easy
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Users
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| user_id | int |
| name | varchar |
+----------------+---------+
user_id is the primary key (column with unique values) for this table.
This table contains the ID and the name of the user. The name consists of only lowercase and uppercase characters.
Write a solution to fix the names so that only the first character is uppercase and the rest are lowercase.
Return the result table ordered by user_id.
The result format is in the following example.
Example 1:
Input:
Users table:
+---------+-------+
| user_id | name |
+---------+-------+
| 1 | aLice |
| 2 | bOB |
+---------+-------+
Output:
+---------+-------+
| user_id | name |
+---------+-------+
| 1 | Alice |
| 2 | Bob |
+---------+-------+
楼主: Myron2017
狗仔卡
楼主
