Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitadea95b

Browse files
authored
Added task 3482
1 parentc0158b7 commitadea95b

File tree

3 files changed

+228
-0
lines changed

3 files changed

+228
-0
lines changed
Lines changed: 96 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,96 @@
1+
3482\. Analyze Organization Hierarchy
2+
3+
Hard
4+
5+
Table:`Employees`
6+
7+
+----------------+---------+
8+
| Column Name | Type |
9+
+----------------+---------+
10+
| employee_id | int |
11+
| employee_name | varchar |
12+
| manager_id | int |
13+
| salary | int |
14+
| department | varchar |
15+
+----------------+---------+
16+
employee_id is the unique key for this table.
17+
Each row contains information about an employee, including their ID, name, their manager's ID, salary, and department.
18+
manager_id is null for the top-level manager (CEO).
19+
20+
Write a solution to analyze the organizational hierarchy and answer the following:
21+
22+
1.**Hierarchy Levels:** For each employee, determine their level in the organization (CEO is level`1`, employees reporting directly to the CEO are level`2`, and so on).
23+
2.**Team Size:** For each employee who is a manager, count the total number of employees under them (direct and indirect reports).
24+
3.**Salary Budget:** For each manager, calculate the total salary budget they control (sum of salaries of all employees under them, including indirect reports, plus their own salary).
25+
26+
Return_the result table ordered by_the result ordered by**level** in**ascending** order, then by**budget** in**descending** order, and finally by**employee\_name** in**ascending** order_._
27+
28+
_The result format is in the following example._
29+
30+
**Example:**
31+
32+
**Input:**
33+
34+
Employees table:
35+
36+
+-------------+---------------+------------+--------+-------------+
37+
| employee_id | employee_name | manager_id | salary | department |
38+
+-------------+---------------+------------+--------+-------------+
39+
| 1 | Alice | null | 12000 | Executive |
40+
| 2 | Bob | 1 | 10000 | Sales |
41+
| 3 | Charlie | 1 | 10000 | Engineering |
42+
| 4 | David | 2 | 7500 | Sales |
43+
| 5 | Eva | 2 | 7500 | Sales |
44+
| 6 | Frank | 3 | 9000 | Engineering |
45+
| 7 | Grace | 3 | 8500 | Engineering |
46+
| 8 | Hank | 4 | 6000 | Sales |
47+
| 9 | Ivy | 6 | 7000 | Engineering |
48+
| 10 | Judy | 6 | 7000 | Engineering |
49+
+-------------+---------------+------------+--------+-------------+
50+
51+
**Output:**
52+
53+
+-------------+---------------+-------+-----------+--------+
54+
| employee_id | employee_name | level | team_size | budget |
55+
+-------------+---------------+-------+-----------+--------+
56+
| 1 | Alice | 1 | 9 | 84500 |
57+
| 3 | Charlie | 2 | 4 | 41500 |
58+
| 2 | Bob | 2 | 3 | 31000 |
59+
| 6 | Frank | 3 | 2 | 23000 |
60+
| 4 | David | 3 | 1 | 13500 |
61+
| 7 | Grace | 3 | 0 | 8500 |
62+
| 5 | Eva | 3 | 0 | 7500 |
63+
| 9 | Ivy | 4 | 0 | 7000 |
64+
| 10 | Judy | 4 | 0 | 7000 |
65+
| 8 | Hank | 4 | 0 | 6000 |
66+
+-------------+---------------+-------+-----------+--------+
67+
68+
**Explanation:**
69+
70+
***Organization Structure:**
71+
* Alice (ID: 1) is the CEO (level 1) with no manager
72+
* Bob (ID: 2) and Charlie (ID: 3) report directly to Alice (level 2)
73+
* David (ID: 4), Eva (ID: 5) report to Bob, while Frank (ID: 6) and Grace (ID: 7) report to Charlie (level 3)
74+
* Hank (ID: 8) reports to David, and Ivy (ID: 9) and Judy (ID: 10) report to Frank (level 4)
75+
***Level Calculation:**
76+
* The CEO (Alice) is at level 1
77+
* Each subsequent level of management adds 1 to the level
78+
***Team Size Calculation:**
79+
* Alice has 9 employees under her (the entire company except herself)
80+
* Bob has 3 employees (David, Eva, and Hank)
81+
* Charlie has 4 employees (Frank, Grace, Ivy, and Judy)
82+
* David has 1 employee (Hank)
83+
* Frank has 2 employees (Ivy and Judy)
84+
* Eva, Grace, Hank, Ivy, and Judy have no direct reports (team\_size = 0)
85+
***Budget Calculation:**
86+
* Alice's budget: Her salary (12000) + all employees' salaries (72500) = 84500
87+
* Charlie's budget: His salary (10000) + Frank's budget (23000) + Grace's salary (8500) = 41500
88+
* Bob's budget: His salary (10000) + David's budget (13500) + Eva's salary (7500) = 31000
89+
* Frank's budget: His salary (9000) + Ivy's salary (7000) + Judy's salary (7000) = 23000
90+
* David's budget: His salary (7500) + Hank's salary (6000) = 13500
91+
* Employees with no direct reports have budgets equal to their own salary
92+
93+
**Note:**
94+
95+
* The result is ordered first by level in ascending order
96+
* Within the same level, employees are ordered by budget in descending order then by name in ascending order
Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,43 @@
1+
# Write your MySQL query statement below
2+
# #Hard #2025_03_11_Time_712_ms_(100.00%)_Space_0.0_MB_(100.00%)
3+
with recursive org_hierarchy(orig_employee_id, orig_employee_name, employee_id, employee_name, manager_id, salary, org_level)as
4+
(
5+
select employee_idas orig_employee_id,
6+
employee_nameas orig_employee_name,
7+
employee_id,
8+
employee_name,
9+
manager_id,
10+
salary,
11+
1as org_level
12+
from Employees
13+
UNION ALL
14+
selectP.orig_employee_id,
15+
P.orig_employee_name,
16+
CH.employee_id,
17+
CH.employee_name,
18+
CH.manager_id,
19+
CH.salary,
20+
P.org_level+1
21+
from org_hierarchy P, Employees CH
22+
wherech.manager_id=P.employee_id
23+
),
24+
CEO_hierarchyas (
25+
selectorg_hierarchy.employee_idas SUB_employee_id,
26+
org_hierarchy.employee_name,
27+
org_hierarchy.org_levelas sub_level
28+
from org_hierarchy, Employees
29+
whereorg_hierarchy.orig_employee_id=Employees.employee_id
30+
andEmployees.manager_id isnull
31+
)
32+
select
33+
org_hierarchy.ORIG_EMPLOYEE_IDas employee_id,
34+
org_hierarchy.ORIG_EMPLOYEE_nameas employee_name,
35+
CEO_hierarchy.sub_levelas"level",
36+
count(*)-1as team_size,
37+
sum(org_hierarchy.salary)as budget
38+
from org_hierarchy, CEO_hierarchy
39+
whereorg_hierarchy.ORIG_EMPLOYEE_ID=CEO_hierarchy.SUB_employee_id
40+
group byorg_hierarchy.ORIG_EMPLOYEE_ID,
41+
org_hierarchy.ORIG_EMPLOYEE_name,
42+
CEO_hierarchy.sub_level
43+
order by3asc,5desc,2
Lines changed: 89 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,89 @@
1+
packageg3401_3500.s3482_analyze_organization_hierarchy;
2+
3+
importstaticorg.hamcrest.CoreMatchers.equalTo;
4+
importstaticorg.hamcrest.MatcherAssert.assertThat;
5+
6+
importjava.io.BufferedReader;
7+
importjava.io.FileNotFoundException;
8+
importjava.io.FileReader;
9+
importjava.sql.Connection;
10+
importjava.sql.ResultSet;
11+
importjava.sql.SQLException;
12+
importjava.sql.Statement;
13+
importjava.util.stream.Collectors;
14+
importjavax.sql.DataSource;
15+
importorg.junit.jupiter.api.Test;
16+
importorg.zapodot.junit.db.annotations.EmbeddedDatabase;
17+
importorg.zapodot.junit.db.annotations.EmbeddedDatabaseTest;
18+
importorg.zapodot.junit.db.common.CompatibilityMode;
19+
20+
@EmbeddedDatabaseTest(
21+
compatibilityMode =CompatibilityMode.MySQL,
22+
initialSqls =
23+
" CREATE TABLE Employees ("
24+
+" employee_id INT,"
25+
+" employee_name VARCHAR(50),"
26+
+" manager_id INT,"
27+
+" salary INT,"
28+
+" department VARCHAR(100)"
29+
+");"
30+
+"insert into Employees (employee_id, employee_name, manager_id, salary, department) values "
31+
+"(1, 'Alice', NULL, 12000, 'Executive');"
32+
+"insert into Employees (employee_id, employee_name, manager_id, salary, department) values "
33+
+"(2, 'Bob', 1, 10000, 'Sales');"
34+
+"insert into Employees (employee_id, employee_name, manager_id, salary, department) values "
35+
+"(3, 'Charlie', 1, 10000, 'Engineering');"
36+
+"insert into Employees (employee_id, employee_name, manager_id, salary, department) values "
37+
+"(4, 'David', 2, 7500, 'Sales');"
38+
+"insert into Employees (employee_id, employee_name, manager_id, salary, department) values "
39+
+"(5, 'Eva', 2, 7500, 'Sales');"
40+
+"insert into Employees (employee_id, employee_name, manager_id, salary, department) values "
41+
+"(6, 'Frank', 3, 9000, 'Engineering');"
42+
+"insert into Employees (employee_id, employee_name, manager_id, salary, department) values "
43+
+"(7, 'Grace', 3, 8500, 'Engineering');"
44+
+"insert into Employees (employee_id, employee_name, manager_id, salary, department) values "
45+
+"(8, 'Hank', 4, 6000, 'Sales');"
46+
+"insert into Employees (employee_id, employee_name, manager_id, salary, department) values "
47+
+"(9, 'Ivy', 6, 7000, 'Engineering');"
48+
+"insert into Employees (employee_id, employee_name, manager_id, salary, department) values "
49+
+"(10, 'Judy', 6, 7000, 'Engineering');")
50+
classMysqlTest {
51+
@Test
52+
voidtestScript(@EmbeddedDatabaseDataSourcedataSource)
53+
throwsSQLException,FileNotFoundException {
54+
try (finalConnectionconnection =dataSource.getConnection()) {
55+
try (finalStatementstatement =connection.createStatement();
56+
finalResultSetresultSet =
57+
statement.executeQuery(
58+
newBufferedReader(
59+
newFileReader(
60+
"src/main/java/g3401_3500/"
61+
+"s3482_analyze_organization_hierarchy/"
62+
+"script.sql"))
63+
.lines()
64+
.collect(Collectors.joining("\n"))
65+
.replaceAll("#.*?\\r?\\n",""))) {
66+
checkRow(resultSet,newString[] {"1","Alice","1","9","84500"});
67+
checkRow(resultSet,newString[] {"3","Charlie","2","4","41500"});
68+
checkRow(resultSet,newString[] {"2","Bob","2","3","31000"});
69+
checkRow(resultSet,newString[] {"6","Frank","3","2","23000"});
70+
checkRow(resultSet,newString[] {"4","David","3","1","13500"});
71+
checkRow(resultSet,newString[] {"7","Grace","3","0","8500"});
72+
checkRow(resultSet,newString[] {"5","Eva","3","0","7500"});
73+
checkRow(resultSet,newString[] {"9","Ivy","4","0","7000"});
74+
checkRow(resultSet,newString[] {"10","Judy","4","0","7000"});
75+
checkRow(resultSet,newString[] {"8","Hank","4","0","6000"});
76+
assertThat(resultSet.next(),equalTo(false));
77+
}
78+
}
79+
}
80+
81+
privatestaticvoidcheckRow(ResultSetresultSet,String[]values)throwsSQLException {
82+
assertThat(resultSet.next(),equalTo(true));
83+
assertThat(resultSet.getNString(1),equalTo(values[0]));
84+
assertThat(resultSet.getNString(2),equalTo(values[1]));
85+
assertThat(resultSet.getNString(3),equalTo(values[2]));
86+
assertThat(resultSet.getNString(4),equalTo(values[3]));
87+
assertThat(resultSet.getNString(5),equalTo(values[4]));
88+
}
89+
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp