In [1]:
# Subquery Example
In [2]:
import pandas as pd
import MySQLdb
db = MySQLdb.connect(
"db.fastcamp.us", # DATABASE_HOST
"root", # DATABASE_USERNAME
"dkstncks", # DATABASE_PASSWORD
"sakila", # DATABASE_NAME
charset='utf8',
)
In [3]:
# Revenue Per Film
REVENUE_PER_FILM_SQL_QUERY = """
SELECT
film.film_id,
film.rental_rate * COUNT(rental.rental_id) "Revenue"
FROM
rental,
inventory,
film
WHERE
rental.inventory_id = inventory.inventory_id
AND inventory.film_id = film.film_id
GROUP BY 1
;
"""
pd.read_sql(REVENUE_PER_FILM_SQL_QUERY, db)
Out[3]:
In [4]:
# Actor to Film Revenue
SQL_QUERY = """
SELECT
actor.actor_id,
CONCAT(actor.first_name, " ", actor.last_name) AS "Actor Name",
SUM(rev_per_film.Revenue) "Total Film Revenue"
FROM
({REVENUE_PER_FILM_SQL_QUERY}) AS rev_per_film,
actor,
film_actor
WHERE
actor.actor_id = film_actor.actor_id
AND film_actor.film_id = rev_per_film.film_id
GROUP BY 1
ORDER BY 3 DESC
""".format(
REVENUE_PER_FILM_SQL_QUERY=REVENUE_PER_FILM_SQL_QUERY.replace(";", "")
)
pd.read_sql(SQL_QUERY, db)
Out[4]:
In [5]:
print(SQL_QUERY)
In [ ]: