imdb_movie_analysis.rtf

{\rtf1\ansi\ansicpg1252\deff0\nouicompat\deflang1033{\fonttbl{\f0\fnil\fcharset0 Calibri;}}
{\*\generator Riched20 10.0.19041}\viewkind4\uc1 
\pard\sl240\slmult1\b\f0\fs22\lang9 ---q1 Find the total number of movies released each year? How does the trend look month wise?\par
\b0 select year,count(title) as movie_count\par
from movie\par
group by movie\par
\par
select month(date_published) as month_num\par
count(title) as num_0f_movies\par
from movie\par
group by month(date_published)\par
order by count(title) desc;\par
\par

\pard\sl240\slmult1\qj\b ---q2How many movies were produced in the USA or India in the year 2019??\par
\b0 select year,country,count(id)\par
from movie\par
where year='2019' and country="USA" or "India"\par
group by year\par

\pard\sl240\slmult1\par
\par
\b ----- Q3.Which genre had the highest number of movies produced overall?\par
\b0 select g.genre,count(m.title)\par
from movie m\par
inner join genre g\par
on m.id=g.movie_id\par
group by g.genre\par
order by count(m.title) desc limit 1;\par
\par
\par
\b --q4 Which genre had the highest number of movies produced overall?\par
\b0\par
select count(m.title),g.genre\par
from movie m \par
inner join genre g \par
on m.id=g.movie_id \par
group by g.genre\par
order by count(m.title) desc \par
limit 1\par
\par
\par
\b ---q5How many movies belong to only one genre?\par
\b0\par
with agg\par
as (select m.id,count(g.genre)\par
from movie m \par
inner join genre g \par
on m.id=g.movie_id \par
group by m.id\par
having count(g.genre) =1)\par
\par
select count(id) as movie_count\par
from agg\par
\par
\par
\par
\b ---q6 What is the average duration of movies in each genre?\par
\b0\par
select m.id,avg(m.duration),g.genre\par
from movie m \par
inner join genre g \par
on m.id=g.movie_id \par
group by g.genre\par
\par
\b ---q7 What is the rank of the \lquote thriller\rquote  genre of movies among all the genres in terms of number of \b0 movies produced?\par
\par
with cte\par
as (select genre, count(movie_id) as movie_count\par
    rank()\par
\tab over(order by count(movie_id) desc)\par
\tab from genre\par
\tab group by genre)\par
\tab\par
select * from cte\par
where genre = 'thriller'\par
\par
\par
\b ---q8 Which are the top 10 movies based on average rating?\par
\b0\par
select m.movie_title,r.average_rating\par
rank() over(order by r.average_rating) as movie_rank\par
from movie m \par
inner join ratings r\par
on m.id=r.movie_id\par
order by r.average_rating desc\par
limit 10\par
\par
\par
\b ---q9 Which production house has produced the most number of hit movies (average rating > 8)??\par
\b0\par
with cte\par
as(select m.production_company,m.id,r.average_rating\par
from movie m \par
inner join ratings r\par
on m.id=r.movie_id\par
group by m.production_company\par
order by r.average_rating desc)\par
\par
\par
select production_company, count(id) as movie_count\par
rank() ovder(order by count(id) desc)\par
from cte\par
group by production_company\par
order by count(id) desc\par
limit 2 \par
\par
\par
\b ---q10 How many movies released in each genre during March 2017 in the USA had more than 1,000 votes?\b0\par
\par
with cte\par
as(select m.year,month(date_published),m.country, g.genre,count(m.title),r.total_votes\par
from movie m\par
inner join genre g\par
on m.id=g.movie_id\par
inner join ratings r\par
on m.id=r.movie_id\par
group by g.genre\par
where m.year= "2017" and month(date_published)= "march" and country = "usa"\par
having r.total_votes >1000)\par
\par
select genre,count(id)\par
from cte\par
group by genre\par

\pard\sl240\slmult1\qj order by count(id) desc\par
}