{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "902ab1f1-3432-4f37-aea7-e12bf5292dfc",
   "metadata": {},
   "source": [
    "## PROBLEM 3 (20 points)\n",
    "### Each film can be rented multiple times by a store, and each rental earns a payment amount. For each store, what is the average total revenue it received for each film that it has rented, based on rental payments? Display store ids and the averages formatted as monetary amounts, sorted by store id."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27d769f6-9707-41f2-b1ff-8965e9292966",
   "metadata": {},
   "outputs": [],
   "source": [
    "from data201 import db_connection, df_query\n",
    "conn = db_connection(config_file='sakila.ini')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5f6346e-92ad-4c67-9714-b796685feb0b",
   "metadata": {},
   "source": [
    "### Solution using CTEs.\n",
    "#### Total rental revenue of each film, by store."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "05e3bc4a-5b93-42d2-a54f-7ea6a82ff145",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    WITH\n",
    "        film_revenues_by_store AS\n",
    "        (\n",
    "            SELECT store_id, film_id, \n",
    "                   SUM(amount) AS total_film_revenue\n",
    "            FROM rental\n",
    "            JOIN payment USING (rental_id)\n",
    "            JOIN inventory USING (inventory_id)\n",
    "            GROUP BY store_id, film_id\n",
    "            ORDER BY store_id\n",
    "        )\n",
    "    SELECT * FROM film_revenues_by_store ORDER BY store_id\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "907cf79d-a7bf-4611-86c9-90463c90fc69",
   "metadata": {},
   "source": [
    "#### Average of film revenues, by store."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1019f1da-05f9-483e-a6dc-290a04d10cae",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    WITH\n",
    "        film_revenues_by_store AS\n",
    "        (\n",
    "            SELECT store_id, film_id, \n",
    "                   SUM(amount) AS total_film_revenue\n",
    "            FROM rental\n",
    "            JOIN payment USING (rental_id)\n",
    "            JOIN inventory USING (inventory_id)\n",
    "            GROUP BY store_id, film_id\n",
    "        ),\n",
    "        average_film_revenues_by_store AS\n",
    "        (\n",
    "            SELECT store_id,\n",
    "                   CONCAT('$',\n",
    "                          FORMAT(AVG(total_film_revenue), 2)) \n",
    "                      AS average_film_revenue\n",
    "            FROM film_revenues_by_store\n",
    "            GROUP BY store_id\n",
    "        )\n",
    "    -- SELECT * FROM film_revenues_by_store ORDER BY store_id\n",
    "    SELECT * FROM average_film_revenues_by_store ORDER BY store_id\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff3d5a64-d79c-4542-909c-a5296602ae64",
   "metadata": {},
   "source": [
    "### Solution using a nested `SELECT` to calculate the total film revenues."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "692ca9c9-4861-4b64-a2db-4c5bb0e9d00d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT store_id,\n",
    "           CONCAT('$', FORMAT(AVG(total_film_revenue), 2))\n",
    "               AS average_film_revenue\n",
    "    FROM (\n",
    "        SELECT store_id, film_id,\n",
    "               SUM(amount) AS total_film_revenue\n",
    "        FROM rental\n",
    "        JOIN payment USING (rental_id)\n",
    "        JOIN inventory USING (inventory_id)\n",
    "        GROUP BY store_id, film_id\n",
    "    ) AS film_revenue_sums\n",
    "    GROUP BY store_id\n",
    "    ORDER BY store_id\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2da9f621-195a-4b3c-ba68-e01ff8171ac4",
   "metadata": {},
   "source": [
    "### **Why is the following solution wrong?**\n",
    "#### The number of films that each store rented."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88349e4f-c1d6-4948-9ba4-b124bff1a4f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    WITH\n",
    "        film_counts AS\n",
    "        (\n",
    "            SELECT DISTINCT store_id, \n",
    "                   COUNT(DISTINCT film_id) AS film_count\n",
    "            FROM inventory\n",
    "            JOIN film USING (film_id)\n",
    "            GROUP BY store_id\n",
    "        )\n",
    "    SELECT * FROM film_counts ORDER BY store_id\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e1c03ac-353c-4845-8162-861b188b5275",
   "metadata": {},
   "source": [
    "#### The payment of each rental."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a44d3071-62a9-4734-abf2-01b51b4b01d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    WITH\n",
    "        film_counts AS\n",
    "        (\n",
    "            SELECT DISTINCT store_id, \n",
    "                   COUNT(DISTINCT film_id) AS film_count\n",
    "            FROM inventory\n",
    "            JOIN film USING (film_id)\n",
    "            GROUP BY store_id\n",
    "        ),\n",
    "        rental_payments AS\n",
    "        (\n",
    "            SELECT store_id, rental_id, r.staff_id, amount\n",
    "            FROM rental r\n",
    "            JOIN staff USING (staff_id)\n",
    "            JOIN payment USING (rental_id)\n",
    "            ORDER BY store_id\n",
    "        )\n",
    "    -- SELECT * FROM film_counts ORDER BY store_id\n",
    "    SELECT * FROM rental_payments ORDER BY store_id\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc980d58-da6a-4d08-ad2e-0d3dd3160346",
   "metadata": {},
   "source": [
    "#### The total revenue of each store."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a949277-61f8-4a1f-8777-8b9f3791708d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    WITH\n",
    "        film_counts AS\n",
    "        (\n",
    "            SELECT DISTINCT store_id, \n",
    "                   COUNT(DISTINCT film_id) AS film_count\n",
    "            FROM inventory\n",
    "            JOIN film USING (film_id)\n",
    "            GROUP BY store_id\n",
    "        ),\n",
    "        rental_payments AS\n",
    "        (\n",
    "            SELECT store_id, rental_id, r.staff_id, amount\n",
    "            FROM rental r\n",
    "            JOIN staff USING (staff_id)\n",
    "            JOIN payment USING (rental_id)\n",
    "            ORDER BY store_id\n",
    "        ),\n",
    "        total_revenues AS\n",
    "        (\n",
    "            SELECT store_id, \n",
    "                   SUM(amount) AS total_revenue\n",
    "            FROM rental_payments\n",
    "            GROUP BY store_id\n",
    "        )\n",
    "    -- SELECT * FROM film_counts ORDER BY store_id\n",
    "    -- SELECT * FROM rental_rates ORDER BY store_id\n",
    "    SELECT * FROM total_revenues ORDER BY store_id\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49cfe14d-a7f9-4cdf-8e1b-2129ff90b9d9",
   "metadata": {},
   "source": [
    "#### Store averages: Total revenue / number of rentals."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e408881e-6088-4466-a32e-c39b7698764a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    WITH\n",
    "        film_counts AS\n",
    "        (\n",
    "            SELECT DISTINCT store_id, \n",
    "                   COUNT(DISTINCT film_id) AS film_count\n",
    "            FROM inventory\n",
    "            JOIN film USING (film_id)\n",
    "            GROUP BY store_id\n",
    "        ),\n",
    "        rental_payments AS\n",
    "        (\n",
    "            SELECT store_id, rental_id, r.staff_id, amount\n",
    "            FROM rental r\n",
    "            JOIN staff USING (staff_id)\n",
    "            JOIN payment USING (rental_id)\n",
    "            ORDER BY store_id\n",
    "        ),\n",
    "        total_revenues AS\n",
    "        (\n",
    "            SELECT store_id, \n",
    "                   SUM(amount) AS total_revenue\n",
    "            FROM rental_payments\n",
    "            GROUP BY store_id\n",
    "        ),\n",
    "        film_averages AS\n",
    "        (\n",
    "            SELECT store_id, \n",
    "                   CONCAT('$', FORMAT(total_revenue/film_count, 2))\n",
    "                       AS film_average\n",
    "            FROM total_revenues\n",
    "            JOIN film_counts USING (store_id)\n",
    "        )\n",
    "    -- SELECT * FROM film_counts ORDER BY store_id\n",
    "    -- SELECT * FROM rental_rates ORDER BY store_id\n",
    "    -- SELECT * FROM total_revenues ORDER BY store_id\n",
    "    SELECT * FROM film_averages ORDER BY store_id\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc42a684-c483-4cdf-b93c-b09b5abfe937",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cef67096-c315-4a49-b75b-5dd8a1d52534",
   "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
}