i have solved 99% challenges in sql in skillrack.com which is a site for practicing coding for placements, this is the last one, I cant even understand the question itself, I think there is a conceptual error in question, If you have time, you can read the question and help me out, thank you reading this.
A table called movie is created with the following DDL command.
The Table Schema
create table movie(
id int,
name varchar(15),
releaseyear int,
budget int
);
Write the SQL query to fetch the id, name, release year and budget of the highest budget movies in each year.
Note: The movies must be sorted in descending order based on the budget. If two or more movies have the same budget, then those movies must be sorted in descending order based on the release year. If two or more movies have the same budget and release year, then those movies must be sorted in descending order based on their ids.
The data in Database
501 Movie1 2018 850000000
502 Movie2 2018 800000000
503 Movie3 2018 850000000
504 Movie4 2018 750000000
505 Movie5 2019 850000000
506 Movie6 2019 950000000
507 Movie7 2019 900000000
508 Movie8 2019 500000000
509 Movie9 2019 850000000
510 Movie10 2020 800000000
511 Movie11 2020 700000000
512 Movie12 2020 600000000
513 Movie13 2020 990000000
514 Movie14 2017 920000000
515 Movie15 2017 910000000
516 Movie16 2021 990000000
Required Output
516 Movie16 2021 990000000
513 Movie13 2020 990000000
506 Movie6 2019 950000000
514 Movie14 2017 920000000
509 Movie9 2019 850000000
505 Movie5 2019 850000000
503 Movie3 2018 850000000
501 Movie1 2018 850000000
I dont know why the movie id with 509 and 505 has been in required output,since the year 2019 has the maximum budget as 950000000, If you find any logic to clarify, I highly appreciate it.
2
Answers
Assuming you posted the integral and correct text for the question the required output is wrong.
The required output should look like this:
I don’t know Skillrack but usually similar websites implement a reporting system for wrong answers or ambiguous question to be examined by moderators.
I suggest you read again the question and confirm that the request is in fact correct as you posted it, if it is report the question to be reviewed.
(This is not an answer, since I agree with you that the question doesn’t make sense)
I would suggest you to solve these, if you can than you are ok:
Query1: List all the movies, sorting them as in the question.
Expected output:
Query2: List only the movies with the highest budget per year, sorting them as in the question.
Expected output:
Please try solving on your own first and then check the solution (there are other ways to solve it, just one them even applicable to very old versions of any SQL series database):
DBfiddle solution