# Netflix

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

## EASY

### Find details of oscar winners between 2001 and 2009

Find the details of oscar winners between 2001 and 2009.

{% embed url="<https://platform.stratascratch.com/coding/9687-find-details-of-oscar-winners-between-2001-and-2009?code_type=1>" %}

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

{% embed url="<https://platform.stratascratch.com/coding/2102-flags-per-video?code_type=1>" %}

```sql
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.

{% embed url="<https://platform.stratascratch.com/coding/9754-best-actorsactresses-of-all-time?code_type=1>" %}

```sql
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

{% embed url="<https://platform.stratascratch.com/coding/2133-first-three-most-watched-videos?code_type=1>" %}

```sql
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.

{% embed url="<https://platform.stratascratch.com/coding/2054-consecutive-days?code_type=1>" %}

```sql
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.

{% embed url="<https://platform.stratascratch.com/coding/10171-find-the-genre-of-the-person-with-the-most-number-of-oscar-winnings?code_type=1>" %}

```sql
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".&#x20;

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**](/ds-hub/sql/sql-practice/popular-websites-for-sql-practice/stratascratch/netflix/nominee-filmography-table.md)**,** [**nominee\_information**](/ds-hub/sql/sql-practice/popular-websites-for-sql-practice/stratascratch/netflix/nominee-information-table.md)

{% embed url="<https://platform.stratascratch.com/coding/9606-differences-in-movie-ratings?code_type=1>" %}

```sql
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
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://dshub.gitbook.io/ds-hub/sql/sql-practice/popular-websites-for-sql-practice/stratascratch/netflix.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.