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

Commitf204721

Browse files
authored
Added tasks 550, 570
1 parent63f7aee commitf204721

File tree

7 files changed

+244
-0
lines changed

7 files changed

+244
-0
lines changed

‎README.md

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3399,6 +3399,7 @@ implementation 'com.github.javadev:leetcode-in-java:1.21'
33993399
| 0576 |[Out of Boundary Paths](src.save/main/java/g0501_0600/s0576_out_of_boundary_paths/Solution.java)| Medium | Dynamic_Programming | 5 | 92.95
34003400
| 0575 |[Distribute Candies](src.save/main/java/g0501_0600/s0575_distribute_candies/Solution.java)| Easy | Array, Hash_Table | 67 | 42.81
34013401
| 0572 |[Subtree of Another Tree](src.save/main/java/g0501_0600/s0572_subtree_of_another_tree/Solution.java)| Easy | Depth_First_Search, Tree, Binary_Tree, Hash_Function, String_Matching, Algorithm_II_Day_7_Breadth_First_Search_Depth_First_Search | 1 | 100.00
3402+
| 0570 |[Managers with at Least 5 Direct Reports](src.save/main/java/g0501_0600/s0570_managers_with_at_least_5_direct_reports/script.sql)| Medium | Database | 503 | 69.79
34023403
| 0567 |[Permutation in String](src.save/main/java/g0501_0600/s0567_permutation_in_string/Solution.java)| Medium | String, Hash_Table, Two_Pointers, Sliding_Window, Algorithm_I_Day_6_Sliding_Window | 5 | 93.93
34033404
| 0566 |[Reshape the Matrix](src.save/main/java/g0501_0600/s0566_reshape_the_matrix/Solution.java)| Easy | Array, Matrix, Simulation, Data_Structure_I_Day_4_Array, Programming_Skills_I_Day_7_Array | 1 | 90.08
34043405
| 0565 |[Array Nesting](src.save/main/java/g0501_0600/s0565_array_nesting/Solution.java)| Medium | Array, Depth_First_Search | 5 | 95.44
@@ -3414,6 +3415,7 @@ implementation 'com.github.javadev:leetcode-in-java:1.21'
34143415
| 0553 |[Optimal Division](src.save/main/java/g0501_0600/s0553_optimal_division/Solution.java)| Medium | Array, Dynamic_Programming, Math | 0 | 100.00
34153416
| 0552 |[Student Attendance Record II](src.save/main/java/g0501_0600/s0552_student_attendance_record_ii/Solution.java)| Hard | Dynamic_Programming | 11 | 98.55
34163417
| 0551 |[Student Attendance Record I](src.save/main/java/g0501_0600/s0551_student_attendance_record_i/Solution.java)| Easy | String | 0 | 100.00
3418+
| 0550 |[Game Play Analysis IV](src.save/main/java/g0501_0600/s0550_game_play_analysis_iv/script.sql)| Medium | Database | 685 | 100.00
34173419
| 0547 |[Number of Provinces](src.save/main/java/g0501_0600/s0547_number_of_provinces/Solution.java)| Medium | Depth_First_Search, Breadth_First_Search, Graph, Union_Find, Algorithm_II_Day_6_Breadth_First_Search_Depth_First_Search, Graph_Theory_I_Day_8_Standard_Traversal, Level_2_Day_19_Union_Find | 2 | 69.51
34183420
| 0546 |[Remove Boxes](src.save/main/java/g0501_0600/s0546_remove_boxes/Solution.java)| Hard | Array, Dynamic_Programming, Memoization | 45 | 95.58
34193421
| 0543 |[Diameter of Binary Tree](src.save/main/java/g0501_0600/s0543_diameter_of_binary_tree/Solution.java)| Easy | Top_100_Liked_Questions, Depth_First_Search, Tree, Binary_Tree, Level_2_Day_7_Tree, Udemy_Tree_Stack_Queue | 1 | 65.86
Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,52 @@
1+
550\. Game Play Analysis IV
2+
3+
Medium
4+
5+
SQL Schema
6+
7+
Table:`Activity`
8+
9+
+--------------+---------+
10+
| Column Name | Type |
11+
+--------------+---------+
12+
| player_id | int |
13+
| device_id | int |
14+
| event_date | date |
15+
| games_played | int |
16+
+--------------+---------+
17+
(player_id, event_date) is the primary key of this table.
18+
This table shows the activity of players of some games.
19+
Each row is a record of a player who logged in and played a number of games
20+
(possibly 0) before logging out on someday using some device.
21+
22+
Write an SQL query to report the**fraction** of players that logged in again on the day after the day they first logged in,**rounded to 2 decimal places**. In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players.
23+
24+
The query result format is in the following example.
25+
26+
**Example 1:**
27+
28+
**Input:**
29+
30+
Activity table:
31+
32+
+-----------+-----------+------------+--------------+
33+
| player_id | device_id | event_date | games_played |
34+
+-----------+-----------+------------+--------------+
35+
| 1 | 2 | 2016-03-01 | 5 |
36+
| 1 | 2 | 2016-03-02 | 6 |
37+
| 2 | 3 | 2017-06-25 | 1 |
38+
| 3 | 1 | 2016-03-02 | 0 |
39+
| 3 | 4 | 2018-07-03 | 5 |
40+
+-----------+-----------+------------+--------------+
41+
42+
**Output:**
43+
44+
+-----------+
45+
| fraction |
46+
+-----------+
47+
| 0.33 |
48+
+-----------+
49+
50+
**Explanation:**
51+
52+
Only the player with id 1 logged back in after the first day he had logged in so the answer is 1/3 = 0.33
Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,6 @@
1+
# Write your MySQL query statement below
2+
# #Medium #Database #2023_05_16_Time_685_ms_(100.00%)_Space_0B_(100.00%)
3+
SELECT ROUND(COUNT(t2.player_id)/CAST(COUNT(t1.player_id)ASDECIMAL),2)AS fraction
4+
FROM
5+
(SELECT player_id,MIN(event_date)AS first_loginFROM ActivityGROUP BY player_id) t1LEFT JOIN Activity t2
6+
ONt1.player_id=t2.player_idANDt1.first_login=t2.event_date-1
Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,51 @@
1+
570\. Managers with at Least 5 Direct Reports
2+
3+
Medium
4+
5+
SQL Schema
6+
7+
Table:`Employee`
8+
9+
+-------------+---------+
10+
| Column Name | Type |
11+
+-------------+---------+
12+
| id | int |
13+
| name | varchar |
14+
| department | varchar |
15+
| managerId | int |
16+
+-------------+---------+
17+
id is the primary key column for this table.
18+
Each row of this table indicates the name of an employee, their department, and the id of their manager.
19+
If managerId is null, then the employee does not have a manager.
20+
No employee will be the manager of themself.
21+
22+
Write an SQL query to report the managers with at least**five direct reports**.
23+
24+
Return the result table in**any order**.
25+
26+
The query result format is in the following example.
27+
28+
**Example 1:**
29+
30+
**Input:**
31+
32+
Employee table:
33+
34+
+-----+-------+------------+-----------+
35+
| id | name | department | managerId |
36+
+-----+-------+------------+-----------+
37+
| 101 | John | A | None |
38+
| 102 | Dan | A | 101 |
39+
| 103 | James | A | 101 |
40+
| 104 | Amy | A | 101 |
41+
| 105 | Anne | A | 101 |
42+
| 106 | Ron | B | 101 |
43+
+-----+-------+------------+-----------+
44+
45+
**Output:**
46+
47+
+------+
48+
| name |
49+
+------+
50+
| John |
51+
+------+
Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,7 @@
1+
# Write your MySQL query statement below
2+
# #Medium #Database #2023_05_16_Time_503_ms_(69.79%)_Space_0B_(100.00%)
3+
selecte.name
4+
from employee mleft join employee e
5+
onm.managerid=e.id
6+
group bye.name
7+
havingcount(e.name)>4;
Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,61 @@
1+
packageg0501_0600.s0550_game_play_analysis_iv;
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 Activity(player_id INTEGER, device_id INTEGER"
24+
+", event_date DATETIME, games_played INTEGER); "
25+
+"INSERT INTO Activity(player_id, device_id"
26+
+", event_date, games_played)"
27+
+" VALUES (1, 2, '2016-03-01', 5); "
28+
+"INSERT INTO Activity(player_id, device_id"
29+
+", event_date, games_played)"
30+
+" VALUES (1, 2, '2016-03-02', 6); "
31+
+"INSERT INTO Activity(player_id, device_id"
32+
+", event_date, games_played)"
33+
+" VALUES (2, 3, '2017-06-25', 1); "
34+
+"INSERT INTO Activity(player_id, device_id"
35+
+", event_date, games_played)"
36+
+" VALUES (3, 1, '2016-03-02', 0); "
37+
+"INSERT INTO Activity(player_id, device_id"
38+
+", event_date, games_played)"
39+
+" VALUES (3, 4, '2018-07-03', 5); ")
40+
classMysqlTest {
41+
@Test
42+
voidtestScript(@EmbeddedDatabaseDataSourcedataSource)
43+
throwsSQLException,FileNotFoundException {
44+
try (finalConnectionconnection =dataSource.getConnection()) {
45+
try (finalStatementstatement =connection.createStatement();
46+
finalResultSetresultSet =
47+
statement.executeQuery(
48+
newBufferedReader(
49+
newFileReader(
50+
"src/main/java/g0501_0600/"
51+
+"s0550_game_play_analysis_iv/script.sql"))
52+
.lines()
53+
.collect(Collectors.joining("\n"))
54+
.replaceAll("#.*?\\r?\\n",""))) {
55+
assertThat(resultSet.next(),equalTo(true));
56+
assertThat(resultSet.getNString(1),equalTo("0.33"));
57+
assertThat(resultSet.next(),equalTo(false));
58+
}
59+
}
60+
}
61+
}
Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,65 @@
1+
packageg0501_0600.s0570_managers_with_at_least_5_direct_reports;
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 Employee(id INTEGER PRIMARY KEY, name VARCHAR(512)"
24+
+", department VARCHAR(512), managerId INTEGER); "
25+
+"INSERT INTO Employee(id, name"
26+
+", department, managerId)"
27+
+" VALUES (101, 'John', 'A', NULL); "
28+
+"INSERT INTO Employee(id, name"
29+
+", department, managerId)"
30+
+" VALUES (102, 'Dan', 'A', 101); "
31+
+"INSERT INTO Employee(id, name"
32+
+", department, managerId)"
33+
+" VALUES (103, 'James', 'A', 101); "
34+
+"INSERT INTO Employee(id, name"
35+
+", department, managerId)"
36+
+" VALUES (104, 'Amy', 'A', 101); "
37+
+"INSERT INTO Employee(id, name"
38+
+", department, managerId)"
39+
+" VALUES (105, 'Anne', 'A', 101); "
40+
+"INSERT INTO Employee(id, name"
41+
+", department, managerId)"
42+
+" VALUES (106, 'Ron', 'B', 101); ")
43+
classMysqlTest {
44+
@Test
45+
voidtestScript(@EmbeddedDatabaseDataSourcedataSource)
46+
throwsSQLException,FileNotFoundException {
47+
try (finalConnectionconnection =dataSource.getConnection()) {
48+
try (finalStatementstatement =connection.createStatement();
49+
finalResultSetresultSet =
50+
statement.executeQuery(
51+
newBufferedReader(
52+
newFileReader(
53+
"src/main/java/g0501_0600/"
54+
+"s0570_managers_with_at_least_5_direct_reports"
55+
+"/script.sql"))
56+
.lines()
57+
.collect(Collectors.joining("\n"))
58+
.replaceAll("#.*?\\r?\\n",""))) {
59+
assertThat(resultSet.next(),equalTo(true));
60+
assertThat(resultSet.getNString(1),equalTo("John"));
61+
assertThat(resultSet.next(),equalTo(false));
62+
}
63+
}
64+
}
65+
}

0 commit comments

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