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

Added task 3564#1984

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to ourterms of service andprivacy statement. We’ll occasionally send you account related emails.

Already on GitHub?Sign in to your account

Merged
javadev merged 1 commit intojavadev:mainfromjscrdev:task-3564
May 27, 2025
Merged
Show file tree
Hide file tree
Changes fromall commits
Commits
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
118 changes: 118 additions & 0 deletionssrc/main/java/g3501_3600/s3564_seasonal_sales_analysis/readme.md
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,118 @@
3564\. Seasonal Sales Analysis

Medium

Table: `sales`

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| sale_id | int |
| product_id | int |
| sale_date | date |
| quantity | int |
| price | decimal |
+---------------+---------+
sale_id is the unique identifier for this table.
Each row contains information about a product sale including the product_id,
date of sale, quantity sold, and price per unit.

Table: `products`

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| product_name | varchar |
| category | varchar |
+---------------+---------+
product_id is the unique identifier for this table.
Each row contains information about a product including its name and category.

Write a solution to find the most popular product category for each season. The seasons are defined as:

* **Winter**: December, January, February
* **Spring**: March, April, May
* **Summer**: June, July, August
* **Fall**: September, October, November

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`).

Return _the result table ordered by season in **ascending** order_.

The result format is in the following example.

**Example:**

**Input:**

sales table:

+---------+------------+------------+----------+-------+
| sale_id | product_id | sale_date | quantity | price |
+---------+------------+------------+----------+-------+
| 1 | 1 | 2023-01-15 | 5 | 10.00 |
| 2 | 2 | 2023-01-20 | 4 | 15.00 |
| 3 | 3 | 2023-03-10 | 3 | 18.00 |
| 4 | 4 | 2023-04-05 | 1 | 20.00 |
| 5 | 1 | 2023-05-20 | 2 | 10.00 |
| 6 | 2 | 2023-06-12 | 4 | 15.00 |
| 7 | 5 | 2023-06-15 | 5 | 12.00 |
| 8 | 3 | 2023-07-24 | 2 | 18.00 |
| 9 | 4 | 2023-08-01 | 5 | 20.00 |
| 10 | 5 | 2023-09-03 | 3 | 12.00 |
| 11 | 1 | 2023-09-25 | 6 | 10.00 |
| 12 | 2 | 2023-11-10 | 4 | 15.00 |
| 13 | 3 | 2023-12-05 | 6 | 18.00 |
| 14 | 4 | 2023-12-22 | 3 | 20.00 |
| 15 | 5 | 2024-02-14 | 2 | 12.00 |
+---------+------------+------------+----------+-------+

products table:

+------------+-----------------+----------+
| product_id | product_name | category |
+------------+-----------------+----------+
| 1 | Warm Jacket | Apparel |
| 2 | Designer Jeans | Apparel |
| 3 | Cutting Board | Kitchen |
| 4 | Smart Speaker | Tech |
| 5 | Yoga Mat | Fitness |
+------------+-----------------+----------+

**Output:**

+---------+----------+----------------+---------------+
| season | category | total_quantity | total_revenue |
+---------+----------+----------------+---------------+
| Fall | Apparel | 10 | 120.00 |
| Spring | Kitchen | 3 | 54.00 |
| Summer | Tech | 5 | 100.00 |
| Winter | Apparel | 9 | 110.00 |
+---------+----------+----------------+---------------+

**Explanation:**

* **Fall (Sep, Oct, Nov):**
* Apparel: 10 items sold (6 Jackets in Sep, 4 Jeans in Nov), revenue $120.00 (6×$10.00 + 4×$15.00)
* Fitness: 3 Yoga Mats sold in Sep, revenue $36.00
* Most popular: Apparel with highest total quantity (10)
* **Spring (Mar, Apr, May):**
* Kitchen: 3 Cutting Boards sold in Mar, revenue $54.00
* Tech: 1 Smart Speaker sold in Apr, revenue $20.00
* Apparel: 2 Warm Jackets sold in May, revenue $20.00
* Most popular: Kitchen with highest total quantity (3) and highest revenue ($54.00)
* **Summer (Jun, Jul, Aug):**
* Apparel: 4 Designer Jeans sold in Jun, revenue $60.00
* Fitness: 5 Yoga Mats sold in Jun, revenue $60.00
* Kitchen: 2 Cutting Boards sold in Jul, revenue $36.00
* Tech: 5 Smart Speakers sold in Aug, revenue $100.00
* Most popular: Tech and Fitness both have 5 items, but Tech has higher revenue ($100.00 vs $60.00)
* **Winter (Dec, Jan, Feb):**
* Apparel: 9 items sold (5 Jackets in Jan, 4 Jeans in Jan), revenue $110.00
* Kitchen: 6 Cutting Boards sold in Dec, revenue $108.00
* Tech: 3 Smart Speakers sold in Dec, revenue $60.00
* Fitness: 2 Yoga Mats sold in Feb, revenue $24.00
* Most popular: Apparel with highest total quantity (9) and highest revenue ($110.00)

The result table is ordered by season in ascending order.
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
# Write your MySQL query statement below
# #Medium #Database #2025_05_26_Time_505_ms_(100.00%)_Space_0.0_MB_(100.00%)
WITH cte AS (
SELECT CASE
WHEN MONTH(sale_date) IN (1, 2, 12) THEN 'Winter'
WHEN MONTH(sale_date) IN (3, 4, 5) THEN 'Spring'
WHEN MONTH(sale_date) IN (6, 7, 8) THEN 'Summer'
WHEN MONTH(sale_date) IN (9, 10, 11) THEN 'Fall'
END AS season,
category, SUM(quantity) AS total_quantity, SUM(quantity * price) AS total_revenue
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY season, category
),
cte2 AS (
SELECT season, category, total_quantity, total_revenue,
RANK() OVER (PARTITION BY season ORDER BY total_quantity DESC, total_revenue DESC) AS ranking
FROM cte
)
SELECT
season, category, total_quantity, total_revenue
FROM cte2
WHERE ranking = 1
ORDER BY season ASC;
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,89 @@
package g3501_3600.s3564_seasonal_sales_analysis;

import static org.hamcrest.CoreMatchers.equalTo;
import static org.hamcrest.MatcherAssert.assertThat;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.stream.Collectors;
import javax.sql.DataSource;
import org.junit.jupiter.api.Test;
import org.zapodot.junit.db.annotations.EmbeddedDatabase;
import org.zapodot.junit.db.annotations.EmbeddedDatabaseTest;
import org.zapodot.junit.db.common.CompatibilityMode;

@EmbeddedDatabaseTest(
compatibilityMode = CompatibilityMode.MySQL,
initialSqls =
"CREATE TABLE sales(sale_id INTEGER, product_id INTEGER"
+ ", sale_date DATE, quantity INTEGER, price DECIMAL); "
+ "INSERT INTO sales (sale_id, product_id, sale_date, quantity, price) VALUES"
+ "(1, 1, '2023-01-15', 5, 10.00),"
+ "(2, 2, '2023-01-20', 4, 15.00),"
+ "(3, 3, '2023-03-10', 3, 18.00),"
+ "(4, 4, '2023-04-05', 1, 20.00),"
+ "(5, 1, '2023-05-20', 2, 10.00),"
+ "(6, 2, '2023-06-12', 4, 15.00),"
+ "(7, 5, '2023-06-15', 5, 12.00),"
+ "(8, 3, '2023-07-24', 2, 18.00),"
+ "(9, 4, '2023-08-01', 5, 20.00),"
+ "(10, 5, '2023-09-03', 3, 12.00),"
+ "(11, 1, '2023-09-25', 6, 10.00),"
+ "(12, 2, '2023-11-10', 4, 15.00),"
+ "(13, 3, '2023-12-05', 6, 18.00),"
+ "(14, 4, '2023-12-22', 3, 20.00),"
+ "(15, 5, '2024-02-14', 2, 12.00);"
+ "CREATE TABLE products(product_id INTEGER, product_name VARCHAR(255)"
+ ", category VARCHAR(255)); "
+ "INSERT INTO products (product_id, product_name, category) VALUES"
+ "(1, 'Warm Jacket', 'Apparel'),"
+ "(2, 'Designer Jeans', 'Apparel'),"
+ "(3, 'Cutting Board', 'Kitchen'),"
+ "(4, 'Smart Speaker', 'Tech'),"
+ "(5, 'Yoga Mat', 'Fitness');")
class MysqlTest {
@Test
void testScript(@EmbeddedDatabase DataSource dataSource)
throws SQLException, FileNotFoundException {
try (final Connection connection = dataSource.getConnection()) {
try (final Statement statement = connection.createStatement();
final ResultSet resultSet =
statement.executeQuery(
new BufferedReader(
new FileReader(
"src/main/java/g3501_3600/"
+ "s3564_seasonal_sales_analysis/"
+ "script.sql"))
.lines()
.collect(Collectors.joining("\n"))
.replaceAll("#.*?\\r?\\n", ""))) {
assertThat(resultSet.next(), equalTo(true));
assertThat(resultSet.getNString(1), equalTo("Fall"));
assertThat(resultSet.getNString(2), equalTo("Apparel"));
assertThat(resultSet.getNString(3), equalTo("10"));
assertThat(resultSet.getNString(4), equalTo("120"));
assertThat(resultSet.next(), equalTo(true));
assertThat(resultSet.getNString(1), equalTo("Spring"));
assertThat(resultSet.getNString(2), equalTo("Kitchen"));
assertThat(resultSet.getNString(3), equalTo("3"));
assertThat(resultSet.getNString(4), equalTo("54"));
assertThat(resultSet.next(), equalTo(true));
assertThat(resultSet.getNString(1), equalTo("Summer"));
assertThat(resultSet.getNString(2), equalTo("Tech"));
assertThat(resultSet.getNString(3), equalTo("5"));
assertThat(resultSet.getNString(4), equalTo("100"));
assertThat(resultSet.next(), equalTo(true));
assertThat(resultSet.getNString(1), equalTo("Winter"));
assertThat(resultSet.getNString(2), equalTo("Apparel"));
assertThat(resultSet.getNString(3), equalTo("9"));
assertThat(resultSet.getNString(4), equalTo("110"));
assertThat(resultSet.next(), equalTo(false));
}
}
}
}
[8]ページ先頭
©2009-2025 Movatter.jp