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
"""


