CREATE TABLE `country` (
`name` VARCHAR(
60
) NOT NULL,
`code` VARCHAR(
3
) UNIQUE NOT NULL,
PRIMARY KEY (`code`)
CREATE TABLE `user` (
`userId` INT UNIQUE NOT NULL AUTO_INCREMENT,
`country` VARCHAR(
3
) NOT NULL,
`age` INT NOT NULL,
PRIMARY KEY (`userId`),
CONSTRAINT `fk_user_country` FOREIGN KEY (`country`) REFERENCES `country`(`code`)
CREATE TABLE `bookRating` (
`userId` INT NOT NULL,
`isbn` VARCHAR(
13
) NOT NULL,
`rate` INT NOT NULL,
`date` DATE NOT NULL,
CONSTRAINT `fk_bookRating_user` FOREIGN KEY (`userId`) REFERENCES `user`(`userId`),
CONSTRAINT `fk_bookRating_book` FOREIGN KEY (`isbn`) REFERENCES `book`(`isbn`)
CREATE TABLE `book` (
`isbn` varchar(
13
) UNIQUE NOT NULL,
`bookTitle` VARCHAR(
280
),
`bookAuthor` VARCHAR(
150
),
`yearPublication`
int
(
4
),
-- `yearPublication` must be an integer because we have
value
less that
1901
in
dataset
`publisher` VARCHAR(
135
),
PRIMARY KEY (`isbn`),
CONSTRAINT `publication_yea_chk` check ((`yearPublication` > -1) && (`yearPublication` <
2101
))
As I am saying on the title I want to find the book with the highest average rating, For each country
What I have tried:
I have tried this query:
SELECT `country`.`name`,`bookRating`.`isbn` , AVG(`bookRating`.`rate`) FROM `country`
INNER JOIN `user` ON `country`.`code` = `user`.`country`
INNER JOIN `bookRating` ON `user`.`userId` = `bookRating`.`userId`
WHERE bookRating.isbn IN (
SELECT `bookrating`.`isbn`
from
`bookrating`
GROUP BY `bookrating`.`isbn`
GROUP BY `country`.`name`
ORDER BY AVG(`bookRating`.`rate`) DESC;
But I know it is not correct. How could I do it?
Am I trying this wrong from the start?
Take a look here:
MySQL :: MySQL 8.0 Reference Manual :: 12.21.1 Window Function Descriptions
[
^
]
MySql has already embedded ranking functions:
CUME_DIST()
Cumulative distribution value
DENSE_RANK()
Rank of current row within its partition, without gaps
NTH_VALUE()
Value of argument from N-th row of window frame
NTILE()
Bucket number of current row within its partition.
PERCENT_RANK()
Percentage rank value
RANK()
Rank of current row within its partition, with gaps
ROW_NUMBER()
Number of current row within its partition
Use them!
A Guide to MySQL RANK Funtion By Practical Examples
[
^
]
MySQL | Ranking Functions - GeeksforGeeks
[
^
]
DECLARE
@country
TABLE
(
name
VARCHAR
(
60
)
NOT
NULL
,
code
VARCHAR
(
3
)
UNIQUE
NOT
NULL
DECLARE
@User
TABLE
(
userId
INT
UNIQUE
NOT
NULL
IDENTITY
,
country
VARCHAR
(
3
)
NOT
NULL
,
age
INT
NOT
NULL
DECLARE
@book
TABLE
(
isbn
varchar
(
20
)
UNIQUE
NOT
NULL
,
bookTitle
VARCHAR
(
280
),
bookAuthor
VARCHAR
(
150
),
yearPublication
int
,
publisher
VARCHAR
(
135
)
DECLARE
@bookRating
TABLE
(
userId
INT
NOT
NULL
,
isbn
VARCHAR
(
20
)
NOT
NULL
,
rate
INT
NOT
NULL
,
date
DATE
NOT
NULL
INSERT
INTO
@country
(name, code)
SELECT
'
United States'
,
'
US'
UNION
ALL
SELECT
'
United Kingdom'
,
'
UK'
UNION
ALL
SELECT
'
Pakistan'
,
'
PK'
UNION
ALL
SELECT
'
China'
,
'
CH'
;
INSERT
INTO
@USER
(country, age)
SELECT
'
US'
,
20
UNION
ALL
SELECT
'
UK'
,
21
UNION
ALL
SELECT
'
CH'
,
22
UNION
ALL
SELECT
'
PK'
,
23
UNION
ALL
SELECT
'
US'
,
27
INSERT
INTO
@book
(isbn, bookTitle, bookAuthor, yearPublication, publisher)
SELECT
'
978-1-933624-00-1'
,
'
Book Title 1'
,
'
Book Author 1'
,
2000
,
'
Publisher1'
UNION
ALL
SELECT
'
978-1-933624-00-2'
,
'
Book Title 2'
,
'
Book Author 2'
,
2001
,
'
Publisher2'
UNION
ALL
SELECT
'
978-1-933624-00-3'
,
'
Book Title 3'
,
'
Book Author 3'
,
2002
,
'
Publisher3'
UNION
ALL
SELECT
'
978-1-933624-00-4'
,
'
Book Title 4'
,
'
Book Author 4'
,
2003
,
'
Publisher4'
UNION
ALL
SELECT
'
978-1-933624-00-5'
,
'
Book Title 5'
,
'
Book Author 5'
,
2005
,
'
Publisher5'
UNION
ALL
SELECT
'
978-1-933624-00-6'
,
'
Book Title 6'
,
'
Book Author 6'
,
2006
,
'
Publisher6'
UNION
ALL
SELECT
'
978-1-933624-00-7'
,
'
Book Title 7'
,
'
Book Author 7'
,
2007
,
'
Publisher7'
UNION
ALL
SELECT
'
978-1-933624-00-8'
,
'
Book Title 8'
,
'
Book Author 8'
,
2008
,
'
Publisher8'
INSERT
INTO
@bookRating
SELECT
1
,
'
978-1-933624-00-1'
,
2
,
'
01-Jan-2000'
UNION
ALL
SELECT
5
,
'
978-1-933624-00-1'
,
3
,
'
01-Jan-2000'
UNION
ALL
SELECT
3
,
'
978-1-933624-00-2'
,
4
,
'
01-Jan-2001'
UNION
ALL
SELECT
3
,
'
978-1-933624-00-2'
,
2
,
'
01-Jan-2001'
UNION
ALL
SELECT
4
,
'
978-1-933624-00-3'
,
2
,
'
01-Jan-2002'
UNION
ALL
SELECT
4
,
'
978-1-933624-00-3'
,
5
,
'
01-Jan-2002'
select
T1.name, T1.BookTitle, Rate
select
C.Code, MAX(BR.rate) MAXRating
from
@bookRating
BR
inner
join
@book
B
on
BR.isbn = B.isbn
INNER
JOIN
@USER
U
ON
BR.UserID = U.USERId
INNER
JOIN
@COUNTRY
C
ON
U.country = C.Code
group
by
C.Code
inner
join
select
C.Code, C.name, B.BookTitle, BR.ISBN, BR.rate
from
@bookRating
BR
inner
join
@book
B
on
BR.isbn = B.isbn
INNER
JOIN
@USER
U
ON
BR.UserID = U.USERId
INNER
JOIN
@COUNTRY
C
ON
U.country = C.Code
) T1
ON
T.Code = T1.Code
AND
T.MAXRATING = T1.RATE
Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad
spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or
edit the question
and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it.
Provide an answer or move on to the next question.
Let's work to help developers, not make them feel stupid.