[SQL] GROUP BY 연습

1번 문제:

영화 5편 이상을 찍은 감독별 평균 rating은?

SELECT
	director,
    ROUND(AVG(rating), 2) AS avg_rating,
    COUNT(*) AS cnt_movies
FROM
	movies
WHERE 1=1
	AND rating IS NOT NULL
    AND director IS NOT NULL
GROUP BY
	director
HAVING
	cnt_movies >= 5
ORDER BY
	avg_rating DESC;

2번 문제:

21세기에 개봉한 영화 평균 평점은?

SELECT
	ROUND(AVG(rating), 2) AS avg_rating
FROM
	movies
WHERE 1=1
	AND rating IS NOT NULL
    AND release_date >= 2000;

3번 문제:

가장 많은 영화를 작업한 다작 감독 상위 5명은?

SELECT
	director,
    COUNT(*) AS total_movies
FROM
	movies
WHERE 1=1
	AND director IS NOT NULL
    AND runtime > 45
GROUP BY
	director
LIMIT 5;

4번 문제:

감독별 최고/최저 평점 확인하기

SELECT
	director,
    MAX(rating) AS max_rating,
    MIN(rating) AS min_rating,
    COUNT(*) AS total_movies
FROM
	movies
WHERE 1=1
	AND director IS NOT NULL
    AND rating IS NOT NULL
GROUP BY
	director
HAVING
	total_movies > 1;

5번 문제:

돈을 가장 많이 번 감독 찾기(수익에서 예산을 뺀 금액)

SELECT 
	director,
    SUM(revenue) - SUM(budget) AS real_money
FROM 
	movies
WHERE 1=1
	AND revenue IS NOT NULL
    AND budget IS NOT NULL
    AND director IS NOT NULL
GROUP BY
	director
ORDER BY
	real_money DESC;

6번 문제:

평점 7.0보다 높은 영화가 차지하는 비율이 가장 높은 감독 찾기

SELECT
	director,
	COUNT(CASE WHEN rating > 7.0 THEN 1) * 100 / COUNT(*) AS ratio_over_7,
    COUNT(*) AS total_movies
FROM
	movies
WHERE 1=1
	AND director IS NOT NULL
GROUP BY
	director
HAVING
	total_movies > 5
ORDER BY
	ratio_over_7

7번 문제:

상영 시간별로 영화를 분류하고 그룹화하기

SELECT
	CASE WHEN runtime < 90 THEN
    	'Short'
    WHEN runtime BETWEEN 90 AND 120 THEN
    	'Normal'
    WHEN runtime > 120 THEN
    	'Long'
    END AS runtime_category,
    COUNT(*) AS total_movies
FROM
	movies
GROUP BY
	runtime_category
ORDER BY
	total_movies DESC;