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 , -- yearPublication must be an integer because we have value less that 1901 in dataset 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.