SQL Practice Sample: Movie Database, PostgreSQL

Language: Layout:

drop table if exists clintonng_movie;
drop table if exists clintonng_reviewer;
drop table if exists clintonng_rating;

create table clintonng_movie("mid" int, "title" text, "year" int, "director" text);
create table clintonng_reviewer("rid" int, "name" text);
create table clintonng_rating1("rid" int, "mid" int, "stars" int, "ratingdate" date);

insert into clintonng_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');

insert into clintonng_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');

insert into clintonng_rating1("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');

SELECT *
FROM clintonng_movie;

SELECT *
FROM clintonng_reviewer;

SELECT *
FROM clintonng_rating1;

/*Q1 Find the titles of all movies directed by Steven Spielberg*/
SELECT title
FROM clintonng_movie
Where director in ('Steven Spielberg');

/*Q2 Find all year of ratings for movies which received a rating of 4 or 5, and sort them in increasing order*/

SELECT a.year, b.stars
FROM clintonng_movie a
LEFT JOIN clintonng_rating1 b
ON a.mid = b.mid
WHERE b.stars in (4, 5);

SELECT extract(year from ratingdate) as year, stars
FROM clintonng_rating1
Where stars in (4,5) and ratingdate is not null;

/*Q3 Find the titles of all movies that have no ratings*/

SELECT a.title
FROM clintonng_movie a
LEFT JOIN clintonng_rating1 b
ON a.mid = b.mid
WHERE b.mid is null;

/*Q4 Some reviewrs didn't provide a date with their rating. Find the names of all reviwers who have a Null value for the date */

SELECT distinct a.name
FROM clintonng_reviewer a
LEFT JOIN clintonng_rating1 b
ON a.rid = b.rid
WHERE b.ratingdate is null;

/*OR*/

SELECT distinct name
FROM clintonng_reviewer
WHERE rid in (SELECT rid FROM clintonng_rating1 WHERE ratingdate is null);

/*Q5 For all cases where the same reviewer rated the same movie twice only and gave it a higher rating
the second time, return the reviewer's name and the title of the movie.*/

/*Reviewer Reviewing the same movie only twice*/
WITH twice_rating_users AS(
    SELECT rid, mid
    FROM(
    SELECT rid, mid, COUNT(*) as num
    FROM clintonng_rating1
    GROUP BY 1,2
    HAVING COUNT(*) = 2
        ) as cte),

/*SELF join rating on condition of higher rating second time*/

query_result AS(
SELECT r1.rid, r2.mid
FROM clintonng_rating1 r1 JOIN clintonng_rating1 r2
ON r1.rid=r2.rid and r1.mid = r2.mid
and r1.ratingdate < r2.ratingdate
and r1.stars < r2.stars

/*Filter By higher rating second time THAT reviewer only rated twice*/

WHERE (r1.rid, r1.mid) in (SELECT rid, mid FROM twice_rating_users)
    )

/*At the end join back the query table to movie and reviewer table to get the results*/

SELECT name, title
FROM clintonng_reviewer a
JOIN query_result b ON a.rid = b.rid
JOIN clintonng_movie c ON b.mid = c.mid;

/*Q6 For each movie that has at least one rating, find the highest number of stars that movie received.
Return the movie title and number of stars. Sort by movie title*/

/*First Find the movie(mid)that does not have movie rating. Then filter out movies in that category. Then find (mid)
with highest stars received max(stars). Then join movie id to get the movie title*/

/*Solution 1*/
SELECT a.title, max(stars)
FROM clintonng_movie a
JOIN clintonng_rating1 b
ON a.mid = b.mid
WHERE b.stars IS NOT NULL
GROUP BY 1
ORDER BY 1;

/*My Solution (Warning: Long!)*/

WITH movie_no_rating AS (
SELECT a.mid
FROM clintonng_movie a
LEFT JOIN clintonng_rating1 b
ON a.mid = b.mid
WHERE b.mid IS NULL),

query_result AS (
SELECT mid, MAX(stars) as max_stars
FROM clintonng_rating1
WHERE mid NOT IN (SELECT mid FROM movie_no_rating)
GROUP BY 1)

SELECT a.title, b.max_stars
FROM clintonng_movie a
JOIN query_result b
ON a.mid = b.mid
ORDER BY title;

/*Q7. For each movie, return the title and the 'rating spread', that is, the difference
between highest and lowest ratings given to that movie. Sort by rating spread from
Highest to lowest, then by movie title.*/

/*Solution*/
SELECT a.title, (max(b.stars) - min(b.stars)) as rating_spread
FROM clintonng_movie a
JOIN clintonng_rating1 b
ON a.mid = b.mid
GROUP BY 1
ORDER BY 2 DESC, 1;

/*My Solution (Warning: Long!). First get the table with title, min and max stars*/
SELECT title, (highest_star - lowest_star) as rating_spread
FROM (
SELECT a.title, MAX(b.stars) as highest_star, MIN(b.stars) as lowest_star
FROM clintonng_movie a
JOIN clintonng_rating1 b
ON a.mid = b.mid
GROUP BY 1) as cte1
ORDER BY 2 DESC, 1;

/*Q8 Find the difference between the average rating of movies released before 1980
and the average rating movies released after 1980. (Make sure to calculate the average
rating for each movie, then the average of those averages for movies before 1980
and movies after. Don't just calculate the overall average rating before and after
1980.). Return single value*/

/*Solution*/
SELECT (d.avg_rating - f.avg_rating) as rate_diff

FROM (
    SELECT avg(c.avgs)as avg_rating
    FROM
        (
            SELECT a.mid, avg(a.stars) as avgs
            FROM clintonng_rating1 a join clintonng_movie m on a.mid = m.mid
            WHERE m.year < 1980
            GROUP by 1
            ) c
    ) d, /*Cross Join*/
    (SELECT avg(e.avgs) as avg_rating
    FROM
     (SELECT a.mid, avg(a.stars) as avgs
      FROM clintonng_rating1 a join clintonng_movie m on a.mid=m.mid
      WHERE m.year > 1980
      GROUP by 1
      ) e
     ) f;

/*Solution1*/
SELECT AVG(pre_1980.avg_rating) - AVG(post_1980.avg_rating)

FROM
(SELECT a.mid, AVG(b.stars) as avg_rating
FROM clintonng_movie a
JOIN clintonng_rating1 b
ON a.mid = b.mid
WHERE a.year < 1980
GROUP BY 1) as pre_1980,

(SELECT a.mid, AVG(b.stars) as avg_rating
FROM clintonng_movie a
JOIN clintonng_rating1 b
ON a.mid = b.mid
WHERE a.year > 1980
GROUP BY 1) as post_1980;

/*My Solution2: First find average rating of each movies pre_1980 and post_1980. Then use cross
join to minus both value*/

WITH pre_1980 AS(
SELECT a.mid, a.title, a.year, AVG(b.stars) as average_rating
FROM clintonng_movie a
JOIN clintonng_rating1 b
ON a.mid = b.mid
WHERE a.year < 1980
GROUP BY 1, 2, 3
    ),

post_1980 AS(
SELECT a.mid, a.title, a.year, AVG(b.stars) as average_rating
FROM clintonng_movie a
JOIN clintonng_rating1 b
ON a.mid = b.mid
WHERE a.year > 1980
GROUP BY 1, 2, 3
    )
    
SELECT AVG(a.average_rating) - AVG(b.average_rating)
FROM pre_1980 a,post_1980 b


absolute service time: 0,49 sec