[SQL] Correlated subquery

연습문제: 장르별 평균 평점보다 높은 영화 확인하기

WITH avg_rating AS (
	SELECT 
    	ROUND(AVG(inner_movies.rating), 2) 
    FROM 
        movies AS inner_movies
    WHERE 1=1
        AND inner_movies.genres = main_movies.genres 
)
SELECT
	main_movies.title,
    main_movies.genres,
    main_movies.rating,
    (SELECT * FROM avg_rating) AS avg_rating
FROM
	movies AS main_movies
WHERE 1=1
	AND main_movies.release_date > 2020
	AND main_movies.rating > (SELECT * FROM avg_rating)
ORDER BY
	main_movies.genres ASC,
    main_movies.rating DESC;

연습문제: 감독 평균 수익보다 많은 수익을 번 감독 찾기

WITH director_by_career_rev AS (
    SELECT
        director,
        SUM(revenue) AS career_revenue
    FROM
        movies
    WHERE 1=1
        AND director IS NOT NULL
        AND revenue IS NOT NULL
    GROUP BY
        director
), peers_avg AS (
	SELECT
    	AVG(career_revenue) 
    FROM
    	director_by_career_rev  
)
SELECT
	director,
	SUM(revenue) AS total_revenue,
    ROUND((SELECT * FROM peers_avg), 0) AS peers_avg
FROM
	movies
WHERE 1=1
	AND director IS NOT NULL
    AND revenue IS NOT NULL
GROUP BY
	director
HAVING
	total_revenue > (SELECT * FROM peers_avg);