231207 SQL계속(6)

-- 26.
DROP VIEW top5_genres;

-- 27.
SELECT *
FROM rental;
-- rental_date, inventory_id

SELECT *
FROM inventory;
-- inventory_id >> film_id

SELECT *
FROM film;
-- film_id, title

SELECT f.title, COUNT(*)
FROM rental r
         JOIN inventory i ON r.inventory_id = i.inventory_id
         JOIN film f ON i.film_id = f.film_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 3;

-- 28.

SELECT *
FROM rental;

SELECT *
FROM inventory;

SELECT *
FROM film f
         JOIN inventory i ON f.film_id = i.film_id
         LEFT JOIN rental ON i.inventory_id = rental.inventory_id;

SELECT title
FROM film
WHERE title NOT IN (SELECT DISTINCT f.title
                    FROM rental r
                             JOIN inventory i ON r.inventory_id = i.inventory_id
                             JOIN film f ON i.film_id = f.film_id)
;

-- 29.
SELECT CONCAT(c.first_name, ' ', c.last_name), SUM(amount)
FROM payment p
         JOIN customer c ON p.customer_id = c.customer_id
GROUP BY p.customer_id
HAVING SUM(amount) > (SELECT AVG(b)
                      FROM (SELECT SUM(amount) AS b
                            FROM payment
                            GROUP BY customer_id) AS a);
;
# customer_id

SELECT AVG(b)
FROM (SELECT SUM(amount) AS b
      FROM payment
      GROUP BY customer_id) AS a;
-- 고객 합의 평균

SELECT *
FROM customer;
# customer_id, first_name, last_name

-- 30.
SELECT staff_id, COUNT(staff_id)
FROM payment
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

 

오히려 뒤쪽에 몇 문제는 쉬웠던 것 같다. 차분하게 하니 문제가 잘 풀린다!

sql 프로그래머스 테스트(고득점키트)를 시작했다. 재미있게 공부중!