[Sakila] 통계 쿼리 연습

1. 카테고리 별 영화 수

SELECT 
	c.category_id
	, c.`name`
	, COUNT(*)
FROM film f
JOIN film_category fc
ON f.film_id = fc.film_id
JOIN category c
ON fc.category_id = c.category_id
GROUP BY c.category_id

 

2. 러닝타임(length)이 3시간 이상 / 2시간 이상 / 1시간 이상 / 1시간 미만인 영화 수

SELECT 
	COUNT(*) '3시간 이상'
FROM film
WHERE LENGTH >= 180

SELECT 
    SUM(CASE WHEN length >= 180 THEN 1 ELSE 0 END) '3시간 이상',
    SUM(CASE WHEN length >= 120 AND length < 180 THEN 1 ELSE 0 END) '2시간 이상',
    SUM(CASE WHEN length >= 60 AND LENGTH < 120 THEN 1 ELSE 0 END) '1시간 이상',
    SUM(CASE WHEN LENGTH < 60 THEN 1 ELSE 0 END) '1시간 미만'
FROM film;

 

3. 대여료(rental_rate)가 4달러 이상 / 3달러 이상 / 2달러 이상 / 1달러 이상 / 1달러 미만인 영화 수

SELECT COUNT(*) '4달러 이상'
FROM film
WHERE rental_rate >= 4;

SELECT 
    SUM(CASE WHEN rental_rate >= 4 THEN 1 ELSE 0 END) '4달러 이상',
    SUM(CASE WHEN rental_rate >= 3 AND rental_rate < 4 THEN 1 ELSE 0 END) '3달러 이상',
    SUM(CASE WHEN rental_rate >= 2 AND rental_rate < 3 THEN 1 ELSE 0 END) '2달러 이상',
    SUM(CASE WHEN rental_rate >= 1 AND rental_rate < 2 THEN 1 ELSE 0 END) '1달러 이상',
    SUM(CASE WHEN rental_rate < 1 THEN 1 ELSE 0 END) '1달러 미만'
FROM film;

 

4. 영화 등급(rating) 별 수

SELECT 
	rating
   , COUNT(*) COUNT
FROM film
GROUP BY rating

 

5. 가장 많은 배우가 참여한 영화 1~5위

SELECT 
	f.film_id
	, f.title
   , COUNT(*) COUNT
FROM film_actor fa
JOIN film f
ON fa.film_id = f.film_id
GROUP BY f.film_id
ORDER BY COUNT(*) DESC
LIMIT 5;

 

6. 가장 많은 영화에 참여한 배우 1~5위

SELECT 
	a.first_name firstName
	, a.last_name lastName
	, COUNT(*)
FROM film_actor fa
JOIN actor a
ON fa.actor_id = a.actor_id
GROUP BY fa.actor_id
ORDER BY COUNT(*) DESC
LIMIT 5;

 

7. 가장 많이 대여된 영화 1~5위

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

 

8. 가장 대여 매출이 높은 영화 1~5위

SELECT 
	f.title title
	, SUM(p.amount) totalAmount
FROM payment p
JOIN rental r
ON p.rental_id = r.rental_id
JOIN inventory iv
ON r.inventory_id = iv.inventory_id
JOIN film f
ON iv.film_id = f.film_id
GROUP BY f.film_id
ORDER BY totalAmount DESC
LIMIT 5;

 

9. 영화를 가장 많이 대여한 고객 1~5위

SELECT 
	c.customer_id customerId
	, c.first_name firstName
	, c.last_name lastName
	, COUNT(*) '대여횟수'
FROM customer c
JOIN rental r
ON c.customer_id = r.customer_id
GROUP BY c.customer_id
ORDER BY COUNT(*) DESC
LIMIT 5;

 

10. 고객이 가장 많은 도시 1~5위

SELECT 
	city.city city
	, COUNT(*) '거주 고객 수'
FROM customer c
JOIN address a
ON c.address_id = a.address_id
JOIN city
ON a.city_id = city.city_id
GROUP BY city.city_id
ORDER BY COUNT(*) DESC
LIMIT 5;

 

11. 고객이 가장 많은 나라 1~5위

SELECT 
	country.country '국가'
	, COUNT(*) '거주 고객 수'
FROM customer c
JOIN address a
ON c.address_id = a.address_id
JOIN city
ON a.city_id = city.city_id
JOIN country
ON city.country_id = country.country_id
GROUP BY country.country_id
ORDER BY COUNT(*) DESC
LIMIT 5;

 

12.  지점 별 2005년 월별 매출액

SELECT 
	sr.store_id
	, MONTH(p.payment_date) month
	, SUM(p.amount) '월 매출액'
FROM payment p
JOIN staff sf
ON p.staff_id = sf.staff_id
JOIN store sr
ON sf.store_id = sr.store_id
WHERE YEAR(p.payment_date)=2005
GROUP BY sr.store_id, MONTH(p.payment_date)
ORDER BY sr.store_id, month;