{ "cells": [ { "cell_type": "markdown", "id": "fd7cb05a-3e4c-4bc6-8a8b-e9b8d47a61ae", "metadata": {}, "source": [ "## PROBLEM 5 (40 points)\n", "### The ***value*** of an actor is the sum of the rental revenues of *all* the films that the actor was in, based on the films' rental payments. Do the following:\n", "- ### Calculate the sum of the values of *all* the actors in the film(s) that have the *lowest* rental count.\n", "- ### Calculate the sum of the values of the *most popular* actor(s) in the film(s) that have the *highest* rental count. Popularity is a measure of the number of films an actor is in. There can be ties of who's the most popular.\n", "- ### Display in a dataframe the two sums, each formatted as monetary amounts." ] }, { "cell_type": "code", "execution_count": null, "id": "7035c1e5-9d8e-485b-ab21-470665bd6bff", "metadata": {}, "outputs": [], "source": [ "from data201 import db_connection, df_query\n", "conn = db_connection(config_file='sakila.ini')" ] }, { "cell_type": "markdown", "id": "fdeb13cb-99d0-45ef-9e3d-aa6fdf5fe3fa", "metadata": {}, "source": [ "#### Rental counts of the films." ] }, { "cell_type": "code", "execution_count": null, "id": "694e3b31-0fd8-40ca-8a56-98130a555a88", "metadata": {}, "outputs": [], "source": [ "df_query(conn,\n", " \"\"\"\n", " WITH\n", "\t\tfilm_rental_counts AS\n", "\t\t(\n", " SELECT film_id,\n", " \t COUNT(rental_id) AS rental_count\n", " FROM film\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " GROUP BY film_id\n", " ORDER BY rental_count\n", " \t)\t\n", " SELECT * FROM film_rental_counts\n", " \"\"\"\n", ")" ] }, { "cell_type": "markdown", "id": "8af1b771-bc9c-4527-b819-7b0fa211cedf", "metadata": {}, "source": [ "#### Minimum and maximum film rental counts." ] }, { "cell_type": "code", "execution_count": null, "id": "d3ebb9ef-e303-43a0-8676-82cbccc3dec2", "metadata": {}, "outputs": [], "source": [ "df_query(conn,\n", " \"\"\"\n", " WITH\n", "\t\tfilm_rental_counts AS\n", "\t\t(\n", " SELECT film_id,\n", " \t COUNT(rental_id) AS rental_count\n", " FROM film\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " GROUP BY film_id\n", " ORDER BY rental_count\n", " \t),\n", " min_max_rental_counts AS\n", " (\n", " \t\tSELECT MIN(rental_count) AS min_rental_count,\n", " MAX(rental_count) AS max_rental_count\n", " FROM film_rental_counts\n", " )\t\n", " -- SELECT * FROM film_rental_counts\n", " SELECT * FROM min_max_rental_counts\n", " \"\"\"\n", ")" ] }, { "cell_type": "markdown", "id": "2747d3dd-a586-403d-8893-db55d8de9d2e", "metadata": {}, "source": [ "#### The least rented films." ] }, { "cell_type": "code", "execution_count": null, "id": "29e3d6de-807b-4586-9a2f-6b25af08e0b4", "metadata": {}, "outputs": [], "source": [ "df_query(conn,\n", " \"\"\"\n", " WITH\n", "\t\tfilm_rental_counts AS\n", "\t\t(\n", " SELECT film_id,\n", " \t COUNT(rental_id) AS rental_count\n", " FROM film\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " GROUP BY film_id\n", " ORDER BY rental_count\n", " \t),\n", " min_max_rental_counts AS\n", " (\n", " \t\tSELECT MIN(rental_count) AS min_rental_count,\n", " MAX(rental_count) AS max_rental_count\n", " FROM film_rental_counts\n", " ),\n", " least_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS least_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = min_rental_count\n", " )\n", " -- SELECT * FROM film_rental_counts\n", " -- SELECT * FROM min_max_rental_counts\n", " SELECT * FROM least_rented_films\n", " \"\"\"\n", ")" ] }, { "cell_type": "markdown", "id": "17033ca1-8925-4737-bea0-8fdf7494e2da", "metadata": {}, "source": [ "#### The most rented films." ] }, { "cell_type": "code", "execution_count": null, "id": "9b8e9b4e-bae0-4231-8e51-450cc0cd6c0f", "metadata": {}, "outputs": [], "source": [ "df_query(conn,\n", " \"\"\"\n", " WITH\n", "\t\tfilm_rental_counts AS\n", "\t\t(\n", " SELECT film_id,\n", " \t COUNT(rental_id) AS rental_count\n", " FROM film\n", " JOIN inventory USING (film_id)\n", " LEFT OUTER JOIN rental USING (inventory_id)\n", " GROUP BY film_id\n", " ORDER BY rental_count\n", " \t),\n", " min_max_rental_counts AS\n", " (\n", " \t\tSELECT MIN(rental_count) AS min_rental_count,\n", " MAX(rental_count) AS max_rental_count\n", " FROM film_rental_counts\n", " ),\n", " least_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS least_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = min_rental_count\n", " ),\n", " most_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS most_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = max_rental_count\n", " )\n", " -- SELECT * FROM film_rental_counts\n", " -- SELECT * FROM min_max_rental_counts\n", " -- SELECT * FROM least_rented_films\n", " SELECT * FROM most_rented_films\n", " \"\"\"\n", ")" ] }, { "cell_type": "markdown", "id": "3526cff9-8121-4b80-8b59-0d9e50cf3a1d", "metadata": {}, "source": [ "#### Actors of the least rented films." ] }, { "cell_type": "code", "execution_count": null, "id": "74a91799-2915-4797-b239-6025a2d7b93b", "metadata": {}, "outputs": [], "source": [ "df_query(conn,\n", " \"\"\"\n", " WITH\n", "\t\tfilm_rental_counts AS\n", "\t\t(\n", " SELECT film_id,\n", " \t COUNT(rental_id) AS rental_count\n", " FROM film\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " GROUP BY film_id\n", " ORDER BY rental_count\n", " \t),\n", " min_max_rental_counts AS\n", " (\n", " \t\tSELECT MIN(rental_count) AS min_rental_count,\n", " MAX(rental_count) AS max_rental_count\n", " FROM film_rental_counts\n", " ),\n", " least_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS least_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = min_rental_count\n", " ),\n", " most_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS most_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = max_rental_count\n", " ),\n", " actors_of_least_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN least_rented_films\n", " WHERE film_id = least_rented_film_id\n", " )\n", " -- SELECT * FROM film_rental_counts\n", " -- SELECT * FROM min_max_rental_counts\n", " -- SELECT * FROM least_rented_films\n", " -- SELECT * FROM most_rented_films\n", " SELECT * FROM actors_of_least_rented_films ORDER BY actor_id\n", " \"\"\"\n", ")" ] }, { "cell_type": "markdown", "id": "3fa5320f-d987-48f0-b871-f5123302d388", "metadata": {}, "source": [ "#### Actors of the most rented films." ] }, { "cell_type": "code", "execution_count": null, "id": "5f18026c-af34-4c5f-9d66-3eb0c3b9d79b", "metadata": {}, "outputs": [], "source": [ "df_query(conn,\n", " \"\"\"\n", " WITH\n", "\t\tfilm_rental_counts AS\n", "\t\t(\n", " SELECT film_id,\n", " \t COUNT(rental_id) AS rental_count\n", " FROM film\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " GROUP BY film_id\n", " ORDER BY rental_count\n", " \t),\n", " min_max_rental_counts AS\n", " (\n", " \t\tSELECT MIN(rental_count) AS min_rental_count,\n", " MAX(rental_count) AS max_rental_count\n", " FROM film_rental_counts\n", " ),\n", " least_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS least_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = min_rental_count\n", " ),\n", " most_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS most_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = max_rental_count\n", " ),\n", " actors_of_least_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN least_rented_films\n", " WHERE film_id = least_rented_film_id\n", " ),\n", " actors_of_most_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN most_rented_films\n", " WHERE film_id = most_rented_film_id\n", " )\n", " -- SELECT * FROM film_rental_counts\n", " -- SELECT * FROM min_max_rental_counts\n", " -- SELECT * FROM least_rented_films\n", " -- SELECT * FROM most_rented_films\n", " -- SELECT * FROM actors_of_least_rented_films\n", " SELECT * FROM actors_of_most_rented_films\n", " \"\"\"\n", ")" ] }, { "cell_type": "markdown", "id": "1ec63c18-d845-4044-9c6f-e0891715ebfc", "metadata": {}, "source": [ "#### Film counts of the actors of the most rented films." ] }, { "cell_type": "code", "execution_count": null, "id": "c9b265c3-35ca-4856-a6ab-89036efc3cc5", "metadata": {}, "outputs": [], "source": [ "df_query(conn,\n", " \"\"\"\n", " WITH\n", "\t\tfilm_rental_counts AS\n", "\t\t(\n", " SELECT film_id,\n", " \t COUNT(rental_id) AS rental_count\n", " FROM film\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " GROUP BY film_id\n", " ORDER BY rental_count\n", " \t),\n", " min_max_rental_counts AS\n", " (\n", " \t\tSELECT MIN(rental_count) AS min_rental_count,\n", " MAX(rental_count) AS max_rental_count\n", " FROM film_rental_counts\n", " ),\n", " least_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS least_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = min_rental_count\n", " ),\n", " most_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS most_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = max_rental_count\n", " ),\n", " actors_of_least_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN least_rented_films\n", " WHERE film_id = least_rented_film_id\n", " ),\n", " actors_of_most_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN most_rented_films\n", " WHERE film_id = most_rented_film_id\n", " ),\n", " film_counts_of_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id, \n", " COUNT(film_id) AS film_count\n", " FROM actors_of_most_rented_films\n", " JOIN film_actor USING (actor_id)\n", " GROUP BY actor_id\n", " ORDER BY film_count DESC\n", " )\n", " -- SELECT * FROM film_rental_counts\n", " -- SELECT * FROM min_max_rental_counts\n", " -- SELECT * FROM least_rented_films\n", " -- SELECT * FROM most_rented_films\n", " -- SELECT * FROM actors_of_least_rented_films\n", " -- SELECT * FROM actors_of_most_rented_films\n", " SELECT * FROM film_counts_of_actors_of_most_rented_films\n", " \"\"\"\n", ")" ] }, { "cell_type": "markdown", "id": "f982ebe4-d35c-471b-84f2-ffe0b5653c7a", "metadata": {}, "source": [ "#### The most popular actor(s) of the most rented films." ] }, { "cell_type": "code", "execution_count": null, "id": "66ad49a3-43b4-4f81-988e-e4b354ae3b9c", "metadata": {}, "outputs": [], "source": [ "df_query(conn,\n", " \"\"\"\n", " WITH\n", "\t\tfilm_rental_counts AS\n", "\t\t(\n", " SELECT film_id,\n", " \t COUNT(rental_id) AS rental_count\n", " FROM film\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " GROUP BY film_id\n", " ORDER BY rental_count\n", " \t),\n", " min_max_rental_counts AS\n", " (\n", " \t\tSELECT MIN(rental_count) AS min_rental_count,\n", " MAX(rental_count) AS max_rental_count\n", " FROM film_rental_counts\n", " ),\n", " least_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS least_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = min_rental_count\n", " ),\n", " most_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS most_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = max_rental_count\n", " ),\n", " actors_of_least_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN least_rented_films\n", " WHERE film_id = least_rented_film_id\n", " ),\n", " actors_of_most_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN most_rented_films\n", " WHERE film_id = most_rented_film_id\n", " ),\n", " film_counts_of_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id, \n", " COUNT(film_id) AS film_count\n", " FROM actors_of_most_rented_films\n", " JOIN film_actor USING (actor_id)\n", " GROUP BY actor_id\n", " ORDER BY film_count DESC\n", " ),\n", " most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id\n", " FROM film_counts_of_actors_of_most_rented_films\n", " WHERE film_count = (\n", " SELECT MAX(film_count)\n", " FROM film_counts_of_actors_of_most_rented_films\n", " )\n", " )\n", " -- SELECT * FROM film_rental_counts\n", " -- SELECT * FROM min_max_rental_counts\n", " -- SELECT * FROM least_rented_films\n", " -- SELECT * FROM most_rented_films\n", " -- SELECT * FROM actors_of_least_rented_films\n", " -- SELECT * FROM actors_of_most_rented_films\n", " -- SELECT * FROM film_counts_of_actors_of_most_rented_films\n", " SELECT * FROM most_popular_actors_of_most_rented_films\n", " \"\"\"\n", ")" ] }, { "cell_type": "markdown", "id": "e04eb550-96b1-497b-850b-735b16625336", "metadata": {}, "source": [ "#### All films of the most popular actor(s) of the most rented films." ] }, { "cell_type": "code", "execution_count": null, "id": "6f9c6e90-213f-486c-96d0-49970abecbd6", "metadata": {}, "outputs": [], "source": [ "df_query(conn,\n", " \"\"\"\n", " WITH\n", "\t\tfilm_rental_counts AS\n", "\t\t(\n", " SELECT film_id,\n", " \t COUNT(rental_id) AS rental_count\n", " FROM film\n", " JOIN inventory USING (film_id)\n", " LEFT OUTER JOIN rental USING (inventory_id)\n", " GROUP BY film_id\n", " ORDER BY rental_count\n", " \t),\n", " min_max_rental_counts AS\n", " (\n", " \t\tSELECT MIN(rental_count) AS min_rental_count,\n", " MAX(rental_count) AS max_rental_count\n", " FROM film_rental_counts\n", " ),\n", " least_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS least_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = min_rental_count\n", " ),\n", " most_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS most_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = max_rental_count\n", " ),\n", " actors_of_least_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN least_rented_films\n", " WHERE film_id = least_rented_film_id\n", " ),\n", " actors_of_most_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN most_rented_films\n", " WHERE film_id = most_rented_film_id\n", " ),\n", " film_counts_of_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id, \n", " COUNT(film_id) AS film_count\n", " FROM actors_of_most_rented_films\n", " JOIN film_actor USING (actor_id)\n", " GROUP BY actor_id\n", " ORDER BY film_count DESC\n", " ),\n", " most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id\n", " FROM film_counts_of_actors_of_most_rented_films\n", " WHERE film_count = (\n", " SELECT MAX(film_count)\n", " FROM film_counts_of_actors_of_most_rented_films\n", " )\n", " ),\n", " all_films_of_most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id, film_id\n", " FROM most_popular_actors_of_most_rented_films\n", " JOIN film_actor USING (actor_id)\n", " )\n", " -- SELECT * FROM film_rental_counts\n", " -- SELECT * FROM min_max_rental_counts\n", " -- SELECT * FROM least_rented_films\n", " -- SELECT * FROM most_rented_films\n", " -- SELECT * FROM actors_of_least_rented_films\n", " -- SELECT * FROM actors_of_most_rented_films\n", " -- SELECT * FROM film_counts_of_actors_of_most_rented_films\n", " -- SELECT * FROM most_popular_actors_of_most_rented_films\n", " SELECT * FROM all_films_of_most_popular_actors_of_most_rented_films\n", " \"\"\"\n", ")" ] }, { "cell_type": "markdown", "id": "2a4e3db6-f197-4291-8e29-2d8f338d6157", "metadata": {}, "source": [ "#### The values of all the actors of the least rented films." ] }, { "cell_type": "code", "execution_count": null, "id": "704e3702-1bf5-4251-944e-a92c09dbb9d7", "metadata": {}, "outputs": [], "source": [ "df_query(conn,\n", " \"\"\"\n", " WITH\n", "\t\tfilm_rental_counts AS\n", "\t\t(\n", " SELECT film_id,\n", " \t COUNT(rental_id) AS rental_count\n", " FROM film\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " GROUP BY film_id\n", " ORDER BY rental_count\n", " \t),\n", " min_max_rental_counts AS\n", " (\n", " \t\tSELECT MIN(rental_count) AS min_rental_count,\n", " MAX(rental_count) AS max_rental_count\n", " FROM film_rental_counts\n", " ),\n", " least_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS least_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = min_rental_count\n", " ),\n", " most_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS most_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = max_rental_count\n", " ),\n", " actors_of_least_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN least_rented_films\n", " WHERE film_id = least_rented_film_id\n", " ),\n", " actors_of_most_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN most_rented_films\n", " WHERE film_id = most_rented_film_id\n", " ),\n", " film_counts_of_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id, \n", " COUNT(film_id) AS film_count\n", " FROM actors_of_most_rented_films\n", " JOIN film_actor USING (actor_id)\n", " GROUP BY actor_id\n", " ORDER BY film_count DESC\n", " ),\n", " most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id\n", " FROM film_counts_of_actors_of_most_rented_films\n", " WHERE film_count = (\n", " SELECT MAX(film_count)\n", " FROM film_counts_of_actors_of_most_rented_films\n", " )\n", " ),\n", " all_films_of_most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id, film_id\n", " FROM most_popular_actors_of_most_rented_films\n", " JOIN film_actor USING (actor_id)\n", " ),\n", " values_of_all_actors_of_least_rented_films AS\n", " (\n", " SELECT actor_id, \n", " SUM(amount) AS values_of_all_least_rented_film_actors\n", " FROM actors_of_least_rented_films\n", " JOIN film_actor USING (actor_id)\n", " JOIN film USING (film_id)\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " JOIN payment USING (rental_id)\n", " GROUP BY actor_id\n", " )\n", " -- SELECT * FROM film_rental_counts\n", " -- SELECT * FROM min_max_rental_counts\n", " -- SELECT * FROM least_rented_films\n", " -- SELECT * FROM most_rented_films\n", " -- SELECT * FROM actors_of_least_rented_films\n", " -- SELECT * FROM actors_of_most_rented_films\n", " -- SELECT * FROM film_counts_of_actors_of_most_rented_films\n", " -- SELECT * FROM most_popular_actors_of_most_rented_films\n", " -- SELECT * FROM all_films_of_most_popular_actors_of_most_rented_films\n", " SELECT * FROM values_of_all_actors_of_least_rented_films ORDER BY actor_id\n", " \"\"\"\n", ")" ] }, { "cell_type": "markdown", "id": "efcb5262-079e-4ba5-b0a9-66e106bedc7b", "metadata": {}, "source": [ "#### The values of the most popular actors of the most rented films." ] }, { "cell_type": "code", "execution_count": null, "id": "72a1f219-d848-479a-846a-3a8850691175", "metadata": {}, "outputs": [], "source": [ "df_query(conn,\n", " \"\"\"\n", " WITH\n", "\t\tfilm_rental_counts AS\n", "\t\t(\n", " SELECT film_id,\n", " \t COUNT(rental_id) AS rental_count\n", " FROM film\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " GROUP BY film_id\n", " ORDER BY rental_count\n", " \t),\n", " min_max_rental_counts AS\n", " (\n", " \t\tSELECT MIN(rental_count) AS min_rental_count,\n", " MAX(rental_count) AS max_rental_count\n", " FROM film_rental_counts\n", " ),\n", " least_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS least_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = min_rental_count\n", " ),\n", " most_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS most_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = max_rental_count\n", " ),\n", " actors_of_least_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN least_rented_films\n", " WHERE film_id = least_rented_film_id\n", " ),\n", " actors_of_most_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN most_rented_films\n", " WHERE film_id = most_rented_film_id\n", " ),\n", " film_counts_of_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id, \n", " COUNT(film_id) AS film_count\n", " FROM actors_of_most_rented_films\n", " JOIN film_actor USING (actor_id)\n", " GROUP BY actor_id\n", " ORDER BY film_count DESC\n", " ),\n", " most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id\n", " FROM film_counts_of_actors_of_most_rented_films\n", " WHERE film_count = (\n", " SELECT MAX(film_count)\n", " FROM film_counts_of_actors_of_most_rented_films\n", " )\n", " ),\n", " all_films_of_most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id, film_id\n", " FROM most_popular_actors_of_most_rented_films\n", " JOIN film_actor USING (actor_id)\n", " ),\n", " values_of_all_actors_of_least_rented_films AS\n", " (\n", " SELECT actor_id, \n", " SUM(amount) AS values_of_all_least_rented_film_actors\n", " FROM actors_of_least_rented_films\n", " JOIN film_actor USING (actor_id)\n", " JOIN film USING (film_id)\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " JOIN payment USING (rental_id)\n", " GROUP BY actor_id\n", " ),\n", " values_of_most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id,\n", " SUM(amount) AS values_of_most_popular_most_rented_film_actors\n", " FROM most_popular_actors_of_most_rented_films\n", " JOIN film_actor USING (actor_id)\n", " JOIN film USING (film_id)\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " JOIN payment USING (rental_id)\n", " GROUP BY actor_id\n", " )\n", " -- SELECT * FROM film_rental_counts\n", " -- SELECT * FROM min_max_rental_counts\n", " -- SELECT * FROM least_rented_films\n", " -- SELECT * FROM most_rented_films\n", " -- SELECT * FROM actors_of_least_rented_films\n", " -- SELECT * FROM actors_of_most_rented_films\n", " -- SELECT * FROM film_counts_of_actors_of_most_rented_films\n", " -- SELECT * FROM most_popular_actors_of_most_rented_films\n", " -- SELECT * FROM all_films_of_most_popular_actors_of_most_rented_films\n", " -- SELECT * FROM values_of_all_actors_of_least_rented_films ORDER BY actor_id\n", " SELECT * FROM values_of_most_popular_actors_of_most_rented_films ORDER BY actor_id\n", " \"\"\"\n", ")" ] }, { "cell_type": "markdown", "id": "47cd04f7-2113-404b-a5ed-4719593d135c", "metadata": {}, "source": [ "#### The total value of all the actors of the least rented films." ] }, { "cell_type": "code", "execution_count": null, "id": "1523c0cb-fab6-40f1-9db7-1f53cf01a2a5", "metadata": {}, "outputs": [], "source": [ "df_query(conn,\n", " \"\"\"\n", " WITH\n", "\t\tfilm_rental_counts AS\n", "\t\t(\n", " SELECT film_id,\n", " \t COUNT(rental_id) AS rental_count\n", " FROM film\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " GROUP BY film_id\n", " ORDER BY rental_count\n", " \t),\n", " min_max_rental_counts AS\n", " (\n", " \t\tSELECT MIN(rental_count) AS min_rental_count,\n", " MAX(rental_count) AS max_rental_count\n", " FROM film_rental_counts\n", " ),\n", " least_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS least_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = min_rental_count\n", " ),\n", " most_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS most_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = max_rental_count\n", " ),\n", " actors_of_least_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN least_rented_films\n", " WHERE film_id = least_rented_film_id\n", " ),\n", " actors_of_most_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN most_rented_films\n", " WHERE film_id = most_rented_film_id\n", " ),\n", " film_counts_of_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id, \n", " COUNT(film_id) AS film_count\n", " FROM actors_of_most_rented_films\n", " JOIN film_actor USING (actor_id)\n", " GROUP BY actor_id\n", " ORDER BY film_count DESC\n", " ),\n", " most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id\n", " FROM film_counts_of_actors_of_most_rented_films\n", " WHERE film_count = (\n", " SELECT MAX(film_count)\n", " FROM film_counts_of_actors_of_most_rented_films\n", " )\n", " ),\n", " all_films_of_most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id, film_id\n", " FROM most_popular_actors_of_most_rented_films\n", " JOIN film_actor USING (actor_id)\n", " ),\n", " values_of_all_actors_of_least_rented_films AS\n", " (\n", " SELECT actor_id, \n", " SUM(amount) AS values_of_all_least_rented_film_actors\n", " FROM actors_of_least_rented_films\n", " JOIN film_actor USING (actor_id)\n", " JOIN film USING (film_id)\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " JOIN payment USING (rental_id)\n", " GROUP BY actor_id\n", " ),\n", " values_of_most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id,\n", " SUM(amount) AS values_of_most_popular_most_rented_film_actors\n", " FROM most_popular_actors_of_most_rented_films\n", " JOIN film_actor USING (actor_id)\n", " JOIN film USING (film_id)\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " JOIN payment USING (rental_id)\n", " GROUP BY actor_id\n", " ),\n", " total_values_of_all_least_rented_film_actors AS\n", " (\n", " SELECT SUM(values_of_all_least_rented_film_actors) \n", " AS total_value_of_all_least_rented_film_actors\n", " FROM values_of_all_actors_of_least_rented_films\n", " )\n", " -- SELECT * FROM film_rental_counts\n", " -- SELECT * FROM min_max_rental_counts\n", " -- SELECT * FROM least_rented_films\n", " -- SELECT * FROM most_rented_films\n", " -- SELECT * FROM actors_of_least_rented_films\n", " -- SELECT * FROM actors_of_most_rented_films\n", " -- SELECT * FROM film_counts_of_actors_of_most_rented_films\n", " -- SELECT * FROM most_popular_actors_of_most_rented_films\n", " -- SELECT * FROM all_films_of_most_popular_actors_of_most_rented_films\n", " -- SELECT * FROM values_of_all_actors_of_least_rented_films ORDER BY actor_id\n", " SELECT * FROM total_values_of_all_least_rented_film_actors\n", " \"\"\"\n", ")" ] }, { "cell_type": "markdown", "id": "f4976554-3c13-4dcc-abf0-0ac549340d2f", "metadata": {}, "source": [ "#### The total value of the most popular actors of the most rented films." ] }, { "cell_type": "code", "execution_count": null, "id": "ff9fd11b-1e4d-4af4-ab79-556c7630669f", "metadata": {}, "outputs": [], "source": [ "df_query(conn,\n", " \"\"\"\n", " WITH\n", "\t\tfilm_rental_counts AS\n", "\t\t(\n", " SELECT film_id,\n", " \t COUNT(rental_id) AS rental_count\n", " FROM film\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " GROUP BY film_id\n", " ORDER BY rental_count\n", " \t),\n", " min_max_rental_counts AS\n", " (\n", " \t\tSELECT MIN(rental_count) AS min_rental_count,\n", " MAX(rental_count) AS max_rental_count\n", " FROM film_rental_counts\n", " ),\n", " least_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS least_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = min_rental_count\n", " ),\n", " most_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS most_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = max_rental_count\n", " ),\n", " actors_of_least_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN least_rented_films\n", " WHERE film_id = least_rented_film_id\n", " ),\n", " actors_of_most_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN most_rented_films\n", " WHERE film_id = most_rented_film_id\n", " ),\n", " film_counts_of_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id, \n", " COUNT(film_id) AS film_count\n", " FROM actors_of_most_rented_films\n", " JOIN film_actor USING (actor_id)\n", " GROUP BY actor_id\n", " ORDER BY film_count DESC\n", " ),\n", " most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id\n", " FROM film_counts_of_actors_of_most_rented_films\n", " WHERE film_count = (\n", " SELECT MAX(film_count)\n", " FROM film_counts_of_actors_of_most_rented_films\n", " )\n", " ),\n", " all_films_of_most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id, film_id\n", " FROM most_popular_actors_of_most_rented_films\n", " JOIN film_actor USING (actor_id)\n", " ),\n", " values_of_all_actors_of_least_rented_films AS\n", " (\n", " SELECT actor_id, \n", " SUM(amount) AS values_of_all_least_rented_film_actors\n", " FROM actors_of_least_rented_films\n", " JOIN film_actor USING (actor_id)\n", " JOIN film USING (film_id)\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " JOIN payment USING (rental_id)\n", " GROUP BY actor_id\n", " ),\n", " values_of_most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id,\n", " SUM(amount) AS values_of_most_popular_most_rented_film_actors\n", " FROM most_popular_actors_of_most_rented_films\n", " JOIN film_actor USING (actor_id)\n", " JOIN film USING (film_id)\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " JOIN payment USING (rental_id)\n", " GROUP BY actor_id\n", " ),\n", " total_values_of_all_least_rented_film_actors AS\n", " (\n", " SELECT SUM(values_of_all_least_rented_film_actors) \n", " AS total_value_of_all_least_rented_film_actors\n", " FROM values_of_all_actors_of_least_rented_films\n", " ),\n", " total_values_of_most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT SUM(values_of_most_popular_most_rented_film_actors) \n", " AS total_value_of_most_popular_most_rented_film_actors\n", " FROM values_of_most_popular_actors_of_most_rented_films\n", " )\n", " -- SELECT * FROM film_rental_counts\n", " -- SELECT * FROM min_max_rental_counts\n", " -- SELECT * FROM least_rented_films\n", " -- SELECT * FROM most_rented_films\n", " -- SELECT * FROM actors_of_least_rented_films\n", " -- SELECT * FROM actors_of_most_rented_films\n", " -- SELECT * FROM film_counts_of_actors_of_most_rented_films\n", " -- SELECT * FROM most_popular_actors_of_most_rented_films\n", " -- SELECT * FROM all_films_of_most_popular_actors_of_most_rented_films\n", " -- SELECT * FROM values_of_all_actors_of_least_rented_films ORDER BY actor_id\n", " -- SELECT * FROM total_values_of_all_least_rented_film_actors\n", " SELECT * FROM total_values_of_most_popular_actors_of_most_rented_films\n", " \"\"\"\n", ")" ] }, { "cell_type": "markdown", "id": "c1262b94-88a6-4400-9bea-3b76a69a992b", "metadata": {}, "source": [ "#### Comparison of the two total values." ] }, { "cell_type": "code", "execution_count": null, "id": "026c6cb1-0a45-4462-b2e0-949574d55816", "metadata": {}, "outputs": [], "source": [ "df_query(conn,\n", " \"\"\"\n", " WITH\n", "\t\tfilm_rental_counts AS\n", "\t\t(\n", " SELECT film_id,\n", " \t COUNT(rental_id) AS rental_count\n", " FROM film\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " GROUP BY film_id\n", " ORDER BY rental_count\n", " \t),\n", " min_max_rental_counts AS\n", " (\n", " \t\tSELECT MIN(rental_count) AS min_rental_count,\n", " MAX(rental_count) AS max_rental_count\n", " FROM film_rental_counts\n", " ),\n", " least_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS least_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = min_rental_count\n", " ),\n", " most_rented_films AS\n", " (\n", " SELECT rental_count,\n", " film_id AS most_rented_film_id\n", " FROM film_rental_counts\n", " JOIN min_max_rental_counts\n", " WHERE rental_count = max_rental_count\n", " ),\n", " actors_of_least_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN least_rented_films\n", " WHERE film_id = least_rented_film_id\n", " ),\n", " actors_of_most_rented_films AS\n", " (\n", " SELECT DISTINCT actor_id\n", " FROM film_actor \n", " JOIN most_rented_films\n", " WHERE film_id = most_rented_film_id\n", " ),\n", " film_counts_of_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id, \n", " COUNT(film_id) AS film_count\n", " FROM actors_of_most_rented_films\n", " JOIN film_actor USING (actor_id)\n", " GROUP BY actor_id\n", " ORDER BY film_count DESC\n", " ),\n", " most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id\n", " FROM film_counts_of_actors_of_most_rented_films\n", " WHERE film_count = (\n", " SELECT MAX(film_count)\n", " FROM film_counts_of_actors_of_most_rented_films\n", " )\n", " ),\n", " all_films_of_most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id, film_id\n", " FROM most_popular_actors_of_most_rented_films\n", " JOIN film_actor USING (actor_id)\n", " ),\n", " values_of_all_actors_of_least_rented_films AS\n", " (\n", " SELECT actor_id, \n", " SUM(amount) AS values_of_all_least_rented_film_actors\n", " FROM actors_of_least_rented_films\n", " JOIN film_actor USING (actor_id)\n", " JOIN film USING (film_id)\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " JOIN payment USING (rental_id)\n", " GROUP BY actor_id\n", " ),\n", " values_of_most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT actor_id,\n", " SUM(amount) AS values_of_most_popular_most_rented_film_actors\n", " FROM most_popular_actors_of_most_rented_films\n", " JOIN film_actor USING (actor_id)\n", " JOIN film USING (film_id)\n", " JOIN inventory USING (film_id)\n", " JOIN rental USING (inventory_id)\n", " JOIN payment USING (rental_id)\n", " GROUP BY actor_id\n", " ),\n", " total_values_of_all_least_rented_film_actors AS\n", " (\n", " SELECT SUM(values_of_all_least_rented_film_actors) \n", " AS total_value_of_all_least_rented_film_actors\n", " FROM values_of_all_actors_of_least_rented_films\n", " ),\n", " total_values_of_most_popular_actors_of_most_rented_films AS\n", " (\n", " SELECT SUM(values_of_most_popular_most_rented_film_actors) \n", " AS total_value_of_most_popular_most_rented_film_actors\n", " FROM values_of_most_popular_actors_of_most_rented_films\n", " ),\n", " comparison_of_values AS\n", " (\n", " SELECT CONCAT('$', FORMAT(total_value_of_all_least_rented_film_actors, 2))\n", " AS 'Total value of all actors of the least rented films',\n", " CONCAT('$', FORMAT(total_value_of_most_popular_most_rented_film_actors, 2))\n", " AS 'Total value of the most popular actors of the most rented films' \n", " FROM total_values_of_all_least_rented_film_actors\n", " JOIN total_values_of_most_popular_actors_of_most_rented_films\n", " )\n", " -- SELECT * FROM film_rental_counts\n", " -- SELECT * FROM min_max_rental_counts\n", " -- SELECT * FROM least_rented_films\n", " -- SELECT * FROM most_rented_films\n", " -- SELECT * FROM actors_of_least_rented_films\n", " -- SELECT * FROM actors_of_most_rented_films\n", " -- SELECT * FROM film_counts_of_actors_of_most_rented_films\n", " -- SELECT * FROM most_popular_actors_of_most_rented_films\n", " -- SELECT * FROM all_films_of_most_popular_actors_of_most_rented_films\n", " -- SELECT * FROM values_of_all_actors_of_least_rented_films ORDER BY actor_id\n", " -- SELECT * FROM total_values_of_all_least_rented_film_actors\n", " -- SELECT * FROM total_values_of_most_popular_actors_of_most_rented_films\n", " SELECT * FROM comparison_of_values\n", " \"\"\"\n", ")" ] }, { "cell_type": "code", "execution_count": null, "id": "89c2d1b2-14bd-4ac9-9d57-771ee9a36da6", "metadata": {}, "outputs": [], "source": [ "conn.close()" ] }, { "cell_type": "code", "execution_count": null, "id": "51e7b6b9-8987-4686-88e3-edd5d9f6497f", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.12.4" } }, "nbformat": 4, "nbformat_minor": 5 }