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

Commit2e464e9

Browse files
authored
Added tasks 1934, 1978, 2356, 2552, 2553
1 parent7fa1046 commit2e464e9

File tree

15 files changed

+610
-0
lines changed

15 files changed

+610
-0
lines changed
Lines changed: 86 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,86 @@
1+
1934\. Confirmation Rate
2+
3+
Medium
4+
5+
SQL Schema
6+
7+
Table:`Signups`
8+
9+
+----------------+----------+
10+
| Column Name | Type |
11+
+----------------+----------+
12+
| user_id | int |
13+
| time_stamp | datetime |
14+
+----------------+----------+
15+
user_id is the primary key for this table.
16+
Each row contains information about the signup time for the user with ID user_id.
17+
18+
Table:`Confirmations`
19+
20+
+----------------+----------+
21+
| Column Name | Type |
22+
+----------------+----------+
23+
| user_id | int |
24+
| time_stamp | datetime |
25+
| action | ENUM |
26+
+----------------+----------+
27+
(user_id, time_stamp) is the primary key for this table.
28+
user_id is a foreign key with a reference to the Signups table.
29+
action is an ENUM of the type ('confirmed', 'timeout')
30+
Each row of this table indicates that the user with ID user_id requested a confirmation message at time_stamp and that confirmation message was either confirmed ('confirmed') or expired without confirming ('timeout').
31+
32+
The**confirmation rate** of a user is the number of`'confirmed'` messages divided by the total number of requested confirmation messages. The confirmation rate of a user that did not request any confirmation messages is`0`. Round the confirmation rate to**two decimal** places.
33+
34+
Write an SQL query to find the**confirmation rate** of each user.
35+
36+
Return the result table in**any order**.
37+
38+
The query result format is in the following example.
39+
40+
**Example 1:**
41+
42+
**Input:**
43+
44+
Signups table:
45+
+---------+---------------------+
46+
| user_id | time_stamp |
47+
+---------+---------------------+
48+
| 3 | 2020-03-21 10:16:13 |
49+
| 7 | 2020-01-04 13:57:59 |
50+
| 2 | 2020-07-29 23:09:44 |
51+
| 6 | 2020-12-09 10:39:37 |
52+
+---------+---------------------+
53+
54+
Confirmations table:
55+
+---------+---------------------+-----------+
56+
| user_id | time_stamp | action |
57+
+---------+---------------------+-----------+
58+
| 3 | 2021-01-06 03:30:46 | timeout |
59+
| 3 | 2021-07-14 14:00:00 | timeout |
60+
| 7 | 2021-06-12 11:57:29 | confirmed |
61+
| 7 | 2021-06-13 12:58:28 | confirmed |
62+
| 7 | 2021-06-14 13:59:27 | confirmed |
63+
| 2 | 2021-01-22 00:00:00 | confirmed |
64+
| 2 | 2021-02-28 23:59:59 | timeout |
65+
+---------+---------------------+-----------+
66+
67+
**Output:**
68+
69+
+---------+-------------------+
70+
| user_id | confirmation_rate |
71+
+---------+-------------------+
72+
| 6 | 0.00 |
73+
| 3 | 0.00 |
74+
| 7 | 1.00 |
75+
| 2 | 0.50 |
76+
+---------+-------------------+
77+
78+
**Explanation:**
79+
80+
User 6 did not request any confirmation messages. The confirmation rate is 0.
81+
82+
User 3 made 2 requests and both timed out. The confirmation rate is 0.
83+
84+
User 7 made 3 requests and all were confirmed. The confirmation rate is 1.
85+
86+
User 2 made 2 requests where one was confirmed and the other timed out. The confirmation rate is 1 / 2 = 0.5.
Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
# Write your MySQL query statement below
2+
# #Medium #Database #2023_08_17_Time_1432_ms_(68.14%)_Space_0B_(100.00%)
3+
WITH base_tableAS (
4+
SELECT
5+
t.user_id,
6+
ROUND(t.total_confirmed/t.total_messages,2)AS confirmation_rate
7+
FROM (
8+
SELECT
9+
user_id ,
10+
SUM(CASE WHEN action='confirmed' THEN1 ELSE0 END)AS total_confirmed,
11+
COUNT(*)AS total_messages
12+
FROM confirmations
13+
GROUP BY1
14+
) t
15+
)
16+
SELECT
17+
s.user_id,
18+
COALESCE(bt.confirmation_rate,0)AS confirmation_rate
19+
FROM signups s
20+
LEFT JOIN base_table bt
21+
ONs.user_id=bt.user_id
Lines changed: 55 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,55 @@
1+
1978\. Employees Whose Manager Left the Company
2+
3+
Easy
4+
5+
SQL Schema
6+
7+
Table:`Employees`
8+
9+
+-------------+----------+
10+
| Column Name | Type |
11+
+-------------+----------+
12+
| employee_id | int |
13+
| name | varchar |
14+
| manager_id | int |
15+
| salary | int |
16+
+-------------+----------+
17+
18+
employee_id is the primary key for this table. This table contains information about the employees, their salary, and the ID of their manager. Some employees do not have a manager (manager_id is null).
19+
20+
Write an SQL query to report the IDs of the employees whose salary is strictly less than`$30000` and whose manager left the company. When a manager leaves the company, their information is deleted from the`Employees` table, but the reports still have their`manager_id` set to the manager that left.
21+
22+
Return the result table ordered by`employee_id`.
23+
24+
The query result format is in the following example.
25+
26+
**Example 1:**
27+
28+
**Input:** Employees table:
29+
30+
+-------------+-----------+------------+--------+
31+
| employee_id | name | manager_id | salary |
32+
+-------------+-----------+------------+--------+
33+
| 3 | Mila | 9 | 60301 |
34+
| 12 | Antonella | null | 31000 |
35+
| 13 | Emery | null | 67084 |
36+
| 1 | Kalel | 11 | 21241 |
37+
| 9 | Mikaela | null | 50937 |
38+
| 11 | Joziah | 6 | 28485 |
39+
+-------------+-----------+------------+--------+
40+
41+
**Output:**
42+
43+
+-------------+
44+
| employee_id |
45+
+-------------+
46+
| 11 |
47+
+-------------+
48+
49+
**Explanation:**
50+
51+
The employees with a salary less than $30000 are 1 (Kalel) and 11 (Joziah).
52+
53+
Kalel's manager is employee 11, who is still in the company (Joziah).
54+
55+
Joziah's manager is employee 6, who left the company because there is no row for employee 6 as it was deleted.
Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
# Write your MySQL query statement below
2+
# #Easy #Database #2023_08_17_Time_719_ms_(64.69%)_Space_0B_(100.00%)
3+
select employee_idfrom employeeswhere manager_id notin (select distinct (employee_id)
4+
from employees)AND salary<30000order by employee_id
Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,68 @@
1+
2356\. Number of Unique Subjects Taught by Each Teacher
2+
3+
Easy
4+
5+
SQL Schema
6+
7+
Table:`Teacher`
8+
9+
+-------------+------+
10+
| Column Name | Type |
11+
+-------------+------+
12+
| teacher_id | int |
13+
| subject_id | int |
14+
| dept_id | int |
15+
+-------------+------+
16+
(subject_id, dept_id) is the primary key for this table.
17+
Each row in this table indicates that the teacher with teacher_id teaches the subject subject_id in the department dept_id.
18+
19+
Write an SQL query to report the number of unique subjects each teacher teaches in the university.
20+
21+
Return the result table in**any order**.
22+
23+
The query result format is shown in the following example.
24+
25+
**Example 1:**
26+
27+
**Input:**
28+
29+
Teacher table:
30+
31+
+------------+------------+---------+
32+
| teacher_id | subject_id | dept_id |
33+
+------------+------------+---------+
34+
| 1 | 2 | 3 |
35+
| 1 | 2 | 4 |
36+
| 1 | 3 | 3 |
37+
| 2 | 1 | 1 |
38+
| 2 | 2 | 1 |
39+
| 2 | 3 | 1 |
40+
| 2 | 4 | 1 |
41+
+------------+------------+---------+
42+
43+
**Output:**
44+
45+
+------------+-----+
46+
| teacher_id | cnt |
47+
+------------+-----+
48+
| 1 | 2 |
49+
| 2 | 4 |
50+
+------------+-----+
51+
52+
**Explanation:**
53+
54+
Teacher 1:
55+
56+
- They teach subject 2 in departments 3 and 4.
57+
58+
- They teach subject 3 in department 3.
59+
60+
Teacher 2:
61+
62+
- They teach subject 1 in department 1.
63+
64+
- They teach subject 2 in department 1.
65+
66+
- They teach subject 3 in department 1.
67+
68+
- They teach subject 4 in department 1.
Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
# Write your MySQL query statement below
2+
# #Easy #Database #2023_08_17_Time_964_ms_(79.65%)_Space_0B_(100.00%)
3+
select teacher_id,count(distinct subject_id)as"cnt"
4+
from Teacher
5+
group by teacher_id
Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,27 @@
1+
packageg2501_2600.s2552_count_increasing_quadruplets;
2+
3+
// #Hard #Array #Dynamic_Programming #Prefix_Sum #Enumeration #Binary_Indexed_Tree
4+
// #2023_08_18_Time_48_ms_(97.29%)_Space_43_MB_(93.41%)
5+
6+
importjava.util.Arrays;
7+
8+
classSolution {
9+
publiclongcountQuadruplets(int[]nums) {
10+
intn =nums.length;
11+
long[]dp =newlong[n];
12+
Arrays.fill(dp,0);
13+
longret =0;
14+
for (inti =1;i <n;i++) {
15+
intchoice =0;
16+
for (intj =0;j <i;j++) {
17+
if (nums[i] >nums[j]) {
18+
choice++;
19+
ret +=dp[j];
20+
}elseif (nums[i] <nums[j]) {
21+
dp[j] +=choice;
22+
}
23+
}
24+
}
25+
returnret;
26+
}
27+
}
Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,38 @@
1+
2552\. Count Increasing Quadruplets
2+
3+
Hard
4+
5+
Given a**0-indexed** integer array`nums` of size`n` containing all numbers from`1` to`n`, return_the number of increasing quadruplets_.
6+
7+
A quadruplet`(i, j, k, l)` is increasing if:
8+
9+
*`0 <= i < j < k < l < n`, and
10+
*`nums[i] < nums[k] < nums[j] < nums[l]`.
11+
12+
**Example 1:**
13+
14+
**Input:** nums =[1,3,2,4,5]
15+
16+
**Output:** 2
17+
18+
**Explanation:**
19+
20+
- When i = 0, j = 1, k = 2, and l = 3, nums[i] < nums[k] < nums[j] < nums[l].
21+
22+
- When i = 0, j = 1, k = 2, and l = 4, nums[i] < nums[k] < nums[j] < nums[l]. There are no other quadruplets, so we return 2.
23+
24+
**Example 2:**
25+
26+
**Input:** nums =[1,2,3,4]
27+
28+
**Output:** 0
29+
30+
**Explanation:**
31+
32+
There exists only one quadruplet with i = 0, j = 1, k = 2, l = 3, but since nums[j] < nums[k], we return 0.
33+
34+
**Constraints:**
35+
36+
*`4 <= nums.length <= 4000`
37+
*`1 <= nums[i] <= nums.length`
38+
* All the integers of`nums` are**unique**.`nums` is a permutation.
Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
packageg2501_2600.s2553_separate_the_digits_in_an_array;
2+
3+
// #Easy #Array #Simulation #2023_08_18_Time_3_ms_(92.00%)_Space_43.7_MB_(93.70%)
4+
5+
classSolution {
6+
publicint[]separateDigits(int[]nums) {
7+
StringBuilderstr =newStringBuilder();
8+
for (intnum :nums) {
9+
str.append(num);
10+
}
11+
int[]ar =newint[str.length()];
12+
Strings =str.toString();
13+
for (inti =0;i <s.length();i++) {
14+
ar[i] =s.charAt(i) -'0';
15+
}
16+
returnar;
17+
}
18+
}
Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,42 @@
1+
2553\. Separate the Digits in an Array
2+
3+
Easy
4+
5+
Given an array of positive integers`nums`, return_an array_`answer`_that consists of the digits of each integer in_`nums`_after separating them in**the same order** they appear in_`nums`.
6+
7+
To separate the digits of an integer is to get all the digits it has in the same order.
8+
9+
* For example, for the integer`10921`, the separation of its digits is`[1,0,9,2,1]`.
10+
11+
**Example 1:**
12+
13+
**Input:** nums =[13,25,83,77]
14+
15+
**Output:**[1,3,2,5,8,3,7,7]
16+
17+
**Explanation:**
18+
19+
- The separation of 13 is[1,3].
20+
21+
- The separation of 25 is[2,5].
22+
23+
- The separation of 83 is[8,3].
24+
25+
- The separation of 77 is[7,7].
26+
27+
answer =[1,3,2,5,8,3,7,7]. Note that answer contains the separations in the same order.
28+
29+
**Example 2:**
30+
31+
**Input:** nums =[7,1,3,9]
32+
33+
**Output:**[7,1,3,9]
34+
35+
**Explanation:**
36+
37+
The separation of each integer in nums is itself. answer =[7,1,3,9].
38+
39+
**Constraints:**
40+
41+
*`1 <= nums.length <= 1000`
42+
* <code>1 <= nums[i] <= 10<sup>5</sup></code>

0 commit comments

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