Project_Zuber_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           Date and time Temperature       Description\n",
      "0    2017-11-01 00:00:00     276.150     broken clouds\n",
      "1    2017-11-01 01:00:00     275.700  scattered clouds\n",
      "2    2017-11-01 02:00:00     275.610   overcast clouds\n",
      "3    2017-11-01 03:00:00     275.350     broken clouds\n",
      "4    2017-11-01 04:00:00     275.240     broken clouds\n",
      "..                   ...         ...               ...\n",
      "692  2017-11-29 20:00:00     281.340        few clouds\n",
      "693  2017-11-29 21:00:00     281.690      sky is clear\n",
      "694  2017-11-29 22:00:00     281.070        few clouds\n",
      "695  2017-11-29 23:00:00     280.060      sky is clear\n",
      "696  2017-11-30 00:00:00     278.460      sky is clear\n",
      "\n",
      "[697 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "from bs4 import BeautifulSoup\n",
    "import requests\n",
    "import pandas as pd\n",
    "\n",
    "URL = 'https://practicum-content.s3.us-west-1.amazonaws.com/data-analyst-eng/moved_chicago_weather_2017.html'\n",
    "req = requests.get(URL)\n",
    "soup = BeautifulSoup(req.text,'html.parser')\n",
    "\n",
    "# print(soup)\n",
    "\n",
    "table = soup.find(\"table\", attrs={\"id\":\"weather_records\"})\n",
    "# print(table)\n",
    "\n",
    "heading_table = []\n",
    "content = []\n",
    "\n",
    "for row in table.find_all(\"th\"):\n",
    "    heading_table.append(row.text)\n",
    "# print(heading_table)\n",
    "\n",
    "for row in table.find_all(\"tr\"):\n",
    "    if not row.find_all(\"th\"):\n",
    "        content.append([element.text for element in row.find_all(\"td\")])\n",
    "# print(content)\n",
    "\n",
    "weather_records = pd.DataFrame(content, columns=heading_table)\n",
    "print(weather_records)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "-- 1.Imprima o campo company_name. \n",
    "-- Encontre o número de corridas de táxi para cada empresa de táxi de 15 a 16 de novembro de 2017, \n",
    "-- nomeie o campo resultante como trips_amount e imprima-o também.\n",
    "--  Classifique os resultados pelo campo trips_amount em ordem decrescente.\n",
    "\n",
    "SELECT\n",
    "    cabs.company_name,\n",
    "    COUNT(DISTINCT trips.trip_id) AS trips_amount\n",
    "FROM\n",
    "    cabs\n",
    "INNER JOIN trips ON trips.cab_id = cabs.cab_id\n",
    "WHERE\n",
    "    CAST(trips.start_ts AS date) >= '2017-11-15'\n",
    "    AND CAST(trips.start_ts AS date) <= '2017-11-16'\n",
    "GROUP BY\n",
    "    cabs.company_name\n",
    "ORDER BY\n",
    "    COUNT(DISTINCT trips.trip_id) DESC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "-- 2. Encontre o número de corridas para cada empresa de táxi cujo nome contém as palavras \"Amarelo\" ou \"Azul\" de 1 a 7 de novembro de 2017. \n",
    "-- Nomeie a variável resultante como trips_amount. Agrupe os resultados pelo campo company_name.\n",
    "\n",
    "SELECT\n",
    "    cabs.company_name as company_name,\n",
    "    COUNT(DISTINCT trips.trip_id) as trips_amount\n",
    "FROM\n",
    "    cabs\n",
    "INNER JOIN\n",
    "    trips ON trips.cab_id = cabs.cab_id\n",
    "WHERE\n",
    "    CAST(trips.start_ts AS date) BETWEEN '2017-11-01' AND '2017-11-07'\n",
    "    AND cabs.company_name like '%%Yellow%%'\n",
    "GROUP BY\n",
    "    cabs.company_name\n",
    "UNION ALL\n",
    "SELECT\n",
    "    cabs.company_name as company_name,\n",
    "    COUNT(DISTINCT trips.trip_id) as trips_amount\n",
    "FROM\n",
    "    cabs\n",
    "INNER JOIN\n",
    "    trips ON trips.cab_id = cabs.cab_id\n",
    "WHERE\n",
    "    CAST(trips.start_ts AS date) BETWEEN '2017-11-01' AND '2017-11-07'\n",
    "    AND cabs.company_name like '%%Blue%%'\n",
    "GROUP BY\n",
    "    cabs.company_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "-- 3. De 1 a 7 de novembro de 2017, as empresas de táxi mais populares foram Flash Cab e Taxi Affiliation Services. \n",
    "-- Encontre o número de corridas para essas duas empresas e nomeie a variável resultante como trips_amount. \n",
    "-- Junte as corridas de todas as outras empresas no grupo \"Other\". \n",
    "-- Agrupe os dados por nomes de empresas de táxi. \n",
    "-- Nomeie o campo com os nomes das empresas de táxi company. \n",
    "-- Ordene o resultado em ordem decrescente por trips_amount.\n",
    "\n",
    "SELECT\n",
    "    CASE\n",
    "        WHEN cabs.company_name = 'Flash Cab' THEN 'Flash Cab'\n",
    "        WHEN cabs.company_name = 'Taxi Affiliation Services' THEN 'Taxi Affiliation Services'\n",
    "    ELSE\n",
    "        'Other'\n",
    "    END AS company,\n",
    "    COUNT(trips.trip_id) as trips_amount\n",
    "FROM\n",
    "    cabs\n",
    "INNER JOIN\n",
    "    trips \n",
    "ON  \n",
    "    trips.cab_id = cabs.cab_id\n",
    "WHERE\n",
    "    CAST(trips.start_ts AS date) BETWEEN '2017-11-01' AND '2017-11-07'\n",
    "GROUP BY\n",
    "    company\n",
    "ORDER BY\n",
    "    trips_amount DESC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "-- 4. Recupere os identificadores dos bairros O'Hare e Loop da tabela neighborhoods.\n",
    "\n",
    "SELECT\n",
    "    name,\n",
    "    neighborhood_id\n",
    "FROM neighborhoods\n",
    "WHERE \n",
    "    neighborhoods.name like '%%Hare%%'\n",
    "    OR neighborhoods.name = 'Loop'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "-- 5. Para cada hora, recupere os registros de condições climáticas da tabela weather_records. \n",
    "-- Usando o operador CASE, divida todas as horas em dois grupos: \n",
    "-- Bad se o campo descrição contiver as palavras rain ou storm e Good para outros. \n",
    "-- Nomeie o campo resultante como weather_conditions. \n",
    "-- A tabela final deve incluir dois campos: data e hora (ts) e weather_conditions.\n",
    "\n",
    "SELECT\n",
    "    weather_records.ts as ts,\n",
    "    CASE\n",
    "        WHEN weather_records.description like '%rain%' OR weather_records.description like '%storm%' THEN 'Bad'\n",
    "        ELSE 'Good'\n",
    "        END AS weather_conditions\n",
    "FROM\n",
    "    weather_records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "-- 6. Recupere da tabela trips todas as corridas que começaram no Loop (pickup_location_id: 50) em um sábado e\n",
    "-- terminaram em O'Hare (dropoff_location_id: 63). Obtenha as condições meteorológicas para cada corrida. \n",
    "-- Use o método que você aplicou na tarefa anterior. Além disso, recupere a duração de cada corrida. \n",
    "-- Ignore corridas para as quais os dados sobre as condições meteorológicas não estão disponíveis.\n",
    "\n",
    "-- As colunas da tabela devem estar na seguinte ordem:\n",
    "\n",
    "-- start_ts\n",
    "-- weather_conditions\n",
    "-- duration_seconds\n",
    "-- Ordene por trip_id.\n",
    "\n",
    "SELECT\n",
    "    trips.start_ts AS start_ts,\n",
    "    SUBQ.weather_conditions AS weather_conditions,\n",
    "    trips.duration_seconds AS duration_seconds\n",
    "FROM\n",
    "    trips\n",
    "INNER JOIN\n",
    "    (SELECT\n",
    "    weather_records.ts as ts,\n",
    "    CASE\n",
    "        WHEN weather_records.description like '%rain%' OR weather_records.description like '%storm%' THEN 'Bad'\n",
    "        ELSE 'Good'\n",
    "    END AS weather_conditions\n",
    "FROM\n",
    "    weather_records) AS SUBQ ON SUBQ.ts = trips.start_ts\n",
    "WHERE\n",
    "    trips.pickup_location_id = 50\n",
    "    AND trips.dropoff_location_id = 63\n",
    "    AND EXTRACT(DOW FROM trips.start_ts) = 6\n",
    "ORDER BY\n",
    "    trip_id"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}