{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "0c989694-16f7-4817-9790-0527097f0164",
   "metadata": {},
   "source": [
    "## PROBLEM 4 (30 points)\n",
    "### Who is the most popular actor in each country? The most popular actor of a country is the actor who was in the most films rented by customers from that country. There could be ties. For each country, display in a dataframe the country name, the highest actor film count of the country, and the name(s) of the most popular actor(s) of the country. If a country does not have a favorite actor, show \"None\" as the actor name. Only display results for countries whose names start with the letter 'A', and sort by country name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0fe8dc78-f617-4715-ae98-c7d7bbbc630f",
   "metadata": {},
   "outputs": [],
   "source": [
    "from data201 import db_connection, df_query\n",
    "conn = db_connection(config_file='sakila.ini')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae27f4f7-7159-411c-b67a-4628a7f353ba",
   "metadata": {},
   "source": [
    "#### Countries whose names start with the letter 'A'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9960d8f4-7010-4992-9c49-c96fd69d01bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn, \n",
    "    \"\"\"\n",
    "    WITH \n",
    "        countries AS\n",
    "        (\n",
    "            SELECT country_id, country \n",
    "            FROM country\n",
    "            WHERE country LIKE 'A%'\n",
    "        )\n",
    "    SELECT * FROM countries ORDER BY country\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fdc95d1d-84ad-40b8-885d-c7a0539fcd9d",
   "metadata": {},
   "source": [
    "#### Customers' countries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a88c541e-727b-4ccf-ac6d-f6077f386dc8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn, \n",
    "    \"\"\"\n",
    "    WITH \n",
    "        countries AS\n",
    "        (\n",
    "            SELECT country_id, country \n",
    "            FROM country\n",
    "            WHERE country LIKE 'A%'\n",
    "        ),\n",
    "        customer_countries AS\n",
    "        (\n",
    "\t\t\tSELECT country, customer_id\n",
    "            FROM customer\n",
    "            JOIN address USING (address_id)\n",
    "            JOIN city USING (city_id)\n",
    "            JOIN countries USING (country_id)\n",
    "        )\n",
    "    -- SELECT * FROM countries ORDER BY country\n",
    "    SELECT * FROM customer_countries ORDER BY country\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fce86cd6-18c7-4ab5-a312-600fa8a8dd25",
   "metadata": {},
   "source": [
    "#### Customers of countries whose names start with the letter 'A'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc4bffbc-9ffc-4c35-a3e5-795e64091cbd",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn, \n",
    "    \"\"\"\n",
    "    WITH \n",
    "        countries AS\n",
    "        (\n",
    "            SELECT country_id, country \n",
    "            FROM country\n",
    "            WHERE country LIKE 'A%'\n",
    "        ),\n",
    "        customer_countries AS\n",
    "        (\n",
    "\t\t\tSELECT country, customer_id\n",
    "            FROM customer\n",
    "            JOIN address USING (address_id)\n",
    "            JOIN city USING (city_id)\n",
    "            JOIN countries USING (country_id)\n",
    "        ),\n",
    "        customers_by_country AS\n",
    "        (\n",
    "            SELECT country, customer_id\n",
    "            FROM countries\n",
    "            LEFT OUTER JOIN customer_countries USING (country)\n",
    "        )\n",
    "    -- SELECT * FROM countries ORDER BY country\n",
    "    -- SELECT * FROM customer_countries ORDER BY country\n",
    "    SELECT * FROM customers_by_country ORDER BY country\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fab955e0-6614-4666-9fc6-0953d51ec13f",
   "metadata": {},
   "source": [
    "#### Films rented by those customers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aca8ffb6-9ce0-421b-9e5b-a8ae359e6a1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn, \n",
    "    \"\"\"\n",
    "    WITH \n",
    "        countries AS\n",
    "        (\n",
    "            SELECT country_id, country \n",
    "            FROM country\n",
    "            WHERE country LIKE 'A%'\n",
    "        ),\n",
    "        customer_countries AS\n",
    "        (\n",
    "\t\t\tSELECT country, customer_id\n",
    "            FROM customer\n",
    "            JOIN address USING (address_id)\n",
    "            JOIN city USING (city_id)\n",
    "            JOIN countries USING (country_id)\n",
    "        ),\n",
    "        customers_by_country AS\n",
    "        (\n",
    "            SELECT country, customer_id\n",
    "            FROM countries\n",
    "            LEFT OUTER JOIN customer_countries USING (country)\n",
    "        ),\n",
    "        rented_films_by_country AS\n",
    "        (\n",
    "            SELECT country, film_id\n",
    "            FROM customers_by_country\n",
    "            LEFT OUTER JOIN rental USING (customer_id)\n",
    "            LEFT OUTER JOIN inventory USING (inventory_id)\n",
    "        )\n",
    "    -- SELECT * FROM countries ORDER BY country\n",
    "    -- SELECT * FROM customer_countries ORDER BY country\n",
    "    -- SELECT * FROM customers_by_country ORDER BY country\n",
    "    SELECT * FROM rented_films_by_country ORDER BY country\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d0c650c-652f-4bbb-846a-67f117e33a9f",
   "metadata": {},
   "source": [
    "#### Actors in those rented films."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ff5d96e-5e3c-4207-ae0b-9381e9d1d292",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn, \n",
    "    \"\"\"\n",
    "    WITH \n",
    "        countries AS\n",
    "        (\n",
    "            SELECT country_id, country \n",
    "            FROM country\n",
    "            WHERE country LIKE 'A%'\n",
    "        ),\n",
    "        customer_countries AS\n",
    "        (\n",
    "\t\t\tSELECT country, customer_id\n",
    "            FROM customer\n",
    "            JOIN address USING (address_id)\n",
    "            JOIN city USING (city_id)\n",
    "            JOIN countries USING (country_id)\n",
    "        ),\n",
    "        customers_by_country AS\n",
    "        (\n",
    "            SELECT country, customer_id\n",
    "            FROM countries\n",
    "            LEFT OUTER JOIN customer_countries USING (country)\n",
    "        ),\n",
    "        rented_films_by_country AS\n",
    "        (\n",
    "            SELECT country, film_id\n",
    "            FROM customers_by_country\n",
    "            LEFT OUTER JOIN rental USING (customer_id)\n",
    "            LEFT OUTER JOIN inventory USING (inventory_id)\n",
    "        ),\n",
    "        actors_in_rented_films_by_country AS\n",
    "        (\n",
    "            SELECT country, film_id, actor_id\n",
    "            FROM rented_films_by_country\n",
    "            LEFT OUTER JOIN film USING (film_id)\n",
    "            LEFT OUTER JOIN film_actor USING (film_id)\n",
    "        )\n",
    "    -- SELECT * FROM countries ORDER BY country\n",
    "    -- SELECT * FROM customer_countries ORDER BY country\n",
    "    -- SELECT * FROM customers_by_country ORDER BY country\n",
    "    -- SELECT * FROM rented_films_by_country ORDER BY country\n",
    "    SELECT * FROM actors_in_rented_films_by_country ORDER BY country\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6c39b0b-681f-4e45-9190-a26f7d50cf4d",
   "metadata": {},
   "source": [
    "#### Count of films by actors by country."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "87de729d-b33e-4bff-ae6c-f0d6e2c787f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn, \n",
    "    \"\"\"\n",
    "    WITH \n",
    "        countries AS\n",
    "        (\n",
    "            SELECT country_id, country \n",
    "            FROM country\n",
    "            WHERE country LIKE 'A%'\n",
    "        ),\n",
    "        customer_countries AS\n",
    "        (\n",
    "\t\t\tSELECT country, customer_id\n",
    "            FROM customer\n",
    "            JOIN address USING (address_id)\n",
    "            JOIN city USING (city_id)\n",
    "            JOIN countries USING (country_id)\n",
    "        ),\n",
    "        customers_by_country AS\n",
    "        (\n",
    "            SELECT country, customer_id\n",
    "            FROM countries\n",
    "            LEFT OUTER JOIN customer_countries USING (country)\n",
    "        ),\n",
    "        rented_films_by_country AS\n",
    "        (\n",
    "            SELECT country, film_id\n",
    "            FROM customers_by_country\n",
    "            LEFT OUTER JOIN rental USING (customer_id)\n",
    "            LEFT OUTER JOIN inventory USING (inventory_id)\n",
    "        ),\n",
    "        actors_in_rented_films_by_country AS\n",
    "        (\n",
    "            SELECT country, film_id, actor_id\n",
    "            FROM rented_films_by_country\n",
    "            LEFT OUTER JOIN film USING (film_id)\n",
    "            LEFT OUTER JOIN film_actor USING (film_id)\n",
    "        ),\n",
    "        count_of_films_by_actor_by_country AS\n",
    "        (\n",
    "\t\t\tSELECT country, actor_id,\n",
    "                   COUNT(film_id) AS film_count\n",
    "\t\t\tFROM actors_in_rented_films_by_country\n",
    "            GROUP BY country, actor_id\n",
    "        )\n",
    "    -- SELECT * FROM countries ORDER BY country\n",
    "    -- SELECT * FROM customer_countries ORDER BY country\n",
    "    -- SELECT * FROM customers_by_country ORDER BY country\n",
    "    -- SELECT * FROM rented_films_by_country ORDER BY country\n",
    "    -- SELECT * FROM actors_in_rented_films_by_country ORDER BY country\n",
    "    SELECT * FROM count_of_films_by_actor_by_country ORDER BY country, film_count DESC\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b9da7f6-d560-4d42-b9a7-08869aa790f0",
   "metadata": {},
   "source": [
    "#### Maximum film count by actors by country."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b86ed512-959b-4340-a3c4-37ba3b352424",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn, \n",
    "    \"\"\"\n",
    "    WITH \n",
    "        countries AS\n",
    "        (\n",
    "            SELECT country_id, country \n",
    "            FROM country\n",
    "            WHERE country LIKE 'A%'\n",
    "        ),\n",
    "        customer_countries AS\n",
    "        (\n",
    "\t\t\tSELECT country, customer_id\n",
    "            FROM customer\n",
    "            JOIN address USING (address_id)\n",
    "            JOIN city USING (city_id)\n",
    "            JOIN countries USING (country_id)\n",
    "        ),\n",
    "        customers_by_country AS\n",
    "        (\n",
    "            SELECT country, customer_id\n",
    "            FROM countries\n",
    "            LEFT OUTER JOIN customer_countries USING (country)\n",
    "        ),\n",
    "        rented_films_by_country AS\n",
    "        (\n",
    "            SELECT country, film_id\n",
    "            FROM customers_by_country\n",
    "            LEFT OUTER JOIN rental USING (customer_id)\n",
    "            LEFT OUTER JOIN inventory USING (inventory_id)\n",
    "        ),\n",
    "        actors_in_rented_films_by_country AS\n",
    "        (\n",
    "            SELECT country, film_id, actor_id\n",
    "            FROM rented_films_by_country\n",
    "            LEFT OUTER JOIN film USING (film_id)\n",
    "            LEFT OUTER JOIN film_actor USING (film_id)\n",
    "        ),\n",
    "        count_of_films_by_actor_by_country AS\n",
    "        (\n",
    "\t\t\tSELECT country, actor_id,\n",
    "                   COUNT(film_id) AS film_count\n",
    "\t\t\tFROM actors_in_rented_films_by_country\n",
    "            GROUP BY country, actor_id\n",
    "        ),\n",
    "        max_count_of_films_by_actor_by_country AS\n",
    "        (\n",
    "            SELECT country, \n",
    "                   MAX(film_count) AS max_film_count\n",
    "            FROM count_of_films_by_actor_by_country\n",
    "            GROUP BY COUNTRY\n",
    "        )\n",
    "    -- SELECT * FROM countries ORDER BY country\n",
    "    -- SELECT * FROM customer_countries ORDER BY country\n",
    "    -- SELECT * FROM customers_by_country ORDER BY country\n",
    "    -- SELECT * FROM rented_films_by_country ORDER BY country\n",
    "    -- SELECT * FROM actors_in_rented_films_by_country ORDER BY country\n",
    "    -- SELECT * FROM count_of_films_by_actor_by_country ORDER BY country, film_count DESC\n",
    "    SELECT * FROM max_count_of_films_by_actor_by_country ORDER BY country\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f06f803d-f032-46a4-aa60-e448a863cf76",
   "metadata": {},
   "source": [
    "#### Actors of each country whose film counts match the maximum film count."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "689c76e4-9dbd-469c-900a-da5958c2aaa6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn, \n",
    "    \"\"\"\n",
    "    WITH \n",
    "        countries AS\n",
    "        (\n",
    "            SELECT country_id, country \n",
    "            FROM country\n",
    "            WHERE country LIKE 'A%'\n",
    "        ),\n",
    "        customer_countries AS\n",
    "        (\n",
    "\t\t\tSELECT country, customer_id\n",
    "            FROM customer\n",
    "            JOIN address USING (address_id)\n",
    "            JOIN city USING (city_id)\n",
    "            JOIN countries USING (country_id)\n",
    "        ),\n",
    "        customers_by_country AS\n",
    "        (\n",
    "            SELECT country, customer_id\n",
    "            FROM countries\n",
    "            LEFT OUTER JOIN customer_countries USING (country)\n",
    "        ),\n",
    "        rented_films_by_country AS\n",
    "        (\n",
    "            SELECT country, film_id\n",
    "            FROM customers_by_country\n",
    "            LEFT OUTER JOIN rental USING (customer_id)\n",
    "            LEFT OUTER JOIN inventory USING (inventory_id)\n",
    "        ),\n",
    "        actors_in_rented_films_by_country AS\n",
    "        (\n",
    "            SELECT country, film_id, actor_id\n",
    "            FROM rented_films_by_country\n",
    "            LEFT OUTER JOIN film USING (film_id)\n",
    "            LEFT OUTER JOIN film_actor USING (film_id)\n",
    "        ),\n",
    "        count_of_films_by_actor_by_country AS\n",
    "        (\n",
    "\t\t\tSELECT country, actor_id,\n",
    "                   COUNT(film_id) AS film_count\n",
    "\t\t\tFROM actors_in_rented_films_by_country\n",
    "            GROUP BY country, actor_id\n",
    "        ),\n",
    "        max_count_of_films_by_actor_by_country AS\n",
    "        (\n",
    "            SELECT country, \n",
    "                   MAX(film_count) AS max_film_count\n",
    "            FROM count_of_films_by_actor_by_country\n",
    "            GROUP BY COUNTRY\n",
    "        ),\n",
    "        actor_names AS\n",
    "        (\n",
    "            SELECT actor_id,\n",
    "                   CONCAT(actor.first_name, ' ', actor.last_name) \n",
    "                       AS actor_name\n",
    "            FROM actor\n",
    "            ORDER BY actor_id\n",
    "        ),\n",
    "        most_popular_actors_by_country AS\n",
    "        (\n",
    "            SELECT country, max_film_count, \n",
    "                   actor_name AS most_popular_actors\n",
    "            FROM count_of_films_by_actor_by_country\n",
    "            JOIN max_count_of_films_by_actor_by_country USING (country)\n",
    "            LEFT OUTER JOIN actor_names USING (actor_id)\n",
    "            WHERE film_count = max_film_count\n",
    "        )\n",
    "    -- SELECT * FROM countries ORDER BY country\n",
    "    -- SELECT * FROM customer_countries ORDER BY country\n",
    "    -- SELECT * FROM customers_by_country ORDER BY country\n",
    "    -- SELECT * FROM rented_films_by_country ORDER BY country\n",
    "    -- SELECT * FROM actors_in_rented_films_by_country ORDER BY country\n",
    "    -- SELECT * FROM count_of_films_by_actor_by_country ORDER BY country, film_count DESC\n",
    "    -- SELECT * FROM max_count_of_films_by_actor_by_country ORDER BY country\n",
    "    SELECT * FROM most_popular_actors_by_country ORDER BY country\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7f41a8e-bcfc-491f-85fa-ce2ad4e1e9fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  }
 ],
 "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
}