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

Commit700c9ee

Browse files
authored
Added tasks 1661, 1683, 1731, 1789, 1907
1 parent986250d commit700c9ee

File tree

15 files changed

+659
-0
lines changed

15 files changed

+659
-0
lines changed
Lines changed: 76 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,76 @@
1+
1661\. Average Time of Process per Machine
2+
3+
Easy
4+
5+
SQL Schema
6+
7+
Table:`Activity`
8+
9+
+----------------+---------+
10+
| Column Name | Type |
11+
+----------------+---------+
12+
| machine_id | int |
13+
| process_id | int |
14+
| activity_type | enum |
15+
| timestamp | float |
16+
+----------------+---------+
17+
The table shows the user activities for a factory website.
18+
(machine_id, process_id, activity_type) is the primary key of this table.
19+
machine_id is the ID of a machine.
20+
process_id is the ID of a process running on the machine with ID machine_id.
21+
activity_type is an ENUM of type ('start', 'end').
22+
timestamp is a float representing the current time in seconds.
23+
'start' means the machine starts the process at the given timestamp and 'end' means the machine ends the process at the given timestamp.
24+
The 'start' timestamp will always be before the 'end' timestamp for every (machine_id, process_id) pair.
25+
26+
There is a factory website that has several machines each running the**same number of processes**. Write an SQL query to find the**average time** each machine takes to complete a process.
27+
28+
The time to complete a process is the`'end' timestamp` minus the`'start' timestamp`. The average time is calculated by the total time to complete every process on the machine divided by the number of processes that were run.
29+
30+
The resulting table should have the`machine_id` along with the**average time** as`processing_time`, which should be**rounded to 3 decimal places**.
31+
32+
Return the result table in**any order**.
33+
34+
The query result format is in the following example.
35+
36+
**Example 1:**
37+
38+
**Input:**
39+
40+
Activity table:
41+
+------------+------------+---------------+-----------+
42+
| machine_id | process_id | activity_type | timestamp |
43+
+------------+------------+---------------+-----------+
44+
| 0 | 0 | start | 0.712 |
45+
| 0 | 0 | end | 1.520 |
46+
| 0 | 1 | start | 3.140 |
47+
| 0 | 1 | end | 4.120 |
48+
| 1 | 0 | start | 0.550 |
49+
| 1 | 0 | end | 1.550 |
50+
| 1 | 1 | start | 0.430 |
51+
| 1 | 1 | end | 1.420 |
52+
| 2 | 0 | start | 4.100 |
53+
| 2 | 0 | end | 4.512 |
54+
| 2 | 1 | start | 2.500 |
55+
| 2 | 1 | end | 5.000 |
56+
+------------+------------+---------------+-----------+
57+
58+
**Output:**
59+
60+
+------------+-----------------+
61+
| machine_id | processing_time |
62+
+------------+-----------------+
63+
| 0 | 0.894 |
64+
| 1 | 0.995 |
65+
| 2 | 1.456 |
66+
+------------+-----------------+
67+
68+
**Explanation:**
69+
70+
There are 3 machines running 2 processes each.
71+
72+
Machine 0's average time is ((1.520 - 0.712) + (4.120 - 3.140)) / 2 = 0.894
73+
74+
Machine 1's average time is ((1.550 - 0.550) + (1.420 - 0.430)) / 2 = 0.995
75+
76+
Machine 2's average time is ((4.512 - 4.100) + (5.000 - 2.500)) / 2 = 1.456
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_16_Time_429_ms_(79.06%)_Space_0B_(100.00%)
3+
selectact1.machine_id,round(avg(act2.timestamp-act1.timestamp),3)as processing_timefrom activity act1join activity act2onact1.machine_id=act2.machine_id
4+
whereact1.process_id=act2.process_idandact1.activity_type='start'
5+
andact2.activity_type='end'group byact1.machine_id;
Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,48 @@
1+
1683\. Invalid Tweets
2+
3+
Easy
4+
5+
SQL Schema
6+
7+
Table:`Tweets`
8+
9+
+----------------+---------+
10+
| Column Name | Type |
11+
+----------------+---------+
12+
| tweet_id | int |
13+
| content | varchar |
14+
+----------------+---------+
15+
tweet_id is the primary key for this table.
16+
This table contains all the tweets in a social media app.
17+
18+
Write an SQL query to find the IDs of the invalid tweets. The tweet is invalid if the number of characters used in the content of the tweet is**strictly greater** than`15`.
19+
20+
Return the result table in**any order**.
21+
22+
The query result format is in the following example.
23+
24+
**Example 1:**
25+
26+
**Input:**
27+
28+
Tweets table:
29+
+----------+----------------------------------+
30+
| tweet_id | content |
31+
+----------+----------------------------------+
32+
| 1 | Vote for Biden |
33+
| 2 | Let us make America great again! |
34+
+----------+----------------------------------+
35+
36+
**Output:**
37+
38+
+----------+
39+
| tweet_id |
40+
+----------+
41+
| 2 |
42+
+----------+
43+
44+
**Explanation:**
45+
46+
Tweet 1 has length = 14. It is a valid tweet.
47+
48+
Tweet 2 has length = 32. It is an invalid tweet.
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_16_Time_1184_ms_(78.28%)_Space_0B_(100.00%)
3+
SELECT tweet_idFROM Tweets
4+
WHERE LENGTH(content)>15;
Lines changed: 55 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,55 @@
1+
1731\. The Number of Employees Which Report to Each Employee
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+
| reports_to | int |
15+
| age | int |
16+
+-------------+----------+
17+
employee_id is the primary key for this table.
18+
This table contains information about the employees and the id of the manager they report to.
19+
Some employees do not report to anyone (reports_to is null).
20+
21+
For this problem, we will consider a**manager** an employee who has at least 1 other employee reporting to them.
22+
23+
Write an SQL query to report the ids and the names of all**managers**, the number of employees who report**directly** to them, and the average age of the reports rounded to the nearest integer.
24+
25+
Return the result table ordered by`employee_id`.
26+
27+
The query result format is in the following example.
28+
29+
**Example 1:**
30+
31+
**Input:**
32+
33+
Employees table:
34+
+-------------+---------+------------+-----+
35+
| employee_id | name | reports_to | age |
36+
+-------------+---------+------------+-----+
37+
| 9 | Hercy | null | 43 |
38+
| 6 | Alice | 9 | 41 |
39+
| 4 | Bob | 9 | 36 |
40+
| 2 | Winston | null | 37 |
41+
+-------------+---------+------------+-----+
42+
43+
**Output:**
44+
45+
+-------------+-------+---------------+-------------+
46+
| employee_id | name | reports_count | average_age |
47+
+-------------+-------+---------------+-------------+
48+
| 9 | Hercy | 2 | 39 |
49+
+-------------+-------+---------------+-------------+
50+
51+
**Explanation:**
52+
53+
Hercy has 2 people report directly to him, Alice and Bob.
54+
55+
Their average age is (41+36)/2 = 38.5, which is 39 after rounding it to the nearest integer.
Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
# Write your MySQL query statement below
2+
# #Easy #Database #2023_08_16_Time_1162_ms_(87.26%)_Space_0B_(100.00%)
3+
SELECT
4+
t1.employee_id,
5+
t1.name,
6+
count(t2.reports_to)as reports_count,
7+
ROUND(avg(t2.age))as average_age
8+
FROM
9+
Employees t1
10+
INNER JOIN Employees t2ont1.employee_id=t2.reports_to
11+
GROUP BY1,2
12+
ORDER BY employee_id
Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,66 @@
1+
1789\. Primary Department for Each Employee
2+
3+
Easy
4+
5+
SQL Schema
6+
7+
Table:`Employee`
8+
9+
+---------------+---------+
10+
| Column Name | Type |
11+
+---------------+---------+
12+
| employee_id | int |
13+
| department_id | int |
14+
| primary_flag | varchar |
15+
+---------------+---------+
16+
(employee_id, department_id) is the primary key for this table.
17+
employee_id is the id of the employee.
18+
department_id is the id of the department to which the employee belongs.
19+
primary_flag is an ENUM of type ('Y', 'N'). If the flag is 'Y', the department is the primary department for the employee.
20+
If the flag is 'N', the department is not the primary.
21+
22+
Employees can belong to multiple departments. When the employee joins other departments, they need to decide which department is their primary department. Note that when an employee belongs to only one department, their primary column is`'N'`.
23+
24+
Write an SQL query to report all the employees with their primary department. For employees who belong to one department, report their only department.
25+
26+
Return the result table in**any order**.
27+
28+
The query result format is in the following example.
29+
30+
**Example 1:**
31+
32+
**Input:**
33+
34+
Employee table:
35+
+-------------+---------------+--------------+
36+
| employee_id | department_id | primary_flag |
37+
+-------------+---------------+--------------+
38+
| 1 | 1 | N |
39+
| 2 | 1 | Y |
40+
| 2 | 2 | N |
41+
| 3 | 3 | N |
42+
| 4 | 2 | N |
43+
| 4 | 3 | Y |
44+
| 4 | 4 | N |
45+
+-------------+---------------+--------------+
46+
47+
**Output:**
48+
49+
+-------------+---------------+
50+
| employee_id | department_id |
51+
+-------------+---------------+
52+
| 1 | 1 |
53+
| 2 | 1 |
54+
| 3 | 3 |
55+
| 4 | 3 |
56+
+-------------+---------------+
57+
58+
**Explanation:**
59+
60+
- The Primary department for employee 1 is 1.
61+
62+
- The Primary department for employee 2 is 1.
63+
64+
- The Primary department for employee 3 is 3.
65+
66+
- The Primary department for employee 4 is 3.
Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
# Write your MySQL query statement below
2+
# #Easy #Database #2023_08_16_Time_1122_ms_(74.47%)_Space_0B_(100.00%)
3+
WITH cteAS (
4+
SELECT DISTINCT employee_id, department_id,
5+
COUNT(employee_id) OVER (PARTITION BY employee_id)AS n
6+
FROM Employee
7+
)
8+
SELECT employee_id, department_id
9+
FROM cte
10+
WHERE n=1
11+
UNION
12+
SELECT employee_id, department_id
13+
FROM Employee
14+
WHERE primary_flag='Y';
Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,60 @@
1+
1907\. Count Salary Categories
2+
3+
Medium
4+
5+
SQL Schema
6+
7+
Table:`Accounts`
8+
9+
+-------------+------+
10+
| Column Name | Type |
11+
+-------------+------+
12+
| account_id | int |
13+
| income | int |
14+
+-------------+------+
15+
account_id is the primary key for this table.
16+
Each row contains information about the monthly income for one bank account.
17+
18+
Write an SQL query to report the number of bank accounts of each salary category. The salary categories are:
19+
20+
*`"Low Salary"`: All the salaries**strictly less** than`$20000`.
21+
*`"Average Salary"`: All the salaries in the**inclusive** range`[$20000, $50000]`.
22+
*`"High Salary"`: All the salaries**strictly greater** than`$50000`.
23+
24+
The result table**must** contain all three categories. If there are no accounts in a category, then report`0`.
25+
26+
Return the result table in**any order**.
27+
28+
The query result format is in the following example.
29+
30+
**Example 1:**
31+
32+
**Input:**
33+
34+
Accounts table:
35+
+------------+--------+
36+
| account_id | income |
37+
+------------+--------+
38+
| 3 | 108939 |
39+
| 2 | 12747 |
40+
| 8 | 87709 |
41+
| 6 | 91796 |
42+
+------------+--------+
43+
44+
**Output:**
45+
46+
+----------------+----------------+
47+
| category | accounts_count |
48+
+----------------+----------------+
49+
| Low Salary | 1 |
50+
| Average Salary | 0 |
51+
| High Salary | 3 |
52+
+----------------+----------------+
53+
54+
**Explanation:**
55+
56+
Low Salary: Account 2.
57+
58+
Average Salary: No accounts.
59+
60+
High Salary: Accounts 3, 6, and 8.
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_08_17_Time_2216_ms_(95.81%)_Space_0B_(100.00%)
3+
select'Low Salary'as category,count(*)as accounts_countfrom accountswhere income<20000
4+
union
5+
select'Average Salary'as category,count(*)as accounts_countfrom accountswhere income>=20000and income<=50000
6+
union
7+
select'High Salary'as category,count(*)as accounts_countfrom accountswhere income>50000

0 commit comments

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