Application_query

Create table Netflix (
    show_id      VARCHAR(5) primary key,
    type         VARCHAR(10),
    title        VARCHAR(250),
    director     VARCHAR(550),
    casts        VARCHAR(1050),
    country      VARCHAR(550),
    date_added   VARCHAR(55),
    release_year INT,
    rating       VARCHAR(15),
    duration     VARCHAR(15),
    listed_in    VARCHAR(250),
    description  VARCHAR(550)

);

copy Netflix
from 'D:\SQL Projects\netflix_sql_project-main\netflix_titles.csv'
delimiter ','
csv header;

select * from netflix;

-- Netflix Data Analysis using SQL
-- Solutions of 15 business problems
-- 1. Count the number of Movies vs TV Shows
select type,count(*)  as total_number_of from netflix
group by 1;

---- 2. Find the most common rating for movies and TV shows
select rating, count(*) as rating_count from netflix
group by 1
order by 2 desc; 

WITH RatingCounts AS (
    SELECT 
        type,
        rating,
        COUNT(*) AS rating_count
    FROM netflix
    GROUP BY type, rating
),
RankedRatings AS (
    SELECT 
        type,
        rating,
        rating_count,
        RANK() OVER (PARTITION BY type ORDER BY rating_count DESC) AS rank
    FROM RatingCounts
)
SELECT 
    type,
    rating AS most_frequent_rating
FROM RankedRatings
WHERE rank = 1;


-- 3. List all movies released in a specific year (e.g., 2020)

SELECT show_id, type, date_added
FROM netflix
WHERE EXTRACT(YEAR FROM TO_DATE(date_added, 'Month DD, YYYY')) = 2020
  AND type = 'Movie';

-- 4. Find the top 5 countries with the most content on Netflix
SELECT country, COUNT(*) AS number_of_content
FROM netflix
WHERE country IS NOT NULL
GROUP BY country
ORDER BY number_of_content DESC
LIMIT 5;


-- 5. Identify the longest movie
select * from netflix where type='Movie'
order by split_part(duration, ' ', 1)::INT DESC limit 1

-- 6. Find content added in the last 5 years
select date_added,description from netflix
where EXTRACT(YEAR FROM TO_DATE(date_added, 'Month DD, YYYY')) > 2020;

SELECT
*
FROM netflix
WHERE TO_DATE(date_added, 'Month DD, YYYY') >= CURRENT_DATE - INTERVAL '5 years';

-- 7. Find all the movies/TV shows by director 'Rajiv Chilaka'!
select director,type from netflix
where director='Rajiv Chilaka';

-- 8. List all TV shows with more than 5 seasons
select * from netflix;

select type,duration from netflix
where type='TV Show' 
AND SPLIT_PART(duration, ' ', 1)::INT > 5 
group by 1,2;

--9. Count the Number of Content Items in Each Genre

SELECT 
    UNNEST(STRING_TO_ARRAY(listed_in, ',')) AS genre,
    COUNT(*) AS total_content
FROM netflix
GROUP BY 1;

-- 10.Find each year and the average numbers of content release in India on netflix. 
SELECT year, round(AVG(content_count)) AS avg_content_release
FROM (
    SELECT 
        EXTRACT(YEAR FROM TO_DATE(date_added, 'Month DD, YYYY')) AS year,
        COUNT(*) AS content_count
    FROM netflix
    WHERE country = 'India'
    GROUP BY year
) AS yearly_counts
GROUP BY year
ORDER BY year;

--11.List All Movies that are Documentaries
select * from netflix 
where listed_in like '%Documentaries';

-- 12. Find All Content Without a Director
select * from netflix 
where Director is null;

--13.Find How Many Movies Actor 'Salman Khan' Appeared in the Last 10 Years
SELECT COUNT(*) AS total_movies
FROM netflix
WHERE type = 'Movie'
  AND casts ilike '%Salman Khan%'
  AND TO_DATE(date_added, 'Month DD, YYYY') >= CURRENT_DATE - INTERVAL '10 years';

--14.Find the Top 10 Actors Who Have Appeared in the Highest Number of Movies Produced in India
SELECT 
    UNNEST(STRING_TO_ARRAY(casts, ',')) AS actor,
    COUNT(*) as number_of_appearence
FROM netflix
WHERE country = 'India'
GROUP BY actor
ORDER BY COUNT(*) DESC
LIMIT 10;

--15. Categorize Content Based on the Presence of 'Kill' and 'Violence' Keywords

SELECT 
    category,
    COUNT(*) AS content_count
FROM (
    SELECT 
        CASE 
            WHEN description ILIKE '%kill%' OR description ILIKE '%violence%' THEN 'Bad'
            ELSE 'Good'
        END AS category
    FROM netflix
) AS categorized_content
GROUP BY category;