Interview.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "EYam6BprWqUq"
   },
   "source": [
    "# **Concurso - SQL & Programación**\n",
    "\n",
    "Bienvenido a Greensill Latam.\n",
    "\n",
    "Esta prueba está enfocada en identificar y evaluar sus habilidades frente al uso de herramientas como SQL y Pandas. \n",
    "\n",
    "¡Éxitos en su prueba!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cellView": "form",
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 917,
     "status": "ok",
     "timestamp": 1591729789950,
     "user": {
      "displayName": "Angel Benjamin Rojas Garcia",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AOh14GiQurTvdRWHErE-bkaIigaPuSL96EboEpqOhAKS=s64",
      "userId": "01387676823840811611"
     },
     "user_tz": 300
    },
    "id": "gWB1hxpxHarB",
    "outputId": "324f2200-a64d-4f3b-db4d-c66b6b1d5417"
   },
   "outputs": [],
   "source": [
    "from datetime import date\n",
    "\n",
    "print(\"Date:\", date.today())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Queremos conocer un poco de su conocimiento en programación (específicamente del lenguaje Python) y sobre lenguajes de consulta (SQL), por lo que hemos preparado unas cuantas preguntas relacionadas a estos temas."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Pregunta #1**\n",
    "\n",
    "Para un entero positivo $n$, sea $f(n)$ la suma de los digitos de $n$. Se puede observar que los números $n, f(n), f(f(n)), f(f(f(n)))$, ... eventualmente se convierten en un simple dígito que se repite por siempre. Denotemos a este dígito único como $g(n)$.\n",
    "Por ejemplo, consideremos $n = 1234567892$.\n",
    "Entonces: <br>\n",
    "$f(n) = 1+2+3+4+5+6+7+8+9+2 = 47$ <br>\n",
    "$f(f(n)) = 4 + 7 = 11$ <br>\n",
    "$f(f(f(n))) = 1 + 1 = 2 = g(n)$ <br>\n",
    "\n",
    "Por lo tanto, $g(1234567892) = 2$.\n",
    "\n",
    "Escriba la función $g()$."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your answer here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Pregunta #2**\n",
    "\n",
    "Dada una cadena de caracteres, indicar el numero de apariciones de cada caracter en la cadena, agrupado por caracter, ordenado por numero de apariciones:\n",
    "Ejemplo:<br>\n",
    "Cadena: \"Perro\"<br>\n",
    "Resultado;<br>\n",
    "r = 2<br>\n",
    "e = 1<br>\n",
    "o = 1<br>\n",
    "p = 1<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your answer here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Pregunta #3**\n",
    "\n",
    "Dado el alfabeto con los caracteres `\"{\", \"}\", \"(\", \")\", \"[\", \"]\"`, cree un algoritmo encargado de validar que cualquier string cumpla con tener una llave o parentesis de apertura y cierre.\n",
    "    \n",
    "ej: \n",
    "* `[[{}]] = true`\n",
    "* `[[{]] = false`\n",
    "* `[({)}] = false`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your answer here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Pregunta #4**\n",
    "\n",
    "Se requiere un software bancario que permita realizar las siguientes operaciones:\n",
    "\n",
    "a. Vinculacion de clientes<br>\n",
    "b. Apertura de cuentas bancarias (Corriente, Ahorro)<br>\n",
    "c. Consignaciones<br>\n",
    "d. Retiros<br>\n",
    "e. Impresion de Detalle de Movimientos (Fecha, Consignacion/Retiro, Monto, Saldo Inicial, Saldo Final)<br>\n",
    "\n",
    "Cliente: Juan Perez<br>\n",
    "Documento: 2552648 <br>\n",
    "Cuenta de ahorros <br>\n",
    "Valor apertura: 200.000<br>\n",
    "\n",
    "Consignaciones:<br>\n",
    "01/05/2020 - 10.800.000<br>\n",
    "03/05/2020 - 9.000.000<br>\n",
    "05/05/2020 - 8.000.000<br>\n",
    "\n",
    "Retiros:<br>\n",
    "02/05/2020 - 1.000.000<br>\n",
    "04/05/2020 - 5.000.000<br>\n",
    "06/05/2020 - 10.000.000<br>\n",
    "\n",
    "Se requiere crear la Base de Datos para soportar estas operaciones (puede describirlo como clases de Python o como sentencias SQL)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your answer here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "XITng-UOgOoB"
   },
   "source": [
    "# **SQL**\n",
    "\n",
    "El servidor ejecuta una instancia de base de datos, este entorno utilizara el motor de base de datos ***postgres***, no se preocupe si no esta familiarizado con este motor, el SQL necesario esta basado en ***ANSI SQL***.\n",
    "\n",
    "\n",
    "\n",
    "```\n",
    "Para ejecutar las sentencias de consulta SQL coloque el simbolo %%sql al principio de la celda.\n",
    "```\n",
    "\n",
    "Ejemplo:\n",
    "\n",
    "\n",
    "```\n",
    "%%sql\n",
    "SELECT table_name\n",
    "FROM information_schema.tables\n",
    "WHERE table_schema = 'public'\n",
    "ORDER BY table_name;\n",
    "```\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql\n",
    "%config SqlMagic.feedback=False \n",
    "%config SqlMagic.autopandas=True\n",
    "%config SqlMagic.short_errors=False\n",
    "%sql postgresql://localhost/postgres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT table_name\n",
    "FROM information_schema.tables\n",
    "WHERE table_schema = 'public'\n",
    "ORDER BY table_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "H2DkKh_xs5QI"
   },
   "source": [
    "\n",
    "El esquema de base de datos, contiene las tablas,  ***game***,  ***goal***,  ***eteam***\n",
    "\n",
    "Entidad relación:\n",
    "\n",
    "![FootballERD](FootballERD.png)\n",
    "\n",
    "Datos de ejemplo:\n",
    "\n",
    ">table: ***game***\n",
    "\n",
    ">id | mdate | stadium | team1 | team2\n",
    ">--- | --- | --- | --- | --- \n",
    ">1001\t| 8 June 2012 |\tNational Stadium, Warsaw | POL | GRE\n",
    ">1002\t| 8 June 2012\t| Stadion Miejski (Wroclaw) | RUS |\tCZE\n",
    ">1003\t| 12 June 2012 | Stadion Miejski (Wroclaw) | GRE |CZE\n",
    ">...\t| ... | ... | ... |...\n",
    ">1004\t| 12 June 2012 | National Stadium, Warsaw | POL | RUS\n",
    "\n",
    ">table: ***goal***\n",
    "\n",
    ">matchid | teamid | player | gtime\n",
    ">--- | --- | --- | ---\n",
    ">1001 | POL | Robert Lewandowski | 17\n",
    ">1001 | GRE | Dimitris Salpingidis | 51\n",
    ">... | ... | ... | ...\n",
    ">1002 | RUS | Alan Dzagoev | 15\n",
    ">1002 | RUS | Roman Pavlyuchenko | 82\n",
    "\n",
    ">table: ***eteam***\n",
    "\n",
    ">id\t| teamname | coach\n",
    ">--- | --- | ---\n",
    ">POL | Poland | Franciszek Smuda\n",
    ">RUS | Russia | Dick Advocaat\n",
    ">CZE | Czech Republic | Michal Bilek\n",
    ">... | ... | ...\n",
    ">GRE | Greece | Fernando Santos\n",
    "\n",
    "Estas tablas contienen todos los partidos y goles del Campeonato de Fútbol UEFA EURO 2012.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "2cpw3cHFeFwS"
   },
   "source": [
    "# **SQL - Pregunta #1**\n",
    "El ejemplo muestra el gol marcado por el jugador con apellido 'Bender'.\n",
    "\n",
    "\n",
    "```\n",
    "SELECT * \n",
    "FROM goal \n",
    "WHERE player LIKE '%Bender'\n",
    "```\n",
    "\n",
    "Modifíquelo para mostrar el matchid y el nombre del jugador (player) para todos los goles marcados por Alemania (GER). Con el fin de identificar los jugadores alemanes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "-tVjcmApczLn"
   },
   "source": [
    "# **SQL - Pregunta #2**\n",
    "Muestra el nombre del equipo y el número total de goles marcados.\n",
    "\n",
    "Resultado esperado:\n",
    ">teamname | team1\n",
    ">--- | --- \n",
    ">Italy\t| 6\n",
    ">Czech Republic\t| 4\n",
    ">Germany | 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "-V-U5Z0FbCSe"
   },
   "source": [
    "# **SQL - Pregunta #3**\n",
    "\n",
    "La consulta de ejemplo muestra todos los goles marcados en el partido entre Alemania (GER) y Grecia (GRE).\n",
    "```\n",
    "SELECT player, gtime\n",
    "FROM game JOIN goal ON matchid = id \n",
    "WHERE team1 = 'GER' AND team2 = 'GRE'\n",
    "```\n",
    "En su lugar, muestre el nombre de todos los jugadores que marcaron al menos un gol contra Alemania."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Abg8VWD0XvR-"
   },
   "source": [
    "# **SQL - Pregunta #4**\n",
    "Liste cada partido con los goles marcados por cada equipo como se muestra a continuación.\n",
    "\n",
    ">mdate | team1 |\tscore1 |\tteam2 | score2\n",
    ">--- | --- | --- | --- | ---\n",
    ">10 June 2012 |\tESP |\t1\t| ITA\t| 1\n",
    ">10 June 2012\t| IRL |\t1\t| CRO |\t3\n",
    ">11 June 2012\t| FRA\t| 1\t| ENG\t| 1\n",
    ">... |\t...\t| ...\t| ...\t| ...\n",
    ">8 June 2012\t| RUS\t| 4\t| CZE\t| 1\n",
    ">9 June 2012\t| GER\t| 1\t| POR\t| 0\n",
    ">9 June 2012\t| NED\t| 0\t| DEN\t| 1\n",
    "\n",
    "\n",
    "Observe en la consulta. Si fue un gol del team1, aparece un 1 en el score1; de lo contrario, hay un 0. Podría SUMAR esta columna para obtener un recuento de los goles marcados por el team1. Ordene su resultado por mdate, team1 y team2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Exo4Z9Khbiff"
   },
   "source": [
    "\n",
    "# **SQL - Pregunta #5**\n",
    "\n",
    "Para cada partido donde Alemania anotó, retorne matchid, mdate y el número de goles marcados por este equipo.\n",
    "\n",
    "Resultado esperado:\n",
    ">matchid | mdate | count\n",
    ">--|--|--|\n",
    ">1008 |\t9 June 2012\t| 1\n",
    ">1010\t| 13 June 2012\t| 2\n",
    ">--|--|--|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "business intelligence Interview test.ipynb",
   "provenance": [
    {
     "file_id": "1LIsZlnRhXJM-M1HuLtFQx3V6tfriau5Z",
     "timestamp": 1590974380843
    }
   ]
  },
  "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}