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

Commitc7aa0c5

Browse files
authored
Added task 3554
1 parent428d432 commitc7aa0c5

File tree

3 files changed

+236
-0
lines changed

3 files changed

+236
-0
lines changed
Lines changed: 118 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,118 @@
1+
3554\. Find Category Recommendation Pairs
2+
3+
Table:`ProductPurchases`
4+
5+
+-------------+------+
6+
| Column Name | Type |
7+
+-------------+------+
8+
| user_id | int |
9+
| product_id | int |
10+
| quantity | int |
11+
+-------------+------+
12+
(user_id, product_id) is the unique identifier for this table.
13+
Each row represents a purchase of a product by a user in a specific quantity.
14+
15+
Table:`ProductInfo`
16+
17+
+-------------+---------+
18+
| Column Name | Type |
19+
+-------------+---------+
20+
| product_id | int |
21+
| category | varchar |
22+
| price | decimal |
23+
+-------------+---------+
24+
product_id is the unique identifier for this table.
25+
Each row assigns a category and price to a product.
26+
27+
Amazon wants to understand shopping patterns across product categories. Write a solution to:
28+
29+
1. Find all**category pairs** (where`category1` <`category2`)
30+
2. For**each category pair**, determine the number of**unique****customers** who purchased products from**both** categories
31+
32+
A category pair is considered**reportable** if at least`3` different customers have purchased products from both categories.
33+
34+
Return_the result table of reportable category pairs ordered by**customer\_count** in**descending** order, and in case of a tie, by**category1** in**ascending** order lexicographically, and then by**category2** in**ascending** order._
35+
36+
The result format is in the following example.
37+
38+
**Example:**
39+
40+
**Input:**
41+
42+
ProductPurchases table:
43+
44+
+---------+------------+----------+
45+
| user_id | product_id | quantity |
46+
+---------+------------+----------+
47+
| 1 | 101 | 2 |
48+
| 1 | 102 | 1 |
49+
| 1 | 201 | 3 |
50+
| 1 | 301 | 1 |
51+
| 2 | 101 | 1 |
52+
| 2 | 102 | 2 |
53+
| 2 | 103 | 1 |
54+
| 2 | 201 | 5 |
55+
| 3 | 101 | 2 |
56+
| 3 | 103 | 1 |
57+
| 3 | 301 | 4 |
58+
| 3 | 401 | 2 |
59+
| 4 | 101 | 1 |
60+
| 4 | 201 | 3 |
61+
| 4 | 301 | 1 |
62+
| 4 | 401 | 2 |
63+
| 5 | 102 | 2 |
64+
| 5 | 103 | 1 |
65+
| 5 | 201 | 2 |
66+
| 5 | 202 | 3 |
67+
+---------+------------+----------+
68+
69+
ProductInfo table:
70+
71+
+------------+-------------+-------+
72+
| product_id | category | price |
73+
+------------+-------------+-------+
74+
| 101 | Electronics | 100 |
75+
| 102 | Books | 20 |
76+
| 103 | Books | 35 |
77+
| 201 | Clothing | 45 |
78+
| 202 | Clothing | 60 |
79+
| 301 | Sports | 75 |
80+
| 401 | Kitchen | 50 |
81+
+------------+-------------+-------+
82+
83+
**Output:**
84+
85+
+-------------+-------------+----------------+
86+
| category1 | category2 | customer_count |
87+
+-------------+-------------+----------------+
88+
| Books | Clothing | 3 |
89+
| Books | Electronics | 3 |
90+
| Clothing | Electronics | 3 |
91+
| Electronics | Sports | 3 |
92+
+-------------+-------------+----------------+
93+
94+
**Explanation:**
95+
96+
***Books-Clothing**:
97+
* User 1 purchased products from Books (102) and Clothing (201)
98+
* User 2 purchased products from Books (102, 103) and Clothing (201)
99+
* User 5 purchased products from Books (102, 103) and Clothing (201, 202)
100+
* Total: 3 customers purchased from both categories
101+
***Books-Electronics**:
102+
* User 1 purchased products from Books (102) and Electronics (101)
103+
* User 2 purchased products from Books (102, 103) and Electronics (101)
104+
* User 3 purchased products from Books (103) and Electronics (101)
105+
* Total: 3 customers purchased from both categories
106+
***Clothing-Electronics**:
107+
* User 1 purchased products from Clothing (201) and Electronics (101)
108+
* User 2 purchased products from Clothing (201) and Electronics (101)
109+
* User 4 purchased products from Clothing (201) and Electronics (101)
110+
* Total: 3 customers purchased from both categories
111+
***Electronics-Sports**:
112+
* User 1 purchased products from Electronics (101) and Sports (301)
113+
* User 3 purchased products from Electronics (101) and Sports (301)
114+
* User 4 purchased products from Electronics (101) and Sports (301)
115+
* Total: 3 customers purchased from both categories
116+
* Other category pairs like Clothing-Sports (only 2 customers: Users 1 and 4) and Books-Kitchen (only 1 customer: User 3) have fewer than 3 shared customers and are not included in the result.
117+
118+
The result is ordered by customer\_count in descending order. Since all pairs have the same customer\_count of 3, they are ordered by category1 (then category2) in ascending order.
Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,25 @@
1+
# Write your MySQL query statement below
2+
# #Hard #Database #2025_05_22_Time_623_ms_(82.76%)_Space_0.0_MB_(100.00%)
3+
SELECT
4+
pi1.categoryAS category1,
5+
pi2.categoryAS category2,
6+
COUNT(DISTINCTpp1.user_id)AS customer_count
7+
FROM
8+
ProductPurchases pp1,
9+
ProductPurchases pp2,
10+
ProductInfo pi1,
11+
ProductInfo pi2
12+
WHERE
13+
pp1.user_id=pp2.user_id
14+
ANDpi1.category<pi2.category
15+
ANDpp1.product_id=pi1.product_id
16+
ANDpp2.product_id=pi2.product_id
17+
GROUP BY
18+
pi1.category,
19+
pi2.category
20+
HAVING
21+
COUNT(DISTINCTpp1.user_id)>=3
22+
ORDER BY
23+
customer_countDESC,
24+
category1ASC,
25+
category2ASC;
Lines changed: 93 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,93 @@
1+
packageg3501_3600.s3554_find_category_recommendation_pairs;
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 ProductPurchases(user_id INTEGER, product_id INTEGER"
24+
+", quantity INTEGER); "
25+
+"INSERT INTO ProductPurchases(user_id, product_id, quantity)"
26+
+" VALUES "
27+
+"(1, 101, 2), "
28+
+"(1, 102, 1), "
29+
+"(1, 201, 3), "
30+
+"(1, 301, 1), "
31+
+"(2, 101, 1), "
32+
+"(2, 102, 2), "
33+
+"(2, 103, 1), "
34+
+"(2, 201, 5), "
35+
+"(3, 101, 2), "
36+
+"(3, 103, 1), "
37+
+"(3, 301, 4), "
38+
+"(3, 401, 2), "
39+
+"(4, 101, 1), "
40+
+"(4, 201, 3), "
41+
+"(4, 301, 1), "
42+
+"(4, 401, 2), "
43+
+"(5, 102, 2), "
44+
+"(5, 103, 1), "
45+
+"(5, 201, 2), "
46+
+"(5, 202, 3);"
47+
+"CREATE TABLE ProductInfo(product_id INTEGER, category VARCHAR(255)"
48+
+", price INTEGER); "
49+
+"INSERT INTO ProductInfo(product_id, category, price) VALUES "
50+
+"(101, 'Electronics', 100), "
51+
+"(102, 'Books', 20), "
52+
+"(103, 'Books', 35), "
53+
+"(201, 'Clothing', 45), "
54+
+"(202, 'Clothing', 60), "
55+
+"(301, 'Sports', 75), "
56+
+"(401, 'Kitchen', 50);")
57+
classMysqlTest {
58+
@Test
59+
voidtestScript(@EmbeddedDatabaseDataSourcedataSource)
60+
throwsSQLException,FileNotFoundException {
61+
try (finalConnectionconnection =dataSource.getConnection()) {
62+
try (finalStatementstatement =connection.createStatement();
63+
finalResultSetresultSet =
64+
statement.executeQuery(
65+
newBufferedReader(
66+
newFileReader(
67+
"src/main/java/g3501_3600/"
68+
+"s3554_find_category_recommendation_pairs/"
69+
+"script.sql"))
70+
.lines()
71+
.collect(Collectors.joining("\n"))
72+
.replaceAll("#.*?\\r?\\n",""))) {
73+
assertThat(resultSet.next(),equalTo(true));
74+
assertThat(resultSet.getNString(1),equalTo("Books"));
75+
assertThat(resultSet.getNString(2),equalTo("Clothing"));
76+
assertThat(resultSet.getNString(3),equalTo("3"));
77+
assertThat(resultSet.next(),equalTo(true));
78+
assertThat(resultSet.getNString(1),equalTo("Books"));
79+
assertThat(resultSet.getNString(2),equalTo("Electronics"));
80+
assertThat(resultSet.getNString(3),equalTo("3"));
81+
assertThat(resultSet.next(),equalTo(true));
82+
assertThat(resultSet.getNString(1),equalTo("Clothing"));
83+
assertThat(resultSet.getNString(2),equalTo("Electronics"));
84+
assertThat(resultSet.getNString(3),equalTo("3"));
85+
assertThat(resultSet.next(),equalTo(true));
86+
assertThat(resultSet.getNString(1),equalTo("Electronics"));
87+
assertThat(resultSet.getNString(2),equalTo("Sports"));
88+
assertThat(resultSet.getNString(3),equalTo("3"));
89+
assertThat(resultSet.next(),equalTo(false));
90+
}
91+
}
92+
}
93+
}

0 commit comments

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