<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
Step2:  3T_데이터 분석을 위한 SQL 실습 (2) - SUB QUERY, HAVING
유저별 매출을 출력하세요. customer, payment
Step3:  JOIN은 조금 어렵지만 속도가 WHERE보다 빠르다.
Step8:  서브쿼리랑 HAVING 다시 천천히 해보자
렌탈 횟수가 30회 이상인 유저
Step9:  pandas
SUBQUERY - 1. Rental per User, 2.30개 이상인 애들 뽑기
<ASSISTANT_TASK:>
Python Code:
import pymysql
db = pymysql.connect(
    "db.fastcamp.us",
    "root",
    "dkstncks",
    "sakila",
    charset='utf8',
)
customer_df = pd.read_sql("SELECT * FROM customer;", db)
payment_df = pd.read_sql("SELECT * FROM payment;", db)
customer_df.head(1)
payment_df.head(1)
SQL_QUERY = 
    SELECT c.first_name, c.last_name, SUM(p.amount) "Revenue"
    FROM
        customer c
            JOIN payment p
            ON p.customer_id = c.customer_id
    GROUP BY c.customer_id
    ORDER BY Revenue DESC
    ;
pd.read_sql(SQL_QUERY, db)
SQL_QUERY = 
    SELECT
        c.customer_id,
        SUM(p.amount)
    FROM payment p, customer c
    WHERE p.customer_id = c.customer_id
    GROUP BY c.customer_id
    ;
pd.read_sql(SQL_QUERY, db)
Explanation: 3T_데이터 분석을 위한 SQL 실습 (2) - SUB QUERY, HAVING
유저별 매출을 출력하세요. customer, payment
End of explanation
payment_df.groupby("customer_id").agg({"amount": np.sum})
Explanation: JOIN은 조금 어렵지만 속도가 WHERE보다 빠르다.
End of explanation
rental_df = pd.read_sql("SELECT * FROM rental;", db)
rental_df.head(1)
customer_df.head(1)
SQL_QUERY = 
    SELECT
        c.first_name,
        c.last_name,
        COUNT(*) "rentals_per_customer"
    FROM 
        rental r
            JOIN customer c
            ON r.customer_id = c.customer_id
    GROUP BY c.customer_id
    HAVING rentals_per_customer >=30
    ORDER BY 3 DESC
    ;
pd.read_sql(SQL_QUERY, db)
SQL_QUERY = 
    SELECT
        c.first_name,
        c.last_name,
        COUNT(*) "rentals_per_customer"
    FROM
        rental r,
        customer c
    WHERE
        r.customer_id = c.customer_id
    GROUP BY c.customer_id
    HAVING rentals_per_customer >= 30
    ;
pd.read_sql(SQL_QUERY, db)
RENTALS_PER_CUSTOMER_SQL_QUERY = 
    SELECT
        c.first_name,
        c.last_name,
        COUNT(*) "rentals_per_customer"
    FROM 
        rental r
            JOIN customer c
            ON r.customer_id = c.customer_id
    GROUP BY c.customer_id
    ;
SQL_QUERY = 
    SELECT *
    FROM ({RENTALS_PER_CUSTOMER_SQL_QUERY}) as rpc
    WHERE rentals_per_customer >= 30
    ;
.format(RENTALS_PER_CUSTOMER_SQL_QUERY=RENTALS_PER_CUSTOMER_SQL_QUERY.replace(";", ""))
# print(SQL_QUERY)
pd.read_sql(SQL_QUERY, db)
Explanation: 서브쿼리랑 HAVING 다시 천천히 해보자
렌탈 횟수가 30회 이상인 유저
End of explanation
rc_df = rental_df.merge(customer_df, on="customer_id")
rc_df.groupby("customer_id").size() >= 30
rentals_per_customer_df = rc_df.groupby("customer_id").agg({"customer_id": np.size})
is_30 = rentals_per_customer_df.customer_id > 30
rentals_per_customer_df[is_30]
Explanation: pandas
SUBQUERY - 1. Rental per User, 2.30개 이상인 애들 뽑기
End of explanation
<END_TASK>