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

Commitc57b6b7

Browse files
authored
Added tasks 1070, 1075, 1164, 1174, 1193
1 parent0913d13 commitc57b6b7

File tree

15 files changed

+683
-0
lines changed

15 files changed

+683
-0
lines changed
Lines changed: 73 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,73 @@
1+
1070\. Product Sales Analysis III
2+
3+
Medium
4+
5+
SQL Schema
6+
7+
Table:`Sales`
8+
9+
+-------------+-------+
10+
| Column Name | Type |
11+
+-------------+-------+
12+
| sale_id | int |
13+
| product_id | int |
14+
| year | int |
15+
| quantity | int |
16+
| price | int |
17+
+-------------+-------+
18+
19+
(sale_id, year) is the primary key of this table. product_id is a foreign key to`Product` table.
20+
21+
Each row of this table shows a sale on the product product_id in a certain year.
22+
23+
Note that the price is per unit.
24+
25+
Table:`Product`
26+
27+
+--------------+---------+
28+
| Column Name | Type |
29+
+--------------+---------+
30+
| product_id | int |
31+
| product_name | varchar |
32+
+--------------+---------+
33+
34+
product_id is the primary key of this table.
35+
36+
Each row of this table indicates the product name of each product.
37+
38+
Write an SQL query that selects the**product id**,**year**,**quantity**, and**price** for the**first year** of every product sold.
39+
40+
Return the resulting table in**any order**.
41+
42+
The query result format is in the following example.
43+
44+
**Example 1:**
45+
46+
**Input:** Sales table:
47+
48+
+---------+------------+------+----------+-------+
49+
| sale_id | product_id | year | quantity | price |
50+
+---------+------------+------+----------+-------+
51+
| 1 | 100 | 2008 | 10 | 5000 |
52+
| 2 | 100 | 2009 | 12 | 5000 |
53+
| 7 | 200 | 2011 | 15 | 9000 |
54+
+---------+------------+------+----------+-------+
55+
56+
Product table:
57+
58+
+------------+--------------+
59+
| product_id | product_name |
60+
+------------+--------------+
61+
| 100 | Nokia |
62+
| 200 | Apple |
63+
| 300 | Samsung |
64+
+------------+--------------+
65+
66+
**Output:**
67+
68+
+------------+------------+----------+-------+
69+
| product_id | first_year | quantity | price |
70+
+------------+------------+----------+-------+
71+
| 100 | 2008 | 10 | 5000 |
72+
| 200 | 2011 | 15 | 9000 |
73+
+------------+------------+----------+-------+
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_12_Time_1693_ms_(90.17%)_Space_0B_(100.00%)
3+
WITH abAS (SELECT*, RANK() OVER(PARTITION BY product_idORDER BY sale_yearASC)AS rk
4+
FROM Sales)
5+
SELECT product_id, sale_yearAS first_year, quantity, price
6+
FROM ab
7+
WHERE rk=1;
Lines changed: 76 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,76 @@
1+
1075\. Project Employees I
2+
3+
Easy
4+
5+
SQL Schema
6+
7+
Table:`Project`
8+
9+
+-------------+---------+
10+
| Column Name | Type |
11+
+-------------+---------+
12+
| project_id | int |
13+
| employee_id | int |
14+
+-------------+---------+
15+
16+
(project_id, employee_id) is the primary key of this table.
17+
18+
employee_id is a foreign key to`Employee` table.
19+
20+
Each row of this table indicates that the employee with employee_id is working on the project with project_id.
21+
22+
Table:`Employee`
23+
24+
+------------------+---------+
25+
| Column Name | Type |
26+
+------------------+---------+
27+
| employee_id | int |
28+
| name | varchar |
29+
| experience_years | int |
30+
+------------------+---------+
31+
32+
employee_id is the primary key of this table. It's guaranteed that experience_years is not NULL.
33+
34+
Each row of this table contains information about one employee.
35+
36+
Write an SQL query that reports the**average** experience years of all the employees for each project,**rounded to 2 digits**.
37+
38+
Return the result table in**any order**.
39+
40+
The query result format is in the following example.
41+
42+
**Example 1:**
43+
44+
**Input:** Project table:
45+
46+
+-------------+-------------+
47+
| project_id | employee_id |
48+
+-------------+-------------+
49+
| 1 | 1 |
50+
| 1 | 2 |
51+
| 1 | 3 |
52+
| 2 | 1 |
53+
| 2 | 4 |
54+
+-------------+-------------+
55+
56+
Employee table:
57+
58+
+-------------+--------+------------------+
59+
| employee_id | name | experience_years |
60+
+-------------+--------+------------------+
61+
| 1 | Khaled | 3 |
62+
| 2 | Ali | 2 |
63+
| 3 | John | 1 |
64+
| 4 | Doe | 2 |
65+
+-------------+--------+------------------+
66+
67+
**Output:**
68+
69+
+-------------+---------------+
70+
| project_id | average_years |
71+
+-------------+---------------+
72+
| 1 | 2.00 |
73+
| 2 | 2.50 |
74+
+-------------+---------------+
75+
76+
**Explanation:** The average experience years for the first project is (3 + 2 + 1) / 3 = 2.00 and for the second project is (3 + 2) / 2 = 2.50
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_12_Time_1524_ms_(75.86%)_Space_0B_(100.00%)
3+
select project_id,round(avg(
4+
experience_years
5+
),2)as average_yearsfrom Project p
6+
left join
7+
Employee eonp.employee_id=e.employee_idgroup byp.project_id;
Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,50 @@
1+
1164\. Product Price at a Given Date
2+
3+
Medium
4+
5+
SQL Schema
6+
7+
Table:`Products`
8+
9+
+---------------+---------+
10+
| Column Name | Type |
11+
+---------------+---------+
12+
| product_id | int |
13+
| new_price | int |
14+
| change_date | date |
15+
+---------------+---------+
16+
17+
(product_id, change_date) is the primary key of this table.
18+
19+
Each row of this table indicates that the price of some product was changed to a new price at some date.
20+
21+
Write an SQL query to find the prices of all products on`2019-08-16`. Assume the price of all products before any change is`10`.
22+
23+
Return the result table in**any order**.
24+
25+
The query result format is in the following example.
26+
27+
**Example 1:**
28+
29+
**Input:** Products table:
30+
31+
+------------+-----------+-------------+
32+
| product_id | new_price | change_date |
33+
+------------+-----------+-------------+
34+
| 1 | 20 | 2019-08-14 |
35+
| 2 | 50 | 2019-08-14 |
36+
| 1 | 30 | 2019-08-15 |
37+
| 1 | 35 | 2019-08-16 |
38+
| 2 | 65 | 2019-08-17 |
39+
| 3 | 20 | 2019-08-18 |
40+
+------------+-----------+-------------+
41+
42+
**Output:**
43+
44+
+------------+-------+
45+
| product_id | price |
46+
+------------+-------+
47+
| 2 | 50 |
48+
| 1 | 35 |
49+
| 3 | 10 |
50+
+------------+-------+
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+
# #Medium #Database #2023_08_12_Time_920_ms_(76.39%)_Space_0B_(100.00%)
3+
WITH cteAS
4+
(SELECT*, RANK() OVER (PARTITION BY product_idORDER BY change_dateDESC)AS r
5+
FROM Products
6+
WHERE change_date<='2019-08-16')
7+
8+
SELECT product_id, new_priceAS price
9+
FROM cte
10+
WHERE r=1
11+
UNION
12+
SELECT product_id,10AS price
13+
FROM Products
14+
WHERE product_id NOTIN (SELECT product_idFROM cte)
Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,52 @@
1+
1174\. Immediate Food Delivery II
2+
3+
Medium
4+
5+
SQL Schema
6+
7+
Table:`Delivery`
8+
9+
+-----------------------------+---------+
10+
| Column Name | Type |
11+
+-----------------------------+---------+
12+
| delivery_id | int |
13+
| customer_id | int |
14+
| order_date | date |
15+
| customer_pref_delivery_date | date |
16+
+-----------------------------+---------+
17+
18+
delivery_id is the primary key of this table. The table holds information about food delivery to customers that make orders at some date and specify a preferred delivery date (on the same order date or after it).
19+
20+
If the customer's preferred delivery date is the same as the order date, then the order is called**immediate;** otherwise, it is called**scheduled**.
21+
22+
The**first order** of a customer is the order with the earliest order date that the customer made. It is guaranteed that a customer has precisely one first order.
23+
24+
Write an SQL query to find the percentage of immediate orders in the first orders of all customers,**rounded to 2 decimal places**.
25+
26+
The query result format is in the following example.
27+
28+
**Example 1:**
29+
30+
**Input:** Delivery table:
31+
32+
+-------------+-------------+------------+-----------------------------+
33+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
34+
+-------------+-------------+------------+-----------------------------+
35+
| 1 | 1 | 2019-08-01 | 2019-08-02 |
36+
| 2 | 2 | 2019-08-02 | 2019-08-02 |
37+
| 3 | 1 | 2019-08-11 | 2019-08-12 |
38+
| 4 | 3 | 2019-08-24 | 2019-08-24 |
39+
| 5 | 3 | 2019-08-21 | 2019-08-22 |
40+
| 6 | 2 | 2019-08-11 | 2019-08-13 |
41+
| 7 | 4 | 2019-08-09 | 2019-08-09 |
42+
+-------------+-------------+------------+-----------------------------+
43+
44+
**Output:**
45+
46+
+----------------------+
47+
| immediate_percentage |
48+
+----------------------+
49+
| 50.00 |
50+
+----------------------+
51+
52+
**Explanation:** The customer id 1 has a first order with delivery id 1 and it is scheduled. The customer id 2 has a first order with delivery id 2 and it is immediate. The customer id 3 has a first order with delivery id 5 and it is scheduled. The customer id 4 has a first order with delivery id 7 and it is immediate. Hence, half the customers have immediate first orders.
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+
# #Medium #Database #2023_08_12_Time_1205_ms_(78.39%)_Space_0B_(100.00%)
3+
SELECT ROUND(AVG(CASE WHEN customer_pref_delivery_date= order_date THEN1 ELSE0 END)*100,2)AS immediate_percentage
4+
FROM (
5+
SELECT*,
6+
DENSE_RANK() OVER(PARTITION BY customer_idORDER BY order_dateASC)AS dnsrnk
7+
FROM delivery
8+
) subquery_alias
9+
WHEREsubquery_alias.dnsrnk=1;
Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,52 @@
1+
1193\. Monthly Transactions I
2+
3+
Medium
4+
5+
SQL Schema
6+
7+
Table:`Transactions`
8+
9+
+---------------+---------+
10+
| Column Name | Type |
11+
+---------------+---------+
12+
| id | int |
13+
| country | varchar |
14+
| state | enum |
15+
| amount | int |
16+
| trans_date | date |
17+
+---------------+---------+
18+
19+
id is the primary key of this table.
20+
21+
The table has information about incoming transactions.
22+
23+
The state column is an enum of type["approved", "declined"].
24+
25+
Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount.
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:** Transactions table:
34+
35+
+------+---------+----------+--------+------------+
36+
| id | country | state | amount | trans_date |
37+
+------+---------+----------+--------+------------+
38+
| 121 | US | approved | 1000 | 2018-12-18 |
39+
| 122 | US | declined | 2000 | 2018-12-19 |
40+
| 123 | US | approved | 2000 | 2019-01-01 |
41+
| 124 | DE | approved | 2000 | 2019-01-07 |
42+
+------+---------+----------+--------+------------+
43+
44+
**Output:**
45+
46+
+----------+---------+-------------+----------------+--------------------+-----------------------+
47+
| month | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
48+
+----------+---------+-------------+----------------+--------------------+-----------------------+
49+
| 2018-12 | US | 2 | 1 | 3000 | 1000 |
50+
| 2019-01 | US | 1 | 1 | 2000 | 2000 |
51+
| 2019-01 | DE | 1 | 1 | 2000 | 2000 |
52+
+----------+---------+-------------+----------------+--------------------+-----------------------+
Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
# Write your MySQL query statement below
2+
# #Medium #Database #2023_08_12_Time_961_ms_(88.29%)_Space_0B_(100.00%)
3+
SELECT
4+
FORMATDATETIME(trans_date,'yyyy-MM')AS trans_month,
5+
country,
6+
COUNT(*)AS trans_count,
7+
SUM(CASE WHEN state='approved' THEN1 ELSE0 END)AS approved_count,
8+
SUM(amount)AS trans_total_amount,
9+
SUM(CASE WHEN state='approved' THEN amount ELSE0 END)AS approved_total_amount
10+
FROM Transactions
11+
GROUP BY trans_month, country;

0 commit comments

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