<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
Step1:  5T_데이터 분석을 위한 SQL 실습 (4) - SQL Advanced
특정 카테고리에 포함된 영화들의 렌탈 횟수
rental, inventory, film, film_category, category
"Comedy", "Sports", "Family" 카테고리에 포함되는 영화들의 렌탈 횟수
Step3:  Store 1의 등급별 매출 중 "R", "PG-13"의 매출
film, payment, inventory, rental
Step6:  배우별 매출
영화별 매출을 구하고 이 데이터를 바탕으로 배우별 매출을 구하세요
<ASSISTANT_TASK:>
Python Code:
import pymysql
db = pymysql.connect(
    "db.fastcamp.us",
    "root",
    "dkstncks",
    "sakila",
    charset='utf8',
)
rental_df = pd.read_sql("SELECT * FROM rental;", db)
inventory_df = pd.read_sql("SELECT * FROM inventory;", db)
film_df = pd.read_sql("SELECT * FROM film;", db)
film_category_df = pd.read_sql("SELECT * FROM film_category;", db)
category_df = pd.read_sql("SELECT * FROM category;", db)
rental_df.head(1)
inventory_df.head(1)
film_df.head(1)
film_category_df.head(1)
category_df.head(1)
SQL_QUERY = 
    SELECT
        c.category_id category_id,
        c.name category_name,
        COUNT(*) rentals_per_category
    FROM
        rental r
            JOIN inventory i ON r.inventory_id = i.inventory_id
            JOIN film f ON f.film_id = i.film_id
            JOIN film_category fc ON fc.film_id = f.film_id
            JOIN category c ON fc.category_id = c.category_id
    WHERE
        c.name IN ("Family", "Sports", "Comedy")
    GROUP BY
        category_id
    ORDER BY rentals_per_category DESC
    ;
pd.read_sql(SQL_QUERY, db)
Explanation: 5T_데이터 분석을 위한 SQL 실습 (4) - SQL Advanced
특정 카테고리에 포함된 영화들의 렌탈 횟수
rental, inventory, film, film_category, category
"Comedy", "Sports", "Family" 카테고리에 포함되는 영화들의 렌탈 횟수
End of explanation
payment_df = pd.read_sql("SELECT * FROM payment;", db)
film_df.head(1)
payment_df.head(1)
inventory_df.head(1)
rental_df.head(1)
SQL_QUERY = 
    SELECT
        i.store_id store_id,
        f.rating rating,
        SUM(p.amount) total_revenue
    FROM
        payment p
            JOIN rental r ON p.rental_id = r.rental_id
            JOIN inventory i ON r.inventory_id = i.inventory_id
            JOIN film f ON i.film_id = f.film_id
    WHERE
        i.store_id = 1
        AND f.rating IN ("PG-13", "R")
    GROUP BY
        store_id,
        rating
    ;
pd.read_sql(SQL_QUERY, db)
Explanation: Store 1의 등급별 매출 중 "R", "PG-13"의 매출
film, payment, inventory, rental
End of explanation
# 1. 영화별 매출 - rental, film, inventory
REVENUE_PER_FILM_SQL_QUERY = 
    SELECT
        f.film_id film_id,
        COUNT(*) * f.rental_rate revenue
    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
        film_id
    ;
pd.read_sql(REVENUE_PER_FILM_SQL_QUERY, db)
# 2. 배우별 매출 - actor, film_actor
SQL_QUERY = 
    SELECT
        a.actor_id,
        a.last_name last_name,
        a.first_name first_name,
        SUM(rpf.revenue) revenue_per_actor
    FROM ({REVENUE_PER_FILM_SQL_QUERY}) AS rpf
        JOIN film_actor fa ON rpf.film_id = fa.film_id
        JOIN actor a ON fa.actor_id = a.actor_id
    GROUP BY
        actor_id
    ORDER BY revenue_per_actor DESC
    ;
.format(
    REVENUE_PER_FILM_SQL_QUERY=REVENUE_PER_FILM_SQL_QUERY.replace(";",""),
)
pd.read_sql(SQL_QUERY, db)
Explanation: 배우별 매출
영화별 매출을 구하고 이 데이터를 바탕으로 배우별 매출을 구하세요
End of explanation
<END_TASK>