231211 sql test 2차 시작

-- 36.
SELECT *
FROM customer;
# first_name, last_name

SELECT *
FROM rental;
# >> customer_id, inventory_id

SELECT *
FROM inventory;
# inventory_id >> film_id

SELECT *
FROM film_category;
# film_id >> category_id

SELECT *
FROM category;
# category_id, name

SELECT DISTINCT c.customer_id, ca.name, COUNT(ca.name)
FROM rental r
         JOIN customer c ON r.customer_id = c.customer_id
         JOIN inventory i ON r.inventory_id = i.inventory_id
         JOIN film_category fc ON i.film_id = fc.film_id
         JOIN category ca ON fc.category_id = ca.category_id
GROUP BY c.customer_id, ca.name
ORDER BY 1, 3 DESC;

SELECT customer_id, name AS favorite_genre, genre_count
FROM (SELECT c.customer_id,
             ca.name,
             COUNT(ca.name)                                                        AS genre_count,
             RANK() OVER (PARTITION BY c.customer_id ORDER BY COUNT(ca.name) DESC) AS genre_rank
      FROM rental r
               JOIN customer c ON r.customer_id = c.customer_id
               JOIN inventory i ON r.inventory_id = i.inventory_id
               JOIN film_category fc ON i.film_id = fc.film_id
               JOIN category ca ON fc.category_id = ca.category_id
      GROUP BY c.customer_id, ca.name) AS ranked_genres
WHERE genre_rank = 1
ORDER BY customer_id;

-- 37.
SELECT *
FROM rental;
# rental_date, customer_id

SELECT *
FROM customer;
# first_name, last_name

SELECT DISTINCT CONCAT(first_name, ' ', last_name)
FROM rental
         JOIN customer ON rental.customer_id = customer.customer_id
WHERE rental_date NOT BETWEEN '2006-01-14' AND '2006-02-14'
ORDER BY 1;

-- 38.
SELECT *
FROM customer;

SELECT CONCAT(first_name, ' ', last_name)           AS customer_name,
       title                                        AS movie_title,
       TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_duration,
       return_date
FROM rental r
         JOIN customer c ON r.customer_id = c.customer_id
         JOIN inventory i ON r.inventory_id = i.inventory_id
         JOIN film f ON i.film_id = f.film_id
ORDER BY return_date DESC
LIMIT 10;

-- 39.

SELECT @total := SUM(payment.amount)
FROM payment;

SELECT p.staff_id, CONCAT(s.first_name, ' ', s.last_name), SUM(amount), (SUM(amount) / @total) * 100 AS ratio
FROM payment p
         JOIN staff s ON p.staff_id = s.staff_id
GROUP BY 1;

SELECT *
FROM staff;
#first_name, last_name

SELECT @total := SUM(payment.amount)
FROM payment;

-- 40.
SELECT *
FROM rental;
# inventory_id, rental_id

SELECT *
FROM inventory;
# inventory_id >> film_id;

SELECT *
FROM film_category;
# film_id >> category_id

SELECT *
FROM category;
# category_id, name

SELECT @cus_id := customer_id, @cus_count := COUNT(DISTINCT i.film_id) AS unique_movies_borrowed
FROM rental r
         JOIN inventory i ON r.inventory_id = i.inventory_id
         JOIN film_category fc ON fc.film_id = i.film_id
         JOIN category c ON fc.category_id = c.category_id
GROUP BY customer_id
ORDER BY 2 DESC
LIMIT 1;



SELECT @cus_count;

SELECT customer_id, name AS favorite_genre, genre_count
FROM (SELECT c.customer_id,
             ca.name,
             COUNT(ca.name)                                                        AS genre_count,
             RANK() OVER (PARTITION BY c.customer_id ORDER BY COUNT(ca.name) DESC) AS genre_rank
      FROM rental r
               JOIN customer c ON r.customer_id = c.customer_id
               JOIN inventory i ON r.inventory_id = i.inventory_id
               JOIN film_category fc ON i.film_id = fc.film_id
               JOIN category ca ON fc.category_id = ca.category_id
      GROUP BY c.customer_id, ca.name) AS ranked_genres
WHERE genre_rank = 1
ORDER BY customer_id;

 

40번이 겁나 어려운것이었다.. 이런건 엑셀로 하는게 속편할듯싶다.

rank 함수에 대한 연구가 필요하다, union, except, intersect 역시 같이 공부해야겠다!