Problem Statement
The problem statement is:
You are given a list of movies and their ratings from IMDb, a popular movie database website.
You need to write a program that Picks movies with
Movies released in 2012.
The Metacritic rating of the movies should be more than 60.
The domestic collections of the movies should exceed 10 crores.
Using SQL
https://github.com/virajmilinda/sql_problems.git
Dataset
CodingNinja offers us the .sql file to download. it has 3 tables.
IMDB
select *
from IMDB
Genre
SELECT *
from genre
Earning
SELECT *
from earning
Tools
CodingNinja has an in-built coding environment, but I used Azure Data Studio.
Azure Data Studio | Microsoft Azure
Code
- The Metacritic rating of the movies should be more than 60.
Every movie has a Metacritic rating.
/* Condition 1*/
SELECT *
from IMDB
where MetaCritic > 60
ORDER BY CAST(IMDB.Movie_id as VARCHAR(Max)) ASC
for this purpose, MetaCritic > 60 condition is enough.
- The domestic collections of the movies should exceed 100 million.
earnings Table
/* Condition 2*/
SELECT *
from earning
where Domestic > 100000000
ORDER BY CAST(earning.Movie_id as VARCHAR(Max)) ASC
- Movies released in 2012.
I have solved this already in the Problem 1 of the series.
The title column has released year at the end of each title. It needs to be sliced and trimmed to extract the released year.
Then the movies released in 2012 need to be filtered.
Here's the code.
/* Condition 4*/
select Movie_id,
CONVERT(INT, left( RIGHT (CAST( Title as VARCHAR(Max)) ,5),4) ) as year
from IMDB
WHERE
CONVERT(INT, left( RIGHT (CAST( IMDB.Title as VARCHAR(Max)) ,5),4) ) = 2014
Here's the result.
Final results table
I used inner join to combine these condition.
/* Inner Join*/
/* Condition 1*/
SELECT DISTINCT CAST(Movie_id1 AS nvarchar(max)) as Movie_id4 ,
CAST(Title AS nvarchar(max)) as Title
FROM (
SELECT Movie_id as Movie_id1, Title,
CONVERT(INT, left( RIGHT (CAST( Title as VARCHAR(Max)) ,5),4) ) as year
from IMDB
WHERE
CONVERT(INT, left( RIGHT (CAST( IMDB.Title as VARCHAR(Max)) ,5),4) ) = 2012
) as table1
INNER JOIN (
/* Condition 2*/
SELECT CAST(Movie_id AS nvarchar(max)) as Movie_id2, MetaCritic
from IMDB
where MetaCritic > 60
) as table2 ON table1.Movie_id1 = table2.Movie_id2
INNER JOIN (
/* Condition 3*/
SELECT CAST(Movie_id AS nvarchar(max)) as Movie_id3, Domestic
from earning
where Domestic > 100000000
) as table3 ON table2.Movie_id2 = table3.Movie_id3
Here's the result.
