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

Commit66493d7

Browse files
authored
Added task 3642
1 parentde16c7d commit66493d7

File tree

5 files changed

+252
-1
lines changed

5 files changed

+252
-1
lines changed

‎gradle/wrapper/gradle-wrapper.jar‎

1.65 KB
Binary file not shown.

‎gradlew‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
#!/bin/sh
22

33
#
4-
# Copyright © 2015-2021 the original authors.
4+
# Copyright © 2015 the original authors.
55
#
66
# Licensed under the Apache License, Version 2.0 (the "License");
77
# you may not use this file except in compliance with the License.
Lines changed: 118 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,118 @@
1+
3642\. Find Books with Polarized Opinions
2+
3+
Easy
4+
5+
Table:`books`
6+
7+
+-------------+---------+
8+
| Column Name | Type |
9+
+-------------+---------+
10+
| book_id | int |
11+
| title | varchar |
12+
| author | varchar |
13+
| genre | varchar |
14+
| pages | int |
15+
+-------------+---------+
16+
book_id is the unique ID for this table. Each row contains information about a book including its genre and page count.
17+
18+
Table:`reading_sessions`
19+
20+
+----------------+---------+
21+
| Column Name | Type |
22+
+----------------+---------+
23+
| session_id | int |
24+
| book_id | int |
25+
| reader_name | varchar |
26+
| pages_read | int |
27+
| session_rating | int |
28+
+----------------+---------+
29+
session_id is the unique ID for this table. Each row represents a reading session where someone read a portion of a book. session_rating is on a scale of 1-5.
30+
31+
Write a solution to find books that have**polarized opinions** - books that receive both very high ratings and very low ratings from different readers.
32+
33+
* A book has polarized opinions if it has`at least one rating ≥ 4` and`at least one rating ≤ 2`
34+
* Only consider books that have**at least**`5`**reading sessions**
35+
* Calculate the**rating spread** as (`highest_rating - lowest_rating`)
36+
* Calculate the**polarization score** as the number of extreme ratings (`ratings ≤ 2 or ≥ 4`) divided by total sessions
37+
***Only include** books where`polarization score ≥ 0.6` (at least`60%` extreme ratings)
38+
39+
Return_the result table ordered by polarization score in**descending** order, then by title in**descending** order_.
40+
41+
The result format is in the following example.
42+
43+
**Example:**
44+
45+
**Input:**
46+
47+
books table:
48+
49+
+---------+------------------------+---------------+----------+-------+
50+
| book_id | title | author | genre | pages |
51+
+---------+------------------------+---------------+----------+-------+
52+
| 1 | The Great Gatsby | F. Scott | Fiction | 180 |
53+
| 2 | To Kill a Mockingbird | Harper Lee | Fiction | 281 |
54+
| 3 | 1984 | George Orwell | Dystopian| 328 |
55+
| 4 | Pride and Prejudice | Jane Austen | Romance | 432 |
56+
| 5 | The Catcher in the Rye | J.D. Salinger | Fiction | 277 |
57+
+---------+------------------------+---------------+----------+-------+
58+
59+
reading\_sessions table:
60+
61+
+------------+---------+-------------+------------+----------------+
62+
| session_id | book_id | reader_name | pages_read | session_rating |
63+
+------------+---------+-------------+------------+----------------+
64+
| 1 | 1 | Alice | 50 | 5 |
65+
| 2 | 1 | Bob | 60 | 1 |
66+
| 3 | 1 | Carol | 40 | 4 |
67+
| 4 | 1 | David | 30 | 2 |
68+
| 5 | 1 | Emma | 45 | 5 |
69+
| 6 | 2 | Frank | 80 | 4 |
70+
| 7 | 2 | Grace | 70 | 4 |
71+
| 8 | 2 | Henry | 90 | 5 |
72+
| 9 | 2 | Ivy | 60 | 4 |
73+
| 10 | 2 | Jack | 75 | 4 |
74+
| 11 | 3 | Kate | 100 | 2 |
75+
| 12 | 3 | Liam | 120 | 1 |
76+
| 13 | 3 | Mia | 80 | 2 |
77+
| 14 | 3 | Noah | 90 | 1 |
78+
| 15 | 3 | Olivia | 110 | 4 |
79+
| 16 | 3 | Paul | 95 | 5 |
80+
| 17 | 4 | Quinn | 150 | 3 |
81+
| 18 | 4 | Ruby | 140 | 3 |
82+
| 19 | 5 | Sam | 80 | 1 |
83+
| 20 | 5 | Tara | 70 | 2 |
84+
+------------+---------+-------------+------------+----------------+
85+
86+
**Output:**
87+
88+
+---------+------------------+---------------+-----------+-------+---------------+--------------------+
89+
| book_id | title | author | genre | pages | rating_spread | polarization_score |
90+
+---------+------------------+---------------+-----------+-------+---------------+--------------------+
91+
| 1 | The Great Gatsby | F. Scott | Fiction | 180 | 4 | 1.00 |
92+
| 3 | 1984 | George Orwell | Dystopian | 328 | 4 | 1.00 |
93+
+---------+------------------+---------------+-----------+-------+---------------+--------------------+
94+
95+
**Explanation:**
96+
97+
***The Great Gatsby (book\_id = 1):**
98+
* Has 5 reading sessions (meets minimum requirement)
99+
* Ratings: 5, 1, 4, 2, 5
100+
* Has ratings ≥ 4: 5, 4, 5 (3 sessions)
101+
* Has ratings ≤ 2: 1, 2 (2 sessions)
102+
* Rating spread: 5 - 1 = 4
103+
* Extreme ratings (≤2 or ≥4): All 5 sessions (5, 1, 4, 2, 5)
104+
* Polarization score: 5/5 = 1.00 (≥ 0.6, qualifies)
105+
***1984 (book\_id = 3):**
106+
* Has 6 reading sessions (meets minimum requirement)
107+
* Ratings: 2, 1, 2, 1, 4, 5
108+
* Has ratings ≥ 4: 4, 5 (2 sessions)
109+
* Has ratings ≤ 2: 2, 1, 2, 1 (4 sessions)
110+
* Rating spread: 5 - 1 = 4
111+
* Extreme ratings (≤2 or ≥4): All 6 sessions (2, 1, 2, 1, 4, 5)
112+
* Polarization score: 6/6 = 1.00 (≥ 0.6, qualifies)
113+
***Books not included:**
114+
* To Kill a Mockingbird (book\_id = 2): All ratings are 4-5, no low ratings (≤2)
115+
* Pride and Prejudice (book\_id = 4): Only 2 sessions (< 5 minimum)
116+
* The Catcher in the Rye (book\_id = 5): Only 2 sessions (< 5 minimum)
117+
118+
The result table is ordered by polarization score in descending order, then by book title in descending order.
Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
# Write your MySQL query statement below
2+
# #Easy #Database #2025_08_10_Time_490_ms_(100.00%)_Space_0.0_MB_(100.00%)
3+
WITH book_statsAS (
4+
SELECT
5+
book_id,
6+
COUNT(*)AS total_sessions,
7+
SUM(CASE WHEN session_rating<>3 THEN1 ELSE0 END)AS extreme_ratings,
8+
MAX(session_rating)AS max_rating,
9+
MIN(session_rating)AS min_rating,
10+
SUM(CASE WHEN session_rating>3 THEN1 ELSE0 END)AS high_ratings,
11+
SUM(CASE WHEN session_rating<=2 THEN1 ELSE0 END)AS low_ratings
12+
FROM reading_sessions
13+
GROUP BY book_id
14+
)
15+
SELECT
16+
bs.book_id,
17+
b.title,
18+
b.author,
19+
b.genre,
20+
b.pages,
21+
(bs.max_rating-bs.min_rating)AS rating_spread,
22+
ROUND(bs.extreme_ratings*1.0/bs.total_sessions,2)AS polarization_score
23+
FROM book_stats bs
24+
JOIN books b USING (book_id)
25+
WHERE
26+
bs.total_sessions>=5
27+
ANDbs.high_ratings>0
28+
ANDbs.low_ratings>0
29+
AND (bs.extreme_ratings*1.0/bs.total_sessions)>=0.6
30+
ORDER BY polarization_scoreDESC,b.titleDESC;
Lines changed: 103 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,103 @@
1+
packageg3601_3700.s3642_find_books_with_polarized_opinions;
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 books ("
24+
+" book_id INT PRIMARY KEY,"
25+
+" title VARCHAR(255),"
26+
+" author VARCHAR(255),"
27+
+" genre VARCHAR(50),"
28+
+" pages INT"
29+
+");"
30+
+"INSERT INTO books (book_id, title, author, genre, pages) VALUES"
31+
+"(1, 'The Great Gatsby', 'F. Scott', 'Fiction', 180),"
32+
+"(2, 'To Kill a Mockingbird', 'Harper Lee', 'Fiction', 281),"
33+
+"(3, '1984', 'George Orwell', 'Dystopian', 328),"
34+
+"(4, 'Pride and Prejudice', 'Jane Austen', 'Romance', 432),"
35+
+"(5, 'The Catcher in the Rye', 'J.D. Salinger', 'Fiction', 277);"
36+
+"CREATE TABLE reading_sessions ("
37+
+" session_id INT PRIMARY KEY,"
38+
+" book_id INT,"
39+
+" reader_name VARCHAR(100),"
40+
+" pages_read INT,"
41+
+" session_rating INT,"
42+
+" FOREIGN KEY (book_id) REFERENCES books(book_id)"
43+
+");"
44+
+"INSERT INTO reading_sessions (session_id, book_id, "
45+
+"reader_name, pages_read, session_rating) VALUES"
46+
+"(1, 1, 'Alice', 50, 5),"
47+
+"(2, 1, 'Bob', 60, 1),"
48+
+"(3, 1, 'Carol', 40, 4),"
49+
+"(4, 1, 'David', 30, 2),"
50+
+"(5, 1, 'Emma', 45, 5),"
51+
+"(6, 2, 'Frank', 80, 4),"
52+
+"(7, 2, 'Grace', 70, 4),"
53+
+"(8, 2, 'Henry', 90, 5),"
54+
+"(9, 2, 'Ivy', 60, 4),"
55+
+"(10, 2, 'Jack', 75, 4),"
56+
+"(11, 3, 'Kate', 100, 2),"
57+
+"(12, 3, 'Liam', 120, 1),"
58+
+"(13, 3, 'Mia', 80, 2),"
59+
+"(14, 3, 'Noah', 90, 1),"
60+
+"(15, 3, 'Olivia', 110, 4),"
61+
+"(16, 3, 'Paul', 95, 5),"
62+
+"(17, 4, 'Quinn', 150, 3),"
63+
+"(18, 4, 'Ruby', 140, 3),"
64+
+"(19, 5, 'Sam', 80, 1),"
65+
+"(20, 5, 'Tara', 70, 2);")
66+
classMysqlTest {
67+
@Test
68+
voidtestScript(@EmbeddedDatabaseDataSourcedataSource)
69+
throwsSQLException,FileNotFoundException {
70+
try (finalConnectionconnection =dataSource.getConnection()) {
71+
try (finalStatementstatement =connection.createStatement();
72+
finalResultSetresultSet =
73+
statement.executeQuery(
74+
newBufferedReader(
75+
newFileReader(
76+
"src/main/java/g3601_3700/"
77+
+"s3642_find_books_with_"
78+
+"polarized_opinions/"
79+
+"script.sql"))
80+
.lines()
81+
.collect(Collectors.joining("\n"))
82+
.replaceAll("#.*?\\r?\\n",""))) {
83+
assertThat(resultSet.next(),equalTo(true));
84+
assertThat(resultSet.getNString(1),equalTo("1"));
85+
assertThat(resultSet.getNString(2),equalTo("The Great Gatsby"));
86+
assertThat(resultSet.getNString(3),equalTo("F. Scott"));
87+
assertThat(resultSet.getNString(4),equalTo("Fiction"));
88+
assertThat(resultSet.getNString(5),equalTo("180"));
89+
assertThat(resultSet.getNString(6),equalTo("4"));
90+
assertThat(resultSet.getNString(7),equalTo("1.00"));
91+
assertThat(resultSet.next(),equalTo(true));
92+
assertThat(resultSet.getNString(1),equalTo("3"));
93+
assertThat(resultSet.getNString(2),equalTo("1984"));
94+
assertThat(resultSet.getNString(3),equalTo("George Orwell"));
95+
assertThat(resultSet.getNString(4),equalTo("Dystopian"));
96+
assertThat(resultSet.getNString(5),equalTo("328"));
97+
assertThat(resultSet.getNString(6),equalTo("4"));
98+
assertThat(resultSet.getNString(7),equalTo("1.00"));
99+
assertThat(resultSet.next(),equalTo(false));
100+
}
101+
}
102+
}
103+
}

0 commit comments

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