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

Commitcb520e1

Browse files
authored
Added task 3564
1 parent768bbac commitcb520e1

File tree

3 files changed

+231
-0
lines changed

3 files changed

+231
-0
lines changed
Lines changed: 118 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,118 @@
1+
3564\. Seasonal Sales Analysis
2+
3+
Medium
4+
5+
Table:`sales`
6+
7+
+---------------+---------+
8+
| Column Name | Type |
9+
+---------------+---------+
10+
| sale_id | int |
11+
| product_id | int |
12+
| sale_date | date |
13+
| quantity | int |
14+
| price | decimal |
15+
+---------------+---------+
16+
sale_id is the unique identifier for this table.
17+
Each row contains information about a product sale including the product_id,
18+
date of sale, quantity sold, and price per unit.
19+
20+
Table:`products`
21+
22+
+---------------+---------+
23+
| Column Name | Type |
24+
+---------------+---------+
25+
| product_id | int |
26+
| product_name | varchar |
27+
| category | varchar |
28+
+---------------+---------+
29+
product_id is the unique identifier for this table.
30+
Each row contains information about a product including its name and category.
31+
32+
Write a solution to find the most popular product category for each season. The seasons are defined as:
33+
34+
***Winter**: December, January, February
35+
***Spring**: March, April, May
36+
***Summer**: June, July, August
37+
***Fall**: September, October, November
38+
39+
The**popularity** of a**category** is determined by the**total quantity sold** in that**season**. If there is a**tie**, select the category with the highest**total revenue** (`quantity × price`).
40+
41+
Return_the result table ordered by season in**ascending** order_.
42+
43+
The result format is in the following example.
44+
45+
**Example:**
46+
47+
**Input:**
48+
49+
sales table:
50+
51+
+---------+------------+------------+----------+-------+
52+
| sale_id | product_id | sale_date | quantity | price |
53+
+---------+------------+------------+----------+-------+
54+
| 1 | 1 | 2023-01-15 | 5 | 10.00 |
55+
| 2 | 2 | 2023-01-20 | 4 | 15.00 |
56+
| 3 | 3 | 2023-03-10 | 3 | 18.00 |
57+
| 4 | 4 | 2023-04-05 | 1 | 20.00 |
58+
| 5 | 1 | 2023-05-20 | 2 | 10.00 |
59+
| 6 | 2 | 2023-06-12 | 4 | 15.00 |
60+
| 7 | 5 | 2023-06-15 | 5 | 12.00 |
61+
| 8 | 3 | 2023-07-24 | 2 | 18.00 |
62+
| 9 | 4 | 2023-08-01 | 5 | 20.00 |
63+
| 10 | 5 | 2023-09-03 | 3 | 12.00 |
64+
| 11 | 1 | 2023-09-25 | 6 | 10.00 |
65+
| 12 | 2 | 2023-11-10 | 4 | 15.00 |
66+
| 13 | 3 | 2023-12-05 | 6 | 18.00 |
67+
| 14 | 4 | 2023-12-22 | 3 | 20.00 |
68+
| 15 | 5 | 2024-02-14 | 2 | 12.00 |
69+
+---------+------------+------------+----------+-------+
70+
71+
products table:
72+
73+
+------------+-----------------+----------+
74+
| product_id | product_name | category |
75+
+------------+-----------------+----------+
76+
| 1 | Warm Jacket | Apparel |
77+
| 2 | Designer Jeans | Apparel |
78+
| 3 | Cutting Board | Kitchen |
79+
| 4 | Smart Speaker | Tech |
80+
| 5 | Yoga Mat | Fitness |
81+
+------------+-----------------+----------+
82+
83+
**Output:**
84+
85+
+---------+----------+----------------+---------------+
86+
| season | category | total_quantity | total_revenue |
87+
+---------+----------+----------------+---------------+
88+
| Fall | Apparel | 10 | 120.00 |
89+
| Spring | Kitchen | 3 | 54.00 |
90+
| Summer | Tech | 5 | 100.00 |
91+
| Winter | Apparel | 9 | 110.00 |
92+
+---------+----------+----------------+---------------+
93+
94+
**Explanation:**
95+
96+
***Fall (Sep, Oct, Nov):**
97+
* Apparel: 10 items sold (6 Jackets in Sep, 4 Jeans in Nov), revenue $120.00 (6×$10.00 + 4×$15.00)
98+
* Fitness: 3 Yoga Mats sold in Sep, revenue $36.00
99+
* Most popular: Apparel with highest total quantity (10)
100+
***Spring (Mar, Apr, May):**
101+
* Kitchen: 3 Cutting Boards sold in Mar, revenue $54.00
102+
* Tech: 1 Smart Speaker sold in Apr, revenue $20.00
103+
* Apparel: 2 Warm Jackets sold in May, revenue $20.00
104+
* Most popular: Kitchen with highest total quantity (3) and highest revenue ($54.00)
105+
***Summer (Jun, Jul, Aug):**
106+
* Apparel: 4 Designer Jeans sold in Jun, revenue $60.00
107+
* Fitness: 5 Yoga Mats sold in Jun, revenue $60.00
108+
* Kitchen: 2 Cutting Boards sold in Jul, revenue $36.00
109+
* Tech: 5 Smart Speakers sold in Aug, revenue $100.00
110+
* Most popular: Tech and Fitness both have 5 items, but Tech has higher revenue ($100.00 vs $60.00)
111+
***Winter (Dec, Jan, Feb):**
112+
* Apparel: 9 items sold (5 Jackets in Jan, 4 Jeans in Jan), revenue $110.00
113+
* Kitchen: 6 Cutting Boards sold in Dec, revenue $108.00
114+
* Tech: 3 Smart Speakers sold in Dec, revenue $60.00
115+
* Fitness: 2 Yoga Mats sold in Feb, revenue $24.00
116+
* Most popular: Apparel with highest total quantity (9) and highest revenue ($110.00)
117+
118+
The result table is ordered by season in ascending order.
Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
# Write your MySQL query statement below
2+
# #Medium #Database #2025_05_26_Time_505_ms_(100.00%)_Space_0.0_MB_(100.00%)
3+
WITH cteAS (
4+
SELECT CASE
5+
WHEN MONTH(sale_date)IN (1,2,12) THEN'Winter'
6+
WHEN MONTH(sale_date)IN (3,4,5) THEN'Spring'
7+
WHEN MONTH(sale_date)IN (6,7,8) THEN'Summer'
8+
WHEN MONTH(sale_date)IN (9,10,11) THEN'Fall'
9+
ENDAS season,
10+
category,SUM(quantity)AS total_quantity,SUM(quantity* price)AS total_revenue
11+
FROM sales s
12+
JOIN products pONs.product_id=p.product_id
13+
GROUP BY season, category
14+
),
15+
cte2AS (
16+
SELECT season, category, total_quantity, total_revenue,
17+
RANK() OVER (PARTITION BY seasonORDER BY total_quantityDESC, total_revenueDESC)AS ranking
18+
FROM cte
19+
)
20+
SELECT
21+
season, category, total_quantity, total_revenue
22+
FROM cte2
23+
WHERE ranking=1
24+
ORDER BY seasonASC;
Lines changed: 89 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,89 @@
1+
packageg3501_3600.s3564_seasonal_sales_analysis;
2+
3+
importstaticorg.hamcrest.CoreMatchers.equalTo;
4+
importstaticorg.hamcrest.MatcherAssert.assertThat;
5+
6+
importjava.io.BufferedReader;
7+
importjava.io.FileNotFoundException;
8+
importjava.io.FileReader;
9+
importjava.sql.Connection;
10+
importjava.sql.ResultSet;
11+
importjava.sql.SQLException;
12+
importjava.sql.Statement;
13+
importjava.util.stream.Collectors;
14+
importjavax.sql.DataSource;
15+
importorg.junit.jupiter.api.Test;
16+
importorg.zapodot.junit.db.annotations.EmbeddedDatabase;
17+
importorg.zapodot.junit.db.annotations.EmbeddedDatabaseTest;
18+
importorg.zapodot.junit.db.common.CompatibilityMode;
19+
20+
@EmbeddedDatabaseTest(
21+
compatibilityMode =CompatibilityMode.MySQL,
22+
initialSqls =
23+
"CREATE TABLE sales(sale_id INTEGER, product_id INTEGER"
24+
+", sale_date DATE, quantity INTEGER, price DECIMAL); "
25+
+"INSERT INTO sales (sale_id, product_id, sale_date, quantity, price) VALUES"
26+
+"(1, 1, '2023-01-15', 5, 10.00),"
27+
+"(2, 2, '2023-01-20', 4, 15.00),"
28+
+"(3, 3, '2023-03-10', 3, 18.00),"
29+
+"(4, 4, '2023-04-05', 1, 20.00),"
30+
+"(5, 1, '2023-05-20', 2, 10.00),"
31+
+"(6, 2, '2023-06-12', 4, 15.00),"
32+
+"(7, 5, '2023-06-15', 5, 12.00),"
33+
+"(8, 3, '2023-07-24', 2, 18.00),"
34+
+"(9, 4, '2023-08-01', 5, 20.00),"
35+
+"(10, 5, '2023-09-03', 3, 12.00),"
36+
+"(11, 1, '2023-09-25', 6, 10.00),"
37+
+"(12, 2, '2023-11-10', 4, 15.00),"
38+
+"(13, 3, '2023-12-05', 6, 18.00),"
39+
+"(14, 4, '2023-12-22', 3, 20.00),"
40+
+"(15, 5, '2024-02-14', 2, 12.00);"
41+
+"CREATE TABLE products(product_id INTEGER, product_name VARCHAR(255)"
42+
+", category VARCHAR(255)); "
43+
+"INSERT INTO products (product_id, product_name, category) VALUES"
44+
+"(1, 'Warm Jacket', 'Apparel'),"
45+
+"(2, 'Designer Jeans', 'Apparel'),"
46+
+"(3, 'Cutting Board', 'Kitchen'),"
47+
+"(4, 'Smart Speaker', 'Tech'),"
48+
+"(5, 'Yoga Mat', 'Fitness');")
49+
classMysqlTest {
50+
@Test
51+
voidtestScript(@EmbeddedDatabaseDataSourcedataSource)
52+
throwsSQLException,FileNotFoundException {
53+
try (finalConnectionconnection =dataSource.getConnection()) {
54+
try (finalStatementstatement =connection.createStatement();
55+
finalResultSetresultSet =
56+
statement.executeQuery(
57+
newBufferedReader(
58+
newFileReader(
59+
"src/main/java/g3501_3600/"
60+
+"s3564_seasonal_sales_analysis/"
61+
+"script.sql"))
62+
.lines()
63+
.collect(Collectors.joining("\n"))
64+
.replaceAll("#.*?\\r?\\n",""))) {
65+
assertThat(resultSet.next(),equalTo(true));
66+
assertThat(resultSet.getNString(1),equalTo("Fall"));
67+
assertThat(resultSet.getNString(2),equalTo("Apparel"));
68+
assertThat(resultSet.getNString(3),equalTo("10"));
69+
assertThat(resultSet.getNString(4),equalTo("120"));
70+
assertThat(resultSet.next(),equalTo(true));
71+
assertThat(resultSet.getNString(1),equalTo("Spring"));
72+
assertThat(resultSet.getNString(2),equalTo("Kitchen"));
73+
assertThat(resultSet.getNString(3),equalTo("3"));
74+
assertThat(resultSet.getNString(4),equalTo("54"));
75+
assertThat(resultSet.next(),equalTo(true));
76+
assertThat(resultSet.getNString(1),equalTo("Summer"));
77+
assertThat(resultSet.getNString(2),equalTo("Tech"));
78+
assertThat(resultSet.getNString(3),equalTo("5"));
79+
assertThat(resultSet.getNString(4),equalTo("100"));
80+
assertThat(resultSet.next(),equalTo(true));
81+
assertThat(resultSet.getNString(1),equalTo("Winter"));
82+
assertThat(resultSet.getNString(2),equalTo("Apparel"));
83+
assertThat(resultSet.getNString(3),equalTo("9"));
84+
assertThat(resultSet.getNString(4),equalTo("110"));
85+
assertThat(resultSet.next(),equalTo(false));
86+
}
87+
}
88+
}
89+
}

0 commit comments

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