Profissao_Analista_de_dados_M32_Exercicio.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/Armanskij/EBAC_Studies/blob/main/Profissao_Analista_de_dados_M32_Exercicio.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KJqp9AANOCtf"
      },
      "source": [
        "<img src=\"https://raw.githubusercontent.com/andre-marcos-perez/ebac-course-utils/main/media/logo/newebac_logo_black_half.png\" alt=\"ebac-logo\">\n",
        "\n",
        "---\n",
        "\n",
        "# **Módulo** | SQL: Filtrando & Seleção Condicional\n",
        "Caderno de **Exercícios**<br>\n",
        "Professor [Mariane Neiva](https://www.linkedin.com/in/mariane-neiva/)\n",
        "<br>\n",
        "Elaborado por [Douglas Rizzi](https://www.linkedin.com/in/douglas-rizzi/)\n",
        "\n",
        "\n",
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d9jDtUbDOE1-"
      },
      "source": [
        "# **Tópicos**\n",
        "\n",
        "<ol type=\"1\">\n",
        "  <li>Filtros and, or, in e between;</li>\n",
        "  <li>Filtros like e wildcards;</li>\n",
        "  <li>Seleção condicional;</li>\n",
        "</ol>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SmoHgt-lwkpD"
      },
      "source": [
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DIlQOznoTPQF"
      },
      "source": [
        "# **Instruções**\n",
        "\n",
        "Nessa **atividades**, você deve:\n",
        "\n",
        " 1. Executar a consulta SQL fornecida;\n",
        " 2. Exportar os resultados em um arquivo csv para sua máquina;\n",
        " 3. Renomear o arquivo como **query\\<numero-da-query\\>.csv**;\n",
        " 4. Enviar para a avaliação do tutor na plataforma da EBAC.\n",
        "\n",
        "> **Atenção**: Substitua o **\\<numero-da-query>** numero da consulta, exemplo: **query1.csv**.\n",
        "\n",
        "**Importante**: Para consultar mais detalhes da AWS Athena [clique aqui](https://docs.aws.amazon.com/athena/latest/ug/what-is.html)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vvlBORqbTPQp"
      },
      "source": [
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "k4HJwfbQcEQw"
      },
      "source": [
        "# Atividades"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ym_TsGX5_6Nd"
      },
      "source": [
        "#1. Prática 1\n",
        "\n",
        "> **Não há necessidade de entrega**.\n",
        "Para esses exercícios, utilizaremos os mesmo dados do módulo 3.\n",
        "\n",
        "Caso você não tenha as informações de **transacoes.csv**, elas estarão disponíveis no material de aula.\n",
        "\n",
        "Siga os seguintes passos para criação da tabela (desconsidere caso você já tenha os dados no seu S3):\n",
        "\n",
        "* Crie uma pasta bucket-transacoes no seu S3 e carregue o arquivo **transacoes.csv**\n",
        "* Volta para o AWS Athena e execute o seguinte comando:\n",
        "\n",
        "```sql\n",
        "CREATE EXTERNAL TABLE transacoes(\n",
        "id_cliente BIGINT,\n",
        "id_transacao BIGINT,\n",
        "valor FLOAT,\n",
        "id_loja STRING)\n",
        "ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'\n",
        "WITH SERDEPROPERTIES ('separatorChar' = ',', 'quoteChar' = '\"', 'escapeChar' = '\\\\')\n",
        "STORED AS TEXTFILE\n",
        "LOCATION 's3://bucket-transacoes/'\n",
        "```\n",
        "\n",
        "Utilizaremos a tabela para os exercícios seguintes."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qDxe2l4cMcVa"
      },
      "source": [
        "# **2. Selecionando dados**\n",
        "\n",
        "Vamos testar os comandos and, or, in e between?! Execute as queries e salve o resultado para enviar os exercícios.   \n",
        "\n",
        "##2.1 Query 1\n",
        "Selecione da tabela transacoes todas as colunas, e retorne as linhas onde o valor for maior que 30 e que o `id_loja` seja 'magalu'\n",
        "```sql\n",
        "SELECT *\n",
        "FROM transacoes\n",
        "WHERE valor > 30 AND id_loja = 'magalu';\n",
        "```\n",
        "\n",
        "> **Importante**: entregue os resultados da query com o nome *query1.csv*\n",
        "\n",
        "##2.2 Query 2\n",
        "Selecione da tabela transacoes todas as colunas, e retorne as linhas onde o `valor` for maior que 30 ou que o `id_loja` seja 'magalu'\n",
        "```sql\n",
        "SELECT *\n",
        "FROM transacoes\n",
        "WHERE valor > 30 OR id_loja = 'magalu';\n",
        "```\n",
        "\n",
        "> **Importante**: entregue os resultados da query com o nome *query2.csv*\n",
        "\n",
        "##2.3 Query 3\n",
        "Selecione da tabela transacoes todas as colunas, e retorne as linhas onde o `id_loja` for 'magalu' ou 'subway', e que tenham `valor` maior que 10\n",
        "```sql\n",
        "SELECT *\n",
        "FROM transacoes\n",
        "WHERE id_loja IN ('magalu','subway') AND valor > 10;\n",
        "```\n",
        "\n",
        "> **Importante**: entregue os resultados da query com o nome *query3.csv*\n",
        "\n",
        "##2.4 Query 4\n",
        "Selecione da tabela transacoes todas as colunas, e retorne as linhas onde o `valor` estiver entre 60 e 1000.\n",
        "```sql\n",
        "SELECT *\n",
        "FROM transacoes\n",
        "WHERE valor BETWEEN 60.0 AND 1000.0;\n",
        "```\n",
        "\n",
        "> **Importante**: entregue os resultados da query com o nome *query4.csv*"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QJJ92QL9C6fL"
      },
      "source": [
        "# **3. Filtro like e wildcards**\n",
        "\n",
        "Agora é hora de trabalhar com padrões! Execute as queries e salve o resultado para enviar os exercícios.   \n",
        "\n",
        "##3.1 Query 5\n",
        "Selecione da tabela transacoes todas as colunas e retorne as linhas onde na coluna `id_loja` contenha uma string que comece com 'mag' podendo conter ou não mais caracteres após.\n",
        "```sql\n",
        "SELECT *\n",
        "FROM transacoes\n",
        "WHERE id_loja LIKE 'mag%'\n",
        "```\n",
        "\n",
        "> **Importante**: entregue os resultados da query com o nome *query5.csv*\n",
        "\n",
        "##3.2 Query 6\n",
        "Selecione da tabela transacoes todas as colunas e retorne as linhas onde na coluna `id_loja` contenha uma string onde os caracteres 'sh' apareçam em qualquer parte.\n",
        "```sql\n",
        "SELECT *\n",
        "FROM transacoes\n",
        "WHERE id_loja LIKE '%sh%'\n",
        "```\n",
        "\n",
        "> **Importante**: entregue os resultados da query com o nome *query6.csv*\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yprhfZqVDZXL"
      },
      "source": [
        "# **4. Seleção condicional**\n",
        "\n",
        "Chegou a hora de testar a combinação do CASE com o SELECT! Execute a  query e salve o resultado para enviar o  exercício.   \n",
        "\n",
        "##4.1 Query 7\n",
        "Selecione as colunas `id_cliente`, `id_loja` e `valor` da tabela transacoes. Em seguida, crie duas novas colunas com as seguintes características:\n",
        "\n",
        "* Uma coluna chamada `classeValor`, onde:\n",
        "  \n",
        "  Compras com valor acima de 1000 receberão 'Compra com alto valor'.\n",
        "\n",
        "  Compras com valor abaixo de 1000 receberão 'Compra com baixo valor'.\n",
        "\n",
        "* Uma coluna chamada `tipo_compra`, onde, de acordo com o id_loja, será determinado o tipo de compra:\n",
        "\n",
        "  Se o id_loja for 'giraffas' ou 'subway', tipo_compra será 'alimentação'.\n",
        "\n",
        "  Se o id_loja for 'magalu' ou 'extra', tipo_compra será 'variedade'.\n",
        "\n",
        "  Se o id_loja for 'postoshell' ou 'seveneleven', tipo_compra será '24horas'.\n",
        "  \n",
        "  Caso contrário, tipo_compra será 'outros'.\n",
        "\n",
        "```sql\n",
        "SELECT id_cliente, id_loja, valor,\n",
        "CASE\n",
        "    WHEN valor > 1000 THEN 'Compra com alto valor'\n",
        "    WHEN valor < 1000 THEN 'Compra com baixo valor'\n",
        "END\n",
        "AS classeValor,\n",
        "CASE\n",
        "    WHEN id_loja IN ('giraffas','subway')  THEN 'alimentacao'\n",
        "    WHEN id_loja IN ('magalu','extra') THEN 'variedade'\n",
        "    WHEN id_loja IN ('postoshell','seveneleven') THEN '24horas'\n",
        "    ELSE 'outros'\n",
        "END\n",
        "AS tipo_compra\n",
        "FROM transacoes;\n",
        "```\n",
        "\n",
        "> **Importante**: entregue os resultados da query com o nome *query7.csv*\n",
        "\n"
      ]
    }
  ]
}