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

Commit571be8a

Browse files
authored
Added tasks 1204, 1211, 1251, 1280, 1321
1 parenta5917a3 commit571be8a

File tree

15 files changed

+821
-0
lines changed

15 files changed

+821
-0
lines changed
Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,60 @@
1+
1204\. Last Person to Fit in the Bus
2+
3+
Medium
4+
5+
SQL Schema
6+
7+
Table:`Queue`
8+
9+
+-------------+---------+
10+
| Column Name | Type |
11+
+-------------+---------+
12+
| person_id | int |
13+
| person_name | varchar |
14+
| weight | int |
15+
| turn | int |
16+
+-------------+---------+
17+
18+
person_id is the primary key column for this table. This table has the information about all people waiting for a bus. The person_id and turn columns will contain all numbers from 1 to n, where n is the number of rows in the table. turn determines the order of which the people will board the bus, where turn=1 denotes the first person to board and turn=n denotes the last person to board. weight is the weight of the person in kilograms.
19+
20+
There is a queue of people waiting to board a bus. However, the bus has a weight limit of`1000`**kilograms**, so there may be some people who cannot board.
21+
22+
Write an SQL query to find the`person_name` of the**last person** that can fit on the bus without exceeding the weight limit. The test cases are generated such that the first person does not exceed the weight limit.
23+
24+
The query result format is in the following example.
25+
26+
**Example 1:**
27+
28+
**Input:** Queue table:
29+
30+
+-----------+-------------+--------+------+
31+
| person_id | person_name | weight | turn |
32+
+-----------+-------------+--------+------+
33+
| 5 | Alice | 250 | 1 |
34+
| 4 | Bob | 175 | 5 |
35+
| 3 | Alex | 350 | 2 |
36+
| 6 | John Cena | 400 | 3 |
37+
| 1 | Winston | 500 | 6 |
38+
| 2 | Marie | 200 | 4 |
39+
+-----------+-------------+--------+------+
40+
41+
**Output:**
42+
43+
+-------------+
44+
| person_name |
45+
+-------------+
46+
| John Cena |
47+
+-------------+
48+
49+
**Explanation:** The folowing table is ordered by the turn for simplicity.
50+
51+
+------+----+-----------+--------+--------------+
52+
| Turn | ID | Name | Weight | Total Weight |
53+
+------+----+-----------+--------+--------------+
54+
| 1 | 5 | Alice | 250 | 250 |
55+
| 2 | 3 | Alex | 350 | 600 |
56+
| 3 | 6 | John Cena | 400 | 1000 | (last person to board)
57+
| 4 | 2 | Marie | 200 | 1200 | (cannot board)
58+
| 5 | 4 | Bob | 175 | ___ |
59+
| 6 | 1 | Winston | 500 | ___ |
60+
+------+----+-----------+--------+--------------+
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+
# #Medium #Database #2023_08_14_Time_1581_ms_(84.10%)_Space_0B_(100.00%)
3+
WITH ctxAS(
4+
SELECT person_name,SUM(weight) OVER(ORDER BY turnASC)AS running_sum
5+
FROM queue
6+
)
7+
8+
SELECT person_name
9+
FROM ctx
10+
WHERE running_sum<=1000
11+
ORDER BY running_sumDESC
12+
LIMIT1;
Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,60 @@
1+
1211\. Queries Quality and Percentage
2+
3+
Easy
4+
5+
SQL Schema
6+
7+
Table:`Queries`
8+
9+
+-------------+---------+
10+
| Column Name | Type |
11+
+-------------+---------+
12+
| query_name | varchar |
13+
| result | varchar |
14+
| position | int |
15+
| rating | int |
16+
+-------------+---------+
17+
18+
There is no primary key for this table, it may have duplicate rows. This table contains information collected from some queries on a database. The`position` column has a value from**1** to**500**. The`rating` column has a value from**1** to**5**. Query with`rating` less than 3 is a poor query.
19+
20+
We define query`quality` as:
21+
22+
>The average of the ratio between query rating and its position.
23+
24+
We also define`poor query percentage` as:
25+
26+
>The percentage of all queries with rating less than 3.
27+
28+
Write an SQL query to find each`query_name`, the`quality` and`poor_query_percentage`.
29+
30+
Both`quality` and`poor_query_percentage` should be**rounded to 2 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:** Queries table:
39+
40+
+------------+-------------------+----------+--------+
41+
| query_name | result | position | rating |
42+
+------------+-------------------+----------+--------+
43+
| Dog | Golden Retriever | 1 | 5 |
44+
| Dog | German Shepherd | 2 | 5 |
45+
| Dog | Mule | 200 | 1 |
46+
| Cat | Shirazi | 5 | 2 |
47+
| Cat | Siamese | 3 | 3 |
48+
| Cat | Sphynx | 7 | 4 |
49+
+------------+-------------------+----------+--------+
50+
51+
**Output:**
52+
53+
+------------+---------+-----------------------+
54+
| query_name | quality | poor_query_percentage |
55+
+------------+---------+-----------------------+
56+
| Dog | 2.50 | 33.33 |
57+
| Cat | 0.66 | 33.33 |
58+
+------------+---------+-----------------------+
59+
60+
**Explanation:** Dog queries quality is ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50 Dog queries poor_ query_percentage is (1 / 3) * 100 = 33.33 Cat queries quality equals ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66 Cat queries poor_ query_percentage is (1 / 3) * 100 = 33.33
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+
# #Easy #Database #2023_08_14_Time_1230_ms_(82.08%)_Space_0B_(100.00%)
3+
SELECT query_name,
4+
ROUND(AVG(rating/position),2)AS quality,
5+
ROUND(AVG((rating<3)*100),2)AS poor_query_percentage
6+
FROM Queries
7+
GROUP BY query_name
Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,71 @@
1+
1251\. Average Selling Price
2+
3+
Easy
4+
5+
SQL Schema
6+
7+
Table:`Prices`
8+
9+
+---------------+---------+
10+
| Column Name | Type |
11+
+---------------+---------+
12+
| product_id | int |
13+
| start_date | date |
14+
| end_date | date |
15+
| price | int |
16+
+---------------+---------+
17+
18+
(product_id, start_date, end_date) is the primary key for this table. Each row of this table indicates the price of the product_id in the period from start_date to end_date. For each product_id there will be no two overlapping periods. That means there will be no two intersecting periods for the same product_id.
19+
20+
Table:`UnitsSold`
21+
22+
+---------------+---------+
23+
| Column Name | Type |
24+
+---------------+---------+
25+
| product_id | int |
26+
| purchase_date | date |
27+
| units | int |
28+
+---------------+---------+
29+
30+
There is no primary key for this table, it may contain duplicates. Each row of this table indicates the date, units, and product_id of each product sold.
31+
32+
Write an SQL query to find the average selling price for each product.`average_price` should be**rounded to 2 decimal places**.
33+
34+
Return the result table in**any order**.
35+
36+
The query result format is in the following example.
37+
38+
**Example 1:**
39+
40+
**Input:** Prices table:
41+
42+
+------------+------------+------------+--------+
43+
| product_id | start_date | end_date | price |
44+
+------------+------------+------------+--------+
45+
| 1 | 2019-02-17 | 2019-02-28 | 5 |
46+
| 1 | 2019-03-01 | 2019-03-22 | 20 |
47+
| 2 | 2019-02-01 | 2019-02-20 | 15 |
48+
| 2 | 2019-02-21 | 2019-03-31 | 30 |
49+
+------------+------------+------------+--------+
50+
51+
UnitsSold table:
52+
53+
+------------+---------------+-------+
54+
| product_id | purchase_date | units |
55+
+------------+---------------+-------+
56+
| 1 | 2019-02-25 | 100 |
57+
| 1 | 2019-03-01 | 15 |
58+
| 2 | 2019-02-10 | 200 |
59+
| 2 | 2019-03-22 | 30 |
60+
+------------+---------------+-------+
61+
62+
**Output:**
63+
64+
+------------+---------------+
65+
| product_id | average_price |
66+
+------------+---------------+
67+
| 1 | 6.96 |
68+
| 2 | 16.96 |
69+
+------------+---------------+
70+
71+
**Explanation:** Average selling price = Total Price of Product / Number of products sold. Average selling price for product 1 = ((100 * 5) + (15 * 20)) / 115 = 6.96 Average selling price for product 2 = ((200 * 15) + (30 * 30)) / 230 = 16.96
Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
# Write your MySQL query statement below
2+
# #Easy #Database #2023_08_14_Time_1413_ms_(86.28%)_Space_0B_(100.00%)
3+
SELECTus.product_id,
4+
ROUND(SUM(p.price*us.units)/SUM(us.units),2)AS average_price
5+
FROM UnitsSold us
6+
JOIN Prices p
7+
ONus.product_id=p.product_id
8+
ANDus.purchase_date BETWEENp.start_dateANDp.end_date
9+
GROUP BYus.product_id;
Lines changed: 105 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,105 @@
1+
1280\. Students and Examinations
2+
3+
Easy
4+
5+
SQL Schema
6+
7+
Table:`Students`
8+
9+
+---------------+---------+
10+
| Column Name | Type |
11+
+---------------+---------+
12+
| student_id | int |
13+
| student_name | varchar |
14+
+---------------+---------+
15+
16+
student_id is the primary key for this table. Each row of this table contains the ID and the name of one student in the school.
17+
18+
Table:`Subjects`
19+
20+
+--------------+---------+
21+
| Column Name | Type |
22+
+--------------+---------+
23+
| subject_name | varchar |
24+
+--------------+---------+
25+
26+
subject_name is the primary key for this table. Each row of this table contains the name of one subject in the school.
27+
28+
Table:`Examinations`
29+
30+
+--------------+---------+
31+
| Column Name | Type |
32+
+--------------+---------+
33+
| student_id | int |
34+
| subject_name | varchar |
35+
+--------------+---------+
36+
37+
There is no primary key for this table. It may contain duplicates. Each student from the Students table takes every course from the Subjects table. Each row of this table indicates that a student with ID student_id attended the exam of subject_name.
38+
39+
Write an SQL query to find the number of times each student attended each exam.
40+
41+
Return the result table ordered by`student_id` and`subject_name`.
42+
43+
The query result format is in the following example.
44+
45+
**Example 1:**
46+
47+
**Input:** Students table:
48+
49+
+------------+--------------+
50+
| student_id | student_name |
51+
+------------+--------------+
52+
| 1 | Alice |
53+
| 2 | Bob |
54+
| 13 | John |
55+
| 6 | Alex |
56+
+------------+--------------+
57+
58+
Subjects table:
59+
60+
+--------------+
61+
| subject_name |
62+
+--------------+
63+
| Math |
64+
| Physics |
65+
| Programming |
66+
+--------------+
67+
68+
Examinations table:
69+
70+
+------------+--------------+
71+
| student_id | subject_name |
72+
+------------+--------------+
73+
| 1 | Math |
74+
| 1 | Physics |
75+
| 1 | Programming |
76+
| 2 | Programming |
77+
| 1 | Physics |
78+
| 1 | Math |
79+
| 13 | Math |
80+
| 13 | Programming |
81+
| 13 | Physics |
82+
| 2 | Math |
83+
| 1 | Math |
84+
+------------+--------------+
85+
86+
**Output:**
87+
88+
+------------+--------------+--------------+----------------+
89+
| student_id | student_name | subject_name | attended_exams |
90+
+------------+--------------+--------------+----------------+
91+
| 1 | Alice | Math | 3 |
92+
| 1 | Alice | Physics | 2 |
93+
| 1 | Alice | Programming | 1 |
94+
| 2 | Bob | Math | 1 |
95+
| 2 | Bob | Physics | 0 |
96+
| 2 | Bob | Programming | 1 |
97+
| 6 | Alex | Math | 0 |
98+
| 6 | Alex | Physics | 0 |
99+
| 6 | Alex | Programming | 0 |
100+
| 13 | John | Math | 1 |
101+
| 13 | John | Physics | 1 |
102+
| 13 | John | Programming | 1 |
103+
+------------+--------------+--------------+----------------+
104+
105+
**Explanation:** The result table should contain all students and all subjects. Alice attended the Math exam 3 times, the Physics exam 2 times, and the Programming exam 1 time. Bob attended the Math exam 1 time, the Programming exam 1 time, and did not attend the Physics exam. Alex did not attend any exams. John attended the Math exam 1 time, the Physics exam 1 time, and the Programming exam 1 time.
Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
# Write your MySQL query statement below
2+
# #Easy #Database #2023_08_14_Time_1735_ms_(83.69%)_Space_0B_(100.00%)
3+
SELECTs.student_id,s.student_name,sub.subject_name,
4+
sum(case whensub.subject_name=e.subject_name then1 else0 end)as attended_exams
5+
FROM Students s
6+
cross join subjects sub
7+
left join examinations eone.student_id=s.student_id
8+
group by1,2,3
9+
order by1,2,3

0 commit comments

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