Problem Statement

The problem statement is:

Print the genre and the maximum net profit among all the movies of that genre released in 2012 per genre.

a database is given.

Conditions

1. Do not print any row where either the genre or the net profit is empty/null.

2. net_profit = Domestic + Worldwide - Budget

3. Keep the names of the columns

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

--column to group by

SELECT  CAST(genre.genre as varchar(max)) as Genre,

-- column to be grouped.

MAX(     

    CAST(( CAST(earning.Domestic as varchar(max)) ) as bigint)  
        + CAST(( CAST(earning.Worldwide as varchar(max)) ) as bigint)  
            - CAST(( CAST(IMDB.Budget as varchar(max)) ) as bigint)        ) as net_profit

FROM IMDB

-- 3 tables join by left outer

LEFT OUTER JOIN genre
ON CAST(IMDB.Movie_id AS varchar(max)) = CAST(genre.Movie_id AS varchar(max))
LEFT OUTER JOIN earning
ON CAST(IMDB.Movie_id AS varchar(max)) = CAST(earning.Movie_id AS varchar(max))

-- not null values in genre and net_profit and released in 2012


WHERE CAST(genre.genre as varchar(max)) IS NOT NULL AND CAST(( CAST(earning.Domestic as varchar(max)) ) as bigint)  
        + CAST(( CAST(earning.Worldwide as varchar(max)) ) as bigint)  
            - CAST(( CAST(IMDB.Budget as varchar(max)) ) as bigint)  IS NOT NULL   AND 

            CONVERT(INT, left(  RIGHT (CAST( IMDB.Title as VARCHAR(Max)) ,5),4) ) = 2012

-- group by gebre.

GROUP BY CAST(genre.genre as varchar(max))

ORDER BY CAST(genre.genre as varchar(max))

Results

GitHub link

SQLQuery_4.2