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

Commit8e6cbf5

Browse files
authored
Added tasks 550-2545
1 parent55e675d commit8e6cbf5

File tree

20 files changed

+1297
-212
lines changed

20 files changed

+1297
-212
lines changed

‎README.md

Lines changed: 167 additions & 153 deletions
Large diffs are not rendered by default.

‎src/main/java/g0401_0500/s0466_count_the_repetitions/readme.md

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -62,7 +62,6 @@ public class Solution {
6262
s1Count++;
6363
s2CountMap[s1Count]= s2Count;
6464
}
65-
6665
int n1Left= n1- memo[s2Idx][0];
6766
int matchedPatternCount= n1Left/ (s1Count- memo[s2Idx][0])* (s2Count- memo[s2Idx][1]);
6867
n1Left= n1Left% (s1Count- memo[s2Idx][0]);
Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,65 @@
1+
[![](https://img.shields.io/github/stars/javadev/LeetCode-in-Java?label=Stars&style=flat-square)](https://github.com/javadev/LeetCode-in-Java)
2+
[![](https://img.shields.io/github/forks/javadev/LeetCode-in-Java?label=Fork%20me%20on%20GitHub%20&style=flat-square)](https://github.com/javadev/LeetCode-in-Java/fork)
3+
4+
##550\. Game Play Analysis IV
5+
6+
Medium
7+
8+
SQL Schema
9+
10+
Table:`Activity`
11+
12+
+--------------+---------+
13+
| Column Name | Type |
14+
+--------------+---------+
15+
| player_id | int |
16+
| device_id | int |
17+
| event_date | date |
18+
| games_played | int |
19+
+--------------+---------+
20+
(player_id, event_date) is the primary key of this table.
21+
This table shows the activity of players of some games.
22+
Each row is a record of a player who logged in and played a number of games
23+
(possibly 0) before logging out on someday using some device.
24+
25+
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.
26+
27+
The query result format is in the following example.
28+
29+
**Example 1:**
30+
31+
**Input:**
32+
33+
Activity table:
34+
35+
+-----------+-----------+------------+--------------+
36+
| player_id | device_id | event_date | games_played |
37+
+-----------+-----------+------------+--------------+
38+
| 1 | 2 | 2016-03-01 | 5 |
39+
| 1 | 2 | 2016-03-02 | 6 |
40+
| 2 | 3 | 2017-06-25 | 1 |
41+
| 3 | 1 | 2016-03-02 | 0 |
42+
| 3 | 4 | 2018-07-03 | 5 |
43+
+-----------+-----------+------------+--------------+
44+
45+
**Output:**
46+
47+
+-----------+
48+
| fraction |
49+
+-----------+
50+
| 0.33 |
51+
+-----------+
52+
53+
**Explanation:**
54+
55+
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
56+
57+
##Solution
58+
59+
```sql
60+
# Write your MySQL query statement below
61+
SELECT ROUND(COUNT(t2.player_id)/CAST(COUNT(t1.player_id)ASDECIMAL),2)AS fraction
62+
FROM
63+
(SELECT player_id,MIN(event_date)AS first_loginFROM ActivityGROUP BY player_id) t1LEFT JOIN Activity t2
64+
ONt1.player_id=t2.player_idANDt1.first_login=t2.event_date-1
65+
```
Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,65 @@
1+
[![](https://img.shields.io/github/stars/javadev/LeetCode-in-Java?label=Stars&style=flat-square)](https://github.com/javadev/LeetCode-in-Java)
2+
[![](https://img.shields.io/github/forks/javadev/LeetCode-in-Java?label=Fork%20me%20on%20GitHub%20&style=flat-square)](https://github.com/javadev/LeetCode-in-Java/fork)
3+
4+
##570\. Managers with at Least 5 Direct Reports
5+
6+
Medium
7+
8+
SQL Schema
9+
10+
Table:`Employee`
11+
12+
+-------------+---------+
13+
| Column Name | Type |
14+
+-------------+---------+
15+
| id | int |
16+
| name | varchar |
17+
| department | varchar |
18+
| managerId | int |
19+
+-------------+---------+
20+
id is the primary key column for this table.
21+
Each row of this table indicates the name of an employee, their department, and the id of their manager.
22+
If managerId is null, then the employee does not have a manager.
23+
No employee will be the manager of themself.
24+
25+
Write an SQL query to report the managers with at least**five direct reports**.
26+
27+
Return the result table in**any order**.
28+
29+
The query result format is in the following example.
30+
31+
**Example 1:**
32+
33+
**Input:**
34+
35+
Employee table:
36+
37+
+-----+-------+------------+-----------+
38+
| id | name | department | managerId |
39+
+-----+-------+------------+-----------+
40+
| 101 | John | A | None |
41+
| 102 | Dan | A | 101 |
42+
| 103 | James | A | 101 |
43+
| 104 | Amy | A | 101 |
44+
| 105 | Anne | A | 101 |
45+
| 106 | Ron | B | 101 |
46+
+-----+-------+------------+-----------+
47+
48+
**Output:**
49+
50+
+------+
51+
| name |
52+
+------+
53+
| John |
54+
+------+
55+
56+
##Solution
57+
58+
```sql
59+
# Write your MySQL query statement below
60+
selecte.name
61+
from employee mleft join employee e
62+
onm.managerid=e.id
63+
group bye.name
64+
havingcount(e.name)>4;
65+
```
Lines changed: 88 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,88 @@
1+
[![](https://img.shields.io/github/stars/javadev/LeetCode-in-Java?label=Stars&style=flat-square)](https://github.com/javadev/LeetCode-in-Java)
2+
[![](https://img.shields.io/github/forks/javadev/LeetCode-in-Java?label=Fork%20me%20on%20GitHub%20&style=flat-square)](https://github.com/javadev/LeetCode-in-Java/fork)
3+
4+
##577\. Employee Bonus
5+
6+
Easy
7+
8+
SQL Schema
9+
10+
Table:`Employee`
11+
12+
+-------------+---------+
13+
| Column Name | Type |
14+
+-------------+---------+
15+
| empId | int |
16+
| name | varchar |
17+
| supervisor | int |
18+
| salary | int |
19+
+-------------+---------+
20+
21+
empId is the primary key column for this table.
22+
23+
Each row of this table indicates the name and the ID of an employee in addition to their salary and the id of their manager.
24+
25+
Table:`Bonus`
26+
27+
+-------------+------+
28+
| Column Name | Type |
29+
+-------------+------+
30+
| empId | int |
31+
| bonus | int |
32+
+-------------+------+
33+
34+
empId is the primary key column for this table.
35+
36+
empId is a foreign key to empId from the Employee table.
37+
38+
Each row of this table contains the id of an employee and their respective bonus.
39+
40+
Write an SQL query to report the name and bonus amount of each employee with a bonus**less than**`1000`.
41+
42+
Return the result table in**any order**.
43+
44+
The query result format is in the following example.
45+
46+
**Example 1:**
47+
48+
**Input:**
49+
50+
Employee table:
51+
52+
+-------+--------+------------+--------+
53+
| empId | name | supervisor | salary |
54+
+-------+--------+------------+--------+
55+
| 3 | Brad | null | 4000 |
56+
| 1 | John | 3 | 1000 |
57+
| 2 | Dan | 3 | 2000 |
58+
| 4 | Thomas | 3 | 4000 |
59+
+-------+--------+------------+--------+
60+
61+
Bonus table:
62+
63+
+-------+-------+
64+
| empId | bonus |
65+
+-------+-------+
66+
| 2 | 500 |
67+
| 4 | 2000 |
68+
+-------+-------+
69+
70+
**Output:**
71+
72+
+------+-------+
73+
| name | bonus |
74+
+------+-------+
75+
| Brad | null |
76+
| John | null |
77+
| Dan | 500 |
78+
+------+-------+
79+
80+
##Solution
81+
82+
```sql
83+
# Write your MySQL query statement below
84+
SELECT name, bonus
85+
FROM Employee e
86+
LEFT JOIN Bonus bONe.empId=b.empId
87+
WHERE bonus<1000orb.empId isnull;
88+
```
Lines changed: 81 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,81 @@
1+
[![](https://img.shields.io/github/stars/javadev/LeetCode-in-Java?label=Stars&style=flat-square)](https://github.com/javadev/LeetCode-in-Java)
2+
[![](https://img.shields.io/github/forks/javadev/LeetCode-in-Java?label=Fork%20me%20on%20GitHub%20&style=flat-square)](https://github.com/javadev/LeetCode-in-Java/fork)
3+
4+
##585\. Investments in 2016
5+
6+
Medium
7+
8+
SQL Schema
9+
10+
Table:`Insurance`
11+
12+
+-------------+-------+
13+
| Column Name | Type |
14+
+-------------+-------+
15+
| pid | int |
16+
| tiv_2015 | float |
17+
| tiv_2016 | float |
18+
| lat | float |
19+
| lon | float |
20+
+-------------+-------+
21+
22+
pid is the primary key column for this table.
23+
24+
Each row of this table contains information about one policy where:
25+
26+
pid is the policyholder's policy ID.
27+
28+
tiv\_2015 is the total investment value in 2015 and tiv\_2016 is the total investment value in 2016.
29+
30+
lat is the latitude of the policy holder's city. It's guaranteed that lat is not NULL.
31+
32+
lon is the longitude of the policy holder's city. It's guaranteed that lon is not NULL.
33+
34+
Write an SQL query to report the sum of all total investment values in 2016`tiv_2016`, for all policyholders who:
35+
36+
* have the same`tiv_2015` value as one or more other policyholders, and
37+
* are not located in the same city like any other policyholder (i.e., the (`lat, lon`) attribute pairs must be unique).
38+
39+
Round`tiv_2016` to**two decimal places**.
40+
41+
The query result format is in the following example.
42+
43+
**Example 1:**
44+
45+
**Input:** Insurance table:
46+
47+
+-----+----------+----------+-----+-----+
48+
| pid | tiv_2015 | tiv_2016 | lat | lon |
49+
+-----+----------+----------+-----+-----+
50+
| 1 | 10 | 5 | 10 | 10 |
51+
| 2 | 20 | 20 | 20 | 20 |
52+
| 3 | 10 | 30 | 20 | 20 |
53+
| 4 | 10 | 40 | 40 | 40 |
54+
+-----+----------+----------+-----+-----+
55+
56+
**Output:**
57+
58+
+----------+
59+
| tiv_2016 |
60+
+----------+
61+
| 45.00 |
62+
+----------+
63+
64+
**Explanation:**
65+
66+
The first record in the table, like the last record, meets both of the two criteria.
67+
The tiv\_2015 value 10 is the same as the third and fourth records, and its location is unique.
68+
69+
The second record does not meet any of the two criteria. Its tiv\_2015 is not like any other policyholders and its location is the same as the third record, which makes the third record fail, too.
70+
So, the result is the sum of tiv\_2016 of the first and last record, which is 45.
71+
72+
##Solution
73+
74+
```sql
75+
# Write your MySQL query statement below
76+
select round(sum(tiv_2016),2) tiv_2016from insurance i1
77+
where tiv_2015in (select tiv_2015from insurance i2
78+
wherei1.pid!=i2.pid)
79+
and (lat, lon) notin (select lat, lonfrom insurance i3
80+
wherei3.pid!=i1.pid)
81+
```
Lines changed: 67 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,67 @@
1+
[![](https://img.shields.io/github/stars/javadev/LeetCode-in-Java?label=Stars&style=flat-square)](https://github.com/javadev/LeetCode-in-Java)
2+
[![](https://img.shields.io/github/forks/javadev/LeetCode-in-Java?label=Fork%20me%20on%20GitHub%20&style=flat-square)](https://github.com/javadev/LeetCode-in-Java/fork)
3+
4+
##602\. Friend Requests II: Who Has the Most Friends
5+
6+
Medium
7+
8+
SQL Schema
9+
10+
Table:`RequestAccepted`
11+
12+
+----------------+---------+
13+
| Column Name | Type |
14+
+----------------+---------+
15+
| requester_id | int |
16+
| accepter_id | int |
17+
| accept_date | date |
18+
+----------------+---------+
19+
20+
(requester_id, accepter_id) is the primary key for this table. This table contains the ID of the user who sent the request, the ID of the user who received the request, and the date when the request was accepted.
21+
22+
Write an SQL query to find the people who have the most friends and the most friends number.
23+
24+
The test cases are generated so that only one person has the most friends.
25+
26+
The query result format is in the following example.
27+
28+
**Example 1:**
29+
30+
**Input:** RequestAccepted table:
31+
32+
+--------------+-------------+-------------+
33+
| requester_id | accepter_id | accept_date |
34+
+--------------+-------------+-------------+
35+
| 1 | 2 | 2016/06/03 |
36+
| 1 | 3 | 2016/06/08 |
37+
| 2 | 3 | 2016/06/08 |
38+
| 3 | 4 | 2016/06/09 |
39+
+--------------+-------------+-------------+
40+
41+
**Output:**
42+
43+
+----+-----+
44+
| id | num |
45+
+----+-----+
46+
| 3 | 3 |
47+
+----+-----+
48+
49+
**Explanation:** The person with id 3 is a friend of people 1, 2, and 4, so he has three friends in total, which is the most number than any others.
50+
51+
**Follow up:** In the real world, multiple people could have the same most number of friends. Could you find all these people in this case?
52+
53+
##Solution
54+
55+
```sql
56+
# Write your MySQL query statement below
57+
SELECT reqAS id,COUNT(acc)AS num
58+
FROM
59+
((SELECT requester_idAS req, accepter_idAS acc
60+
FROM requestaccepted)
61+
UNION
62+
(SELECT accepter_idAS req, requester_idAS acc
63+
FROM requestaccepted)) t
64+
GROUP BY req
65+
ORDER BY numDESC
66+
LIMIT1
67+
```

0 commit comments

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