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

Commit5bfebf5

Browse files
authored
Added task 3626
1 parentffd3c88 commit5bfebf5

File tree

3 files changed

+282
-0
lines changed

3 files changed

+282
-0
lines changed
Lines changed: 118 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,118 @@
1+
3626\. Find Stores with Inventory Imbalance
2+
3+
Medium
4+
5+
Table:`stores`
6+
7+
+------------+----------+
8+
| Column Name| Type |
9+
+------------+----------+
10+
| store_id | int |
11+
| store_name | varchar |
12+
| location | varchar |
13+
+------------+----------+
14+
15+
store_id is the unique identifier for this table.
16+
Each row contains information about a store and its location.
17+
18+
Table:`inventory`
19+
20+
+--------------+----------+
21+
| Column Name | Type |
22+
+--------------+----------+
23+
| inventory_id | int |
24+
| store_id | int |
25+
| product_name | varchar |
26+
| quantity | int |
27+
| price | decimal |
28+
+--------------+----------+
29+
30+
inventory_id is the unique identifier for this table.
31+
Each row represents the inventory of a specific product at a specific store.
32+
33+
Write a solution to find stores that have**inventory imbalance** - stores where the most expensive product has lower stock than the cheapest product.
34+
35+
* For each store, identify the**most expensive product** (highest price) and its quantity
36+
* For each store, identify the**cheapest product** (lowest price) and its quantity
37+
* A store has inventory imbalance if the most expensive product's quantity is**less than** the cheapest product's quantity
38+
* Calculate the**imbalance ratio** as (cheapest\_quantity / most\_expensive\_quantity)
39+
***Round** the imbalance ratio to**2** decimal places
40+
* Only include stores that have**at least**`3`**different products**
41+
42+
Return_the result table ordered by imbalance ratio in**descending** order, then by store name in**ascending** order_.
43+
44+
The result format is in the following example.
45+
46+
**Example:**
47+
48+
**Input:**
49+
50+
stores table:
51+
52+
+----------+----------------+-------------+
53+
| store_id | store_name | location |
54+
+----------+----------------+-------------+
55+
| 1 | Downtown Tech | New York |
56+
| 2 | Suburb Mall | Chicago |
57+
| 3 | City Center | Los Angeles |
58+
| 4 | Corner Shop | Miami |
59+
| 5 | Plaza Store | Seattle |
60+
+----------+----------------+-------------+
61+
62+
inventory table:
63+
64+
+--------------+----------+--------------+----------+--------+
65+
| inventory_id | store_id | product_name | quantity | price |
66+
+--------------+----------+--------------+----------+--------+
67+
| 1 | 1 | Laptop | 5 | 999.99 |
68+
| 2 | 1 | Mouse | 50 | 19.99 |
69+
| 3 | 1 | Keyboard | 25 | 79.99 |
70+
| 4 | 1 | Monitor | 15 | 299.99 |
71+
| 5 | 2 | Phone | 3 | 699.99 |
72+
| 6 | 2 | Charger | 100 | 25.99 |
73+
| 7 | 2 | Case | 75 | 15.99 |
74+
| 8 | 2 | Headphones | 20 | 149.99 |
75+
| 9 | 3 | Tablet | 2 | 499.99 |
76+
| 10 | 3 | Stylus | 80 | 29.99 |
77+
| 11 | 3 | Cover | 60 | 39.99 |
78+
| 12 | 4 | Watch | 10 | 299.99 |
79+
| 13 | 4 | Band | 25 | 49.99 |
80+
| 14 | 5 | Camera | 8 | 599.99 |
81+
| 15 | 5 | Lens | 12 | 199.99 |
82+
+--------------+----------+--------------+----------+--------+
83+
84+
**Output:**
85+
86+
+----------+----------------+-------------+------------------+--------------------+------------------+
87+
| store_id | store_name | location | most_exp_product | cheapest_product | imbalance_ratio |
88+
+----------+----------------+-------------+------------------+--------------------+------------------+
89+
| 3 | City Center | Los Angeles | Tablet | Stylus | 40.00 |
90+
| 1 | Downtown Tech | New York | Laptop | Mouse | 10.00 |
91+
| 2 | Suburb Mall | Chicago | Phone | Case | 25.00 |
92+
+----------+----------------+-------------+------------------+--------------------+------------------+
93+
94+
**Explanation:**
95+
96+
***Downtown Tech (store\_id = 1):**
97+
* Most expensive product: Laptop ($999.99) with quantity 5
98+
* Cheapest product: Mouse ($19.99) with quantity 50
99+
* Inventory imbalance: 5 < 50 (expensive product has lower stock)
100+
* Imbalance ratio: 50 / 5 = 10.00
101+
* Has 4 products (≥ 3), so qualifies
102+
***Suburb Mall (store\_id = 2):**
103+
* Most expensive product: Phone ($699.99) with quantity 3
104+
* Cheapest product: Case ($15.99) with quantity 75
105+
* Inventory imbalance: 3 < 75 (expensive product has lower stock)
106+
* Imbalance ratio: 75 / 3 = 25.00
107+
* Has 4 products (≥ 3), so qualifies
108+
***City Center (store\_id = 3):**
109+
* Most expensive product: Tablet ($499.99) with quantity 2
110+
* Cheapest product: Stylus ($29.99) with quantity 80
111+
* Inventory imbalance: 2 < 80 (expensive product has lower stock)
112+
* Imbalance ratio: 80 / 2 = 40.00
113+
* Has 3 products (≥ 3), so qualifies
114+
***Stores not included:**
115+
* Corner Shop (store\_id = 4): Only has 2 products (Watch, Band) - doesn't meet minimum 3 products requirement
116+
* Plaza Store (store\_id = 5): Only has 2 products (Camera, Lens) - doesn't meet minimum 3 products requirement
117+
118+
The Results table is ordered by imbalance ratio in descending order, then by store name in ascending order
Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,63 @@
1+
# Write your MySQL query statement below
2+
# #Medium #2025_07_25_Time_516_ms_(100.00%)_Space_0.0_MB_(100.00%)
3+
WITH store_product_checkAS (
4+
SELECT
5+
s.store_id,
6+
s.store_name,
7+
s.location,
8+
COUNT(i.inventory_id)AS store_product_ct
9+
FROM
10+
stores s
11+
JOIN inventory iONs.store_id=i.store_id
12+
GROUP BY
13+
s.store_id,
14+
s.store_name,
15+
s.location
16+
HAVING
17+
COUNT(i.inventory_id)>=3
18+
),
19+
store_product_rankedAS (
20+
SELECT
21+
s.store_id,
22+
s.store_name,
23+
s.location,
24+
i.inventory_id,
25+
i.product_name,
26+
i.quantity,
27+
i.price,
28+
ROW_NUMBER() OVER (PARTITION BYs.store_idORDER BYi.priceASC)AS low_price_rk,
29+
ROW_NUMBER() OVER (PARTITION BYs.store_idORDER BYi.priceDESC)AS high_price_rk
30+
FROM
31+
stores s
32+
JOIN inventory iONs.store_id=i.store_id
33+
),
34+
high_low_priceAS (
35+
SELECT
36+
spc.store_id,
37+
spc.store_name,
38+
spc.location,
39+
lp.product_nameAS low_price_product_name,
40+
lp.quantity+0.0AS low_price_quantity,
41+
hp.product_nameAS high_price_product_name,
42+
hp.quantity+0.0AS high_price_quantity
43+
FROM
44+
store_product_check spc
45+
JOIN store_product_ranked lp
46+
ONspc.store_id=lp.store_idANDlp.low_price_rk=1
47+
JOIN store_product_ranked hp
48+
ONspc.store_id=hp.store_idANDhp.high_price_rk=1
49+
)
50+
SELECT
51+
hlp.store_id,
52+
hlp.store_name,
53+
hlp.location,
54+
hlp.high_price_product_nameAS most_exp_product,
55+
hlp.low_price_product_nameAS cheapest_product,
56+
ROUND(hlp.low_price_quantity/hlp.high_price_quantity,2)AS imbalance_ratio
57+
FROM
58+
high_low_price hlp
59+
WHERE
60+
hlp.high_price_quantity<hlp.low_price_quantity
61+
ORDER BY
62+
imbalance_ratioDESC,
63+
hlp.store_nameASC;
Lines changed: 101 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,101 @@
1+
packageg3601_3700.s3626_find_stores_with_inventory_imbalance;
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 stores ("
24+
+" store_id INT PRIMARY KEY,"
25+
+" store_name VARCHAR(50),"
26+
+" location VARCHAR(50)"
27+
+");"
28+
+"INSERT INTO stores (store_id, store_name, location) VALUES"
29+
+"(1, 'Downtown Tech', 'New York'),"
30+
+"(2, 'Suburb Mall', 'Chicago'),"
31+
+"(3, 'City Center', 'Los Angeles'),"
32+
+"(4, 'Corner Shop', 'Miami'),"
33+
+"(5, 'Plaza Store', 'Seattle');"
34+
+"CREATE TABLE inventory ("
35+
+" inventory_id INT PRIMARY KEY,"
36+
+" store_id INT,"
37+
+" product_name VARCHAR(50),"
38+
+" quantity INT,"
39+
+" price DECIMAL(10,2),"
40+
+" FOREIGN KEY (store_id) REFERENCES stores(store_id)"
41+
+");"
42+
+"INSERT INTO inventory (inventory_id, store_id, "
43+
+"product_name, quantity, price) VALUES"
44+
+"(1, 1, 'Laptop', 5, 999.99),"
45+
+"(2, 1, 'Mouse', 50, 19.99),"
46+
+"(3, 1, 'Keyboard', 25, 79.99),"
47+
+"(4, 1, 'Monitor', 15, 299.99),"
48+
+"(5, 2, 'Phone', 3, 699.99),"
49+
+"(6, 2, 'Charger', 100, 25.99),"
50+
+"(7, 2, 'Case', 75, 15.99),"
51+
+"(8, 2, 'Headphones', 20, 149.99),"
52+
+"(9, 3, 'Tablet', 2, 499.99),"
53+
+"(10, 3, 'Stylus', 80, 29.99),"
54+
+"(11, 3, 'Cover', 60, 39.99),"
55+
+"(12, 4, 'Watch', 10, 299.99),"
56+
+"(13, 4, 'Band', 25, 49.99),"
57+
+"(14, 5, 'Camera', 8, 599.99),"
58+
+"(15, 5, 'Lens', 12, 199.99);")
59+
classMysqlTest {
60+
@Test
61+
voidtestScript(@EmbeddedDatabaseDataSourcedataSource)
62+
throwsSQLException,FileNotFoundException {
63+
try (finalConnectionconnection =dataSource.getConnection()) {
64+
try (finalStatementstatement =connection.createStatement();
65+
finalResultSetresultSet =
66+
statement.executeQuery(
67+
newBufferedReader(
68+
newFileReader(
69+
"src/main/java/g3601_3700/"
70+
+"s3626_find_stores_with_"
71+
+"inventory_imbalance/"
72+
+"script.sql"))
73+
.lines()
74+
.collect(Collectors.joining("\n"))
75+
.replaceAll("#.*?\\r?\\n",""))) {
76+
assertThat(resultSet.next(),equalTo(true));
77+
assertThat(resultSet.getNString(1),equalTo("3"));
78+
assertThat(resultSet.getNString(2),equalTo("City Center"));
79+
assertThat(resultSet.getNString(3),equalTo("Los Angeles"));
80+
assertThat(resultSet.getNString(4),equalTo("Tablet"));
81+
assertThat(resultSet.getNString(5),equalTo("Stylus"));
82+
assertThat(resultSet.getNString(6),equalTo("40.00"));
83+
assertThat(resultSet.next(),equalTo(true));
84+
assertThat(resultSet.getNString(1),equalTo("2"));
85+
assertThat(resultSet.getNString(2),equalTo("Suburb Mall"));
86+
assertThat(resultSet.getNString(3),equalTo("Chicago"));
87+
assertThat(resultSet.getNString(4),equalTo("Phone"));
88+
assertThat(resultSet.getNString(5),equalTo("Case"));
89+
assertThat(resultSet.getNString(6),equalTo("25.00"));
90+
assertThat(resultSet.next(),equalTo(true));
91+
assertThat(resultSet.getNString(1),equalTo("1"));
92+
assertThat(resultSet.getNString(2),equalTo("Downtown Tech"));
93+
assertThat(resultSet.getNString(3),equalTo("New York"));
94+
assertThat(resultSet.getNString(4),equalTo("Laptop"));
95+
assertThat(resultSet.getNString(5),equalTo("Mouse"));
96+
assertThat(resultSet.getNString(6),equalTo("10.00"));
97+
assertThat(resultSet.next(),equalTo(false));
98+
}
99+
}
100+
}
101+
}

0 commit comments

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