Mysql find the book with the highest rating for each country in my db

Question

0.00/5 (No votes)

See more:

I have the following tables:

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?

Posted 24-Feb-22 14:10pm

Member 15439261

Updated 25-Feb-22 0:14am

Add a Solution

Comments

Mohibur Rashid 24-Feb-22 23:23pm

I am not sure if my solution is going to be an overkill but consider windowing function
https://mariadb.com/kb/en/window-functions-overview/

_Asif_ 25-Feb-22 5:15am

it would have been much easier for us to solve this issue if you have provided sample data along with the expected result.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

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.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Answer 1 · 2022-02-25T00:14:00

Try this

SQL

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
from	
(
	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
) T
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

Maciej Los · Answer 2 · 2022-02-24T20:14:00

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[^]

Mysql find the book with the highest rating for each country in my db

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0