Netflix

Nr	Company	Difficulty	ID Number	Title	Link	Table
1	Netflix	Easy	ID 9687	Find details of oscar winners between 2001 and 2009	https://platform.stratascratch.com/coding/9687-find-details-of-oscar-winners-between-2001-and-2009?code_type=1	oscar_nominees
2	Netflix	Easy	ID 10128	Count the number of movies that Abigail Breslin nominated for oscar	https://platform.stratascratch.com/coding/10128-count-the-number-of-movies-that-abigail-breslin-nominated-for-oscar?code_type=1	oscar_nominees
3	Netflix	Medium	ID 9605	Find the average rating of movie stars	https://platform.stratascratch.com/coding/9605-find-the-average-rating-of-movie-stars?code_type=1	nominee_filmography
4	Netflix	Medium	ID 9750	Find the nominee who has won the most Oscars	https://platform.stratascratch.com/coding/9750-find-the-nominee-who-has-won-the-most-oscars?code_type=1	oscar_nominees
5	Netflix	Medium	ID 9751	Nominees Without An Oscar	https://platform.stratascratch.com/coding/9751-nominees-without-an-oscar?code_type=1	oscar_nominees
6	Netflix	Medium	ID 9752	Win-to-Nomination Ratio	https://platform.stratascratch.com/coding/9752-win-to-nomination-ratio?code_type=1	oscar_nominees
7	Netflix	Medium	ID 9753	Find movies that had the most nominated actors/actresses	https://platform.stratascratch.com/coding/9753-find-movies-that-had-the-most-nominated-actorsactresses?code_type=1	oscar_nominees
8	Netflix	Medium	ID 9754	Best Actors/Actresses Of All Time	https://platform.stratascratch.com/coding/9754-best-actorsactresses-of-all-time?code_type=1	oscar_nominees
9	Netflix	Medium	ID 2102	Flags per Video	https://platform.stratascratch.com/coding/2102-flags-per-video?code_type=1	user_flags
10	Netflix	Medium	ID 2133	First Three Most Watched Videos	https://platform.stratascratch.com/coding/2133-first-three-most-watched-videos?code_type=1	videos_watched
11	Netflix	Hard	ID 2054	Consecutive Days	https://platform.stratascratch.com/coding/2054-consecutive-days?code_type=1	sf_events
12	Netflix	Hard	ID 10171	Find the genre of the person with the most number of oscar winnings	https://platform.stratascratch.com/coding/10171-find-the-genre-of-the-person-with-the-most-number-of-oscar-winnings?code_type=1	oscar_nominees, nominee_information
13	Netflix	Hard	ID 9606	Differences In Movie Ratings	https://platform.stratascratch.com/coding/9606-differences-in-movie-ratings?code_type=1	nominee_filmography, nominee_information

EASY

Find details of oscar winners between 2001 and 2009

Find the details of oscar winners between 2001 and 2009.

StrataScratch - Find details of oscar winners between 2001 and 2009platform.stratascratch.com

SELECT * FROM oscar_nominees
WHERE year BETWEEN 2001 AND 2009
AND winner = TRUE

MEDIUM

Flags per Video

For each video, find how many unique users flagged it. A unique user can be identified using the combination of their first name and last name. Do not consider rows in which there is no flag ID. Table(s): user_flags

StrataScratch - Flags per Videoplatform.stratascratch.com

SELECT video_id, 
    COUNT(DISTINCT CONCAT(user_firstname,' ',user_lastname))
FROM user_flags
WHERE flag_id IS NOT NULL
GROUP BY 1;

Best Actors/Actresses Of All Time

Find the best actors/actresses of all time based on the number of Oscar awards. Output nominees alongside their number of Oscars. Order records in descending order based on the number of awards.

StrataScratch - Best Actors/Actresses Of All Timeplatform.stratascratch.com

SELECT nominee, COUNT(id) AS oscar_won
FROM oscar_nominees
WHERE winner = TRUE
AND category IN ('actor in a leading role', 'actress in a leading role')
GROUP BY 1
ORDER BY COUNT(id) DESC;

First Three Most Watched Videos

After a new user creates an account and starts watching videos, the user ID, video ID, and date watched are captured in the database. Find the top 3 videos most users have watched as their first 3 videos. Output the video ID and the number of times it has been watched as the users' first 3 videos.

In the event of a tie, output all the videos in the top 3 that users watched as their first 3 videos.

Table(s): videos_watched

StrataScratch - First Three Most Watched Videosplatform.stratascratch.com

SELECT 
    video_id, 
    COUNT(*) AS num_watched 
FROM (
    SELECT * 
    FROM (
        SELECT 
            *, 
            RANK() OVER (PARTITION BY user_id ORDER BY watched_at) AS video_rank 
        FROM videos_watched
        ) AS t1 
    WHERE video_rank <= 3
    ) AS t2 
GROUP BY 1 
ORDER BY COUNT() DESC;

HARD

Consecutive Days

Find all the users who were active for 3 consecutive days or more.

StrataScratch - Consecutive Daysplatform.stratascratch.com

SELECT DISTINCT user_id
FROM (
    SELECT t1.*,
        LAG(date) OVER (PARTITION BY user_id ORDER BY date) as prev_active_date,
        LAG(date, 2) OVER (PARTITION BY user_id ORDER BY date) as prev_Active_date2
    FROM (
        SELECT DISTINCT user_id, date
        FROM sf_events 
        ) t1
    ) t2
WHERE prev_active_date = date - interval '1' day 
AND prev_Active_date2 = date - interval '2' day;

-- OR
SELECT DISTINCT s1.user_id
FROM sf_events s1
JOIN sf_events s2
ON s1.user_id = s2.user_id AND s1.date = s2.date - INTERVAL '1' DAY
JOIN sf_events s3
ON s1.user_id = s3.user_id AND s1.date = s3.date - INTERVAL '2' DAY;

Find the genre of the person with the most number of oscar winnings

Find the genre of the person with the most number of oscar winnings. If there are more than one person with the same number of oscar wins, return the first one in alphabetic order based on their name. Use the names as keys when joining the tables.

StrataScratch - Find the genre of the person with the most number of oscar winningsplatform.stratascratch.com

SELECT 
    nominee, 
    oscar_won,
    top_genre
FROM
(SELECT nominee, count(id) AS oscar_won
FROM oscar_nominees
WHERE winner = TRUE
GROUP BY 1 
ORDER BY count(id) DESC, nominee
LIMIT 1) t
LEFT JOIN nominee_information n
ON t.nominee = n.name;

Differences In Movie Ratings

Calculate the average lifetime rating and rating from the movie with second biggest id across all actors and all films they had acted in. Remove null ratings from the calculation. Role type is "Normal Acting".

Output a list of actors, their average lifetime rating, rating from the film with the second biggest id (use id column), and the absolute difference between the two ratings.

Table(s): nominee_filmography, nominee_information

StrataScratch - Differences In Movie Ratingsplatform.stratascratch.com

SELECT
    t1.name,
    t1.avg_rating,
    t2.rating,
    ABS(t1.avg_rating - t2.rating) rating_diff
FROM
(SELECT 
    f.name, 
    AVG(rating) AS avg_rating
FROM nominee_filmography f
WHERE f.rating IS NOT NULL 
AND f.role_type = 'Normal Acting'
GROUP BY 1
ORDER BY AVG(rating) DESC) as t1
CROSS JOIN
(SELECT 
    f.name, 
    f.id,
    f.rating
FROM nominee_filmography f
WHERE f.rating IS NOT NULL 
AND f.role_type = 'Normal Acting'
ORDER BY id DESC LIMIT 1 OFFSET 1) as t2

-- OR using temporary tables


WITH t1 AS (
SELECT 
    f.name, 
    AVG(rating) AS avg_rating
FROM nominee_filmography f
WHERE f.rating IS NOT NULL 
AND f.role_type = 'Normal Acting'
GROUP BY 1
ORDER BY AVG(rating) DESC),
t2 AS (
SELECT 
    f.name, 
    f.id,
    f.rating
FROM nominee_filmography f
WHERE f.rating IS NOT NULL 
AND f.role_type = 'Normal Acting'
ORDER BY id DESC LIMIT 1 OFFSET 1)
SELECT 
    t1.name,
    t1.avg_rating,
    t2.rating,
    ABS(t1.avg_rating - t2.rating) rating_diff
FROM t1, t2