Movie Rating SQL Analysis Project, PostgreSQL

Movie Rating SQL Analysis Project

-- Movie Rating SQL Analysis Conducted by Ziying Yan

-----------------------------------------------------------------------
-- database Script

CREATE TABLE Movie
    ("mid" int, "title" varchar(25),"year" int, "director" varchar(25))
;
    
INSERT INTO Movie
    ("mid", "title", "year", "director")
VALUES
    (101, 'Gone with the Wind', 1939, 'Victor Fleming'),
    (102, 'Star Wars', 1977, 'George Lucas'),
    (103, 'The Sound of Music', 1965, 'Robert Wise'),
    (104, 'E.T.', 1982, 'Steven Spielberg'),
    (105, 'Titanic', 1997, 'James Cameron'),
    (106, 'Snow White', 1937, 'NULL'),
    (107, 'Avatar', 2009, 'James Cameron'),
    (108, 'Raiders of the Lost Ark', 1981, 'Steven Spielberg');

CREATE TABLE Reviewer
    ("rid" int, "name" varchar(25))
;
    
INSERT INTO Reviewer
    ("rid", "name")
VALUES
    (201, 'Sarah Martinez'),
    (202, 'Daniel Lewis'),
    (203, 'Brittany Harris'),
    (204, 'Mike Anderson'),
    (205, 'Chris Jackson'),
    (206, 'Elizabeth Thomas'),
    (207, 'James Cameron'),
    (208, 'Ashley White');

CREATE TABLE Ratings
    ("rid" int, "mid" int,  "stars" int,"ratingdate" timestamp)
;
    
INSERT INTO Ratings
    ("rid" , "mid" ,  "stars", "ratingdate")
    
VALUES
    (201, 101, 2, '2011-01-22'),
    (201, 101, 4, '2011-01-27'),
    (202, 106, 4,  NULL),
    (203, 103, 2, '2011-01-20'),
    (203, 108, 4, '2011-01-12'),
    (203, 108, 2, '2011-01-30'),
    (204, 101, 3, '2011-01-09'),
    (205, 103, 3, '2011-01-27'),  
    (205, 104, 2, '2011-01-22'),  
    (205, 108, 4,  NULL),  
    (206, 107, 3, '2011-01-15'),    
    (206, 106, 5, '2011-01-19'),    
    (207, 107, 5, '2011-01-20'),
    (208, 104, 3, '2011-01-02');

-----------------------------------------------------------------------
--Insight Script:

--#0: view table:
select * from Movie limit 5;

select * from Reviewer limit 5;

select * from Ratings limit 5;

--#Q1: Find the difference between the avarge rating of movies released before 1980 and the average rating of movies released after 1980. Return the single value.
-- average -> the average rating for each movie -> the average rating of movies released before and after 1980
-- output: average of rating of movies before 1980 - average of rating of movies after 1980
-- input: mid, star, year
-- steps: seperate movies by year; get the average for each movie; get the average of movies

with cte1 as (
    select avg(before_avg) as before_all_avg
    from (
        select tb1.mid, avg(stars) as before_avg
        from movie tb1
        join ratings tb2
        on tb1.mid = tb2.mid
        where year <= 1980
        group by 1) as temp),
cte2 as (
    select avg(after_avg) as after_all_avg
    from (
        select tb1.mid, avg(stars) as after_avg
        from movie tb1
        join ratings tb2
        on tb1.mid = tb2.mid
        where year > 1980
        group by 1
        )as temp)
        
select abs(before_all_avg - after_all_avg) as difference
from cte1, cte2 ;

--# Q2: Find the titles of all movies directed by Steven Spielberg
select distinct title 
from movie 
where director = 'Steven Spielberg';

--# Q3: Find all years that have a movie that received a rating of 4 or 5, and sort them in increasing order
-- output: year
-- input: year, stars between 4 and 5
-- steps: find the movies - find all years

select year
from movie
where mid in (
  select distinct mid
  from ratings
  where stars between 4 and 5)
order by 1 asc;


run \| edit \| history \| help	0