Movie Rating SQL Analysis Project, PostgreSQL

Language: Layout:

-- 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 ;


absolute service time: 0,42 sec