{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pensar la consulta en pisos\n",
    "\n",
    "WITH, EXISTS, UNION y el NOT IN que devuelve cero filas sin que nadie te avise.\n",
    "\n",
    "Cuaderno de soluciones del capítulo 8 de **SQL desde cero**, de Miss Yera.\n",
    "\n",
    "Corre de arriba abajo. Si lo abres en Google Colab no necesitas instalar nada.\n",
    "\n",
    "Capítulo completo: https://missyera.com/guias/sql-desde-cero/subconsultas-y-ctes/\n",
    "\n",
    "Este es el cuaderno de **soluciones**. Trae el código de cada ejercicio, la\n",
    "explicación de la trampa y la respuesta del quiz. Si vienes del cuaderno de\n",
    "práctica sin haberlo intentado, vuelve 🙂"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Antes de empezar\n",
    "\n",
    "Se baja la base y se deja lista una función `q()` que corre las consultas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import urllib.request\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "urllib.request.urlretrieve(\"https://missyera.com/static/datasets/tienda.db\", \"tienda.db\")\n",
    "con = sqlite3.connect(\"tienda.db\")\n",
    "\n",
    "def q(sql):\n",
    "    \"\"\"Corre las sentencias del bloque y devuelve la ultima como tabla.\n",
    "\n",
    "    Parte por sentencias igual que la consola de SQLite, porque un bloque puede\n",
    "    traer varias y un CREATE TRIGGER lleva punto y coma dentro de su cuerpo.\n",
    "    \"\"\"\n",
    "    resultado = None\n",
    "    trozo = \"\"\n",
    "    for linea in sql.splitlines(keepends=True):\n",
    "        trozo += linea\n",
    "        if sqlite3.complete_statement(trozo):\n",
    "            if trozo.strip():\n",
    "                cur = con.execute(trozo.strip())\n",
    "                resultado = (pd.DataFrame(cur.fetchall(),\n",
    "                                          columns=[d[0] for d in cur.description])\n",
    "                             if cur.description else None)\n",
    "            trozo = \"\"\n",
    "    if trozo.strip():\n",
    "        cur = con.execute(trozo.strip())\n",
    "        resultado = (pd.DataFrame(cur.fetchall(),\n",
    "                                  columns=[d[0] for d in cur.description])\n",
    "                     if cur.description else None)\n",
    "    con.commit()\n",
    "    return resultado\n",
    "\n",
    "q(\"SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Con lo de los siete capítulos anteriores ya puedes contestar casi cualquier\n",
    "pregunta de negocio. El problema empieza cuando la pregunta tiene dos pisos.\n",
    "\n",
    "\"¿Cuánto gasta en promedio un cliente de cada ciudad?\" no es un\n",
    "`AVG`. Es dos cuentas encadenadas: primero cuánto gastó cada cliente,\n",
    "y sobre *eso*, el promedio por ciudad. Y para eso necesitas meter una\n",
    "consulta dentro de otra.\n",
    "\n",
    "Este capítulo va de las dos formas de hacerlo, y de por qué una de ellas te\n",
    "va a cambiar la vida cuando la consulta pase de veinte líneas 🌟\n",
    "\n",
    "Y hazte esta pregunta cuando escribas la próxima consulta larga: **¿esto lo va a entender alguien dentro de seis meses?** Ese alguien casi siempre eres tú, y por eso yo escribo en pisos aunque nadie más lea la consulta 🪜"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Una consulta dentro de otra"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La más simple: una subconsulta que devuelve **un solo valor** y\n",
    "que usas como si fuera un número escrito a mano."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT ROUND(AVG(monto), 2) AS el_promedio FROM pedidos;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS por_encima_del_promedio\n",
    "FROM pedidos\n",
    "WHERE monto > (SELECT AVG(monto) FROM pedidos);\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Eso de dentro del paréntesis se calcula primero, da 610,14, y la consulta de\n",
    "fuera lo usa como si hubieras escrito `WHERE monto > 610.14`.\n",
    "\n",
    "La ventaja no es que sea más corto. Es que **no hay que actualizarlo\n",
    "nunca**: mañana entran cien pedidos más, el promedio cambia solo y la\n",
    "consulta sigue siendo correcta. Un número escrito a mano se pudre; una\n",
    "subconsulta no.\n",
    "\n",
    "Y fíjate en el 421 de 873 pedidos con monto: casi la mitad está por encima\n",
    "del promedio. Eso pasa cuando los datos están repartidos parejos. Si te sale que\n",
    "solo el 5% supera el promedio, ahí hay unos poquitos pedidos gigantes tirando de\n",
    "la media, y entonces la media no era la medida que buscabas."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Subconsultas que devuelven una lista"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT c.nombre, c.ciudad\n",
    "FROM clientes c\n",
    "WHERE c.id IN (SELECT id_cliente FROM pedidos WHERE monto > 1300)\n",
    "ORDER BY c.id;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aquí la subconsulta devuelve muchas filas, y `IN` pregunta \"¿este\n",
    "`id` está en esa lista?\". Siete clientes hicieron alguna vez un\n",
    "pedido de más de S/1.300.\n",
    "\n",
    "Se lee de adentro hacia afuera y es de las cosas que más rápido se vuelven\n",
    "naturales 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## La trampa del NOT IN, que es de las peores del libro"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ya sabemos del capítulo 7 que hay exactamente un cliente que nunca compró.\n",
    "Vamos a buscarlo con `NOT IN`, que es lo que sale solo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS nunca_compraron\n",
    "FROM clientes\n",
    "WHERE id NOT IN (SELECT id_cliente FROM pedidos);\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cero. Y sabemos que es uno 😳\n",
    "\n",
    "El culpable es el `NULL`, otra vez. La tabla `pedidos`\n",
    "tiene 24 filas con `id_cliente` nulo, así que la lista que devuelve la\n",
    "subconsulta contiene nulos. Y `id NOT IN (5, 8, NULL)` significa\n",
    "\"`id <> 5` Y `id <> 8` Y\n",
    "`id <> NULL`\". Esa última no es ni verdadera ni falsa, es\n",
    "desconocida, y una condición desconocida hace que toda la fila se caiga.\n",
    "\n",
    "Resultado: **con un solo `NULL` en la lista,\n",
    "`NOT IN` no devuelve nunca ninguna fila**. Y no da error. Te\n",
    "entrega un cero limpio que parece una buena noticia.\n",
    "\n",
    "Esto es igual en los cuatro motores, que para variar se ponen de acuerdo en\n",
    "lo peor. Hay dos arreglos."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS nunca_compraron\n",
    "FROM clientes\n",
    "WHERE id NOT IN (SELECT id_cliente FROM pedidos WHERE id_cliente IS NOT NULL);\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS nunca_compraron\n",
    "FROM clientes c\n",
    "WHERE NOT EXISTS (SELECT 1 FROM pedidos p WHERE p.id_cliente = c.id);\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "El primero limpia la lista a mano. El segundo usa `NOT EXISTS`,\n",
    "que **no tiene este problema nunca** porque no compara valores:\n",
    "pregunta \"¿existe al menos una fila que cumpla esto?\" y a eso el\n",
    "`NULL` no le hace nada.\n",
    "\n",
    "Mi regla, y te la regalo: **usa `NOT EXISTS` siempre y\n",
    "olvídate de `NOT IN`.** Es igual de legible, es igual o más\n",
    "rápido en los cuatro motores, y no te va a mentir un martes por la tarde.\n",
    "\n",
    "Ese `SELECT 1` de dentro llama la atención y es a propósito: a\n",
    "`EXISTS` no le importa qué devuelvas, solo si devuelve algo. Puedes\n",
    "poner `SELECT 1`, `SELECT *` o\n",
    "`SELECT 'pollito'` y da exactamente lo mismo 🐣"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Una subconsulta en el FROM"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Volvamos a la pregunta del principio: cuánto gasta en promedio un cliente de\n",
    "cada ciudad."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT ciudad, ROUND(AVG(soles), 2) AS gasto_medio_por_cliente\n",
    "FROM (\n",
    "    SELECT c.id, c.ciudad, SUM(p.monto) AS soles\n",
    "    FROM clientes c\n",
    "    JOIN pedidos p ON p.id_cliente = c.id\n",
    "    GROUP BY c.id, c.ciudad\n",
    ") AS por_cliente\n",
    "GROUP BY ciudad\n",
    "ORDER BY gasto_medio_por_cliente DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La de dentro arma una tabla temporal con una fila por cliente y su total. La\n",
    "de fuera la trata como si fuera una tabla normal y le hace el promedio por\n",
    "ciudad. Eso se llama **tabla derivada**.\n",
    "\n",
    "Y mira qué distinto se ve el negocio así: por ticket promedio (capítulo 7)\n",
    "mandaba Piura; por gasto anual de cada cliente manda Chiclayo. Son dos preguntas\n",
    "distintas y dan dos respuestas distintas, las dos correctas 📊"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "El alias de la tabla derivada\n",
    "\n",
    "| PostgreSQL | `obligatorio: sin él, \"subquery in FROM must have an alias\"` |\n",
    "|---|---|\n",
    "| MySQL | `obligatorio: \"Every derived table must have its own alias\"` |\n",
    "| SQL Server | `obligatorio` |\n",
    "| SQLite | `opcional, funciona igual sin ponerlo` |\n",
    "\n",
    "Tres de cuatro te obligan, así que ponle alias siempre aunque estés en SQLite. Es una línea de tres letras y te ahorra que la consulta no arranque el día que la muevas."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## WITH, o cómo escribir de arriba abajo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La consulta de arriba funciona, pero se lee al revés: lo primero que pasa\n",
    "está en el medio, entre paréntesis. Con dos pisos se aguanta; con cuatro es\n",
    "ilegible.\n",
    "\n",
    "`WITH` arregla eso. Es la misma consulta, dada vuelta."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "WITH por_cliente AS (\n",
    "    SELECT c.id, c.ciudad, c.segmento, SUM(p.monto) AS soles, COUNT(p.id) AS pedidos\n",
    "    FROM clientes c\n",
    "    JOIN pedidos p ON p.id_cliente = c.id\n",
    "    GROUP BY c.id, c.ciudad, c.segmento\n",
    ")\n",
    "SELECT ciudad, COUNT(*) AS clientes, ROUND(AVG(soles), 2) AS gasto_medio\n",
    "FROM por_cliente\n",
    "GROUP BY ciudad\n",
    "ORDER BY gasto_medio DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mismos números, y ahora se lee como se piensa: primero calculo el gasto de\n",
    "cada cliente y le pongo nombre, después uso ese nombre.\n",
    "\n",
    "Eso se llama **CTE** (common table expression) y en cristiano es\n",
    "\"una tabla temporal con nombre, que vive solo mientras dura la consulta\". Es lo\n",
    "que más me cambió la forma de escribir SQL, y por eso lo pongo tan pronto en el\n",
    "libro.\n",
    "\n",
    "Y ojo a un detalle de la salida: Cusco dice 16 clientes y en el capítulo 6\n",
    "dijimos 17. No es un error: aquí hay un `JOIN` normal, así que\n",
    "Comercial Rojas 120 no entra porque nunca compró. Cuando un número no cuadra con\n",
    "otro capítulo, casi siempre es que la pregunta no era la misma 🔍\n",
    "\n",
    "Los CTE se encadenan, y ahí es donde brilla."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "WITH por_cliente AS (\n",
    "    SELECT c.id, c.nombre, SUM(p.monto) AS soles\n",
    "    FROM clientes c JOIN pedidos p ON p.id_cliente = c.id\n",
    "    GROUP BY c.id, c.nombre\n",
    "),\n",
    "promedio AS (\n",
    "    SELECT AVG(soles) AS media FROM por_cliente\n",
    ")\n",
    "SELECT COUNT(*) AS por_encima\n",
    "FROM por_cliente, promedio\n",
    "WHERE por_cliente.soles > promedio.media;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dos CTE, y el segundo usa al primero. 58 de los 119 clientes que compraron\n",
    "están por encima del gasto medio.\n",
    "\n",
    "Fíjate en la coma del `FROM por_cliente, promedio`: es un\n",
    "`CROSS JOIN` del capítulo 7, y aquí es correcto justamente porque\n",
    "`promedio` tiene una sola fila. Multiplicar por uno no multiplica\n",
    "nada."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Desde cuándo hay WITH\n",
    "\n",
    "| PostgreSQL | `desde la 8.4, año 2009` |\n",
    "|---|---|\n",
    "| MySQL | `desde la 8.0, año 2018` |\n",
    "| SQL Server | `desde 2005` |\n",
    "| SQLite | `desde la 3.8.3, año 2014` |\n",
    "\n",
    "Se escribe idéntico en los cuatro, que en este libro es casi una fiesta. La única pega es MySQL 5.7, que todavía se ve en empresas y no lo tiene: ahí toca tabla derivada."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Una subconsulta en el SELECT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT c.nombre,\n",
    "       (SELECT COUNT(*) FROM pedidos p WHERE p.id_cliente = c.id) AS pedidos\n",
    "FROM clientes c\n",
    "ORDER BY pedidos DESC\n",
    "LIMIT 3;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Esta se llama **correlacionada** porque la de dentro mira a la\n",
    "de fuera: ese `c.id` cambia en cada fila. O sea que la subconsulta se\n",
    "ejecuta una vez por cliente, 120 veces.\n",
    "\n",
    "Con 120 filas ni lo notas. Con dos millones, esa misma consulta se cuelga y\n",
    "el `JOIN` con `GROUP BY` del capítulo 7 hace lo mismo en un\n",
    "segundo. **Cuando una subconsulta en el SELECT se puede escribir como\n",
    "JOIN, escríbela como JOIN.**\n",
    "\n",
    "Y ahora el error que tiene que salir sí o sí."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    q(\"\"\"\n",
    "    SELECT (SELECT id, nombre FROM clientes LIMIT 1) AS x;\n",
    "    \"\"\")\n",
    "except Exception as e:\n",
    "    print(f'{type(e).__name__}: {e}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y la queja que tiene que salir es esta:\n",
    "\n",
    "```\n",
    "OperationalError: sub-select returns 2 columns - expected 1\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*sub-select returns 2 columns - expected 1*. Una subconsulta que va en\n",
    "el `SELECT` o al lado de un `=` tiene que devolver\n",
    "**exactamente una columna**, y como mucho una fila. Es de los pocos\n",
    "sitios donde SQLite se pone estricto, y menos mal 🙌"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Apilar resultados: UNION"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Los `JOIN` pegan tablas de lado. `UNION` las apila una\n",
    "debajo de otra."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT id_cliente FROM pedidos WHERE canal = 'Web' AND id_cliente IS NOT NULL\n",
    "EXCEPT\n",
    "SELECT id_cliente FROM pedidos WHERE canal = 'WhatsApp' AND id_cliente IS NOT NULL\n",
    "ORDER BY id_cliente\n",
    "LIMIT 5;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ese `EXCEPT` es \"lo del primero que no esté en el segundo\": los\n",
    "clientes que compran por Web y nunca por WhatsApp. Son 16 en total, y esa es una\n",
    "lista concreta para el equipo que quiere mover gente al canal barato 📞\n",
    "\n",
    "Las cuatro operaciones de conjuntos son:\n",
    "\n",
    "- ➕ `UNION`: apila y **quita duplicados**.\n",
    "\n",
    "- ➕ `UNION ALL`: apila y no quita nada. Es más rápida, y es la que\n",
    "quieres cuando sabes que no hay repetidos.\n",
    "\n",
    "- ✖️ `INTERSECT`: solo lo que está en las dos.\n",
    "\n",
    "- ➖ `EXCEPT`: lo del primero que no esté en el segundo.\n",
    "\n",
    "Las cuatro piden que las dos consultas tengan **el mismo número de\n",
    "columnas y en el mismo orden**. Los nombres no importan: manda la\n",
    "primera."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "INTERSECT y EXCEPT\n",
    "\n",
    "| PostgreSQL | `INTERSECT y EXCEPT` |\n",
    "|---|---|\n",
    "| MySQL | `solo desde la 8.0.31, año 2022; antes había que armarlo a mano` |\n",
    "| SQL Server | `INTERSECT y EXCEPT` |\n",
    "| SQLite | `INTERSECT y EXCEPT` |\n",
    "\n",
    "MySQL llegó tardísimo a estos dos, así que en cualquier MySQL que no sea de los últimos años hay que hacer el EXCEPT con un LEFT JOIN ... IS NULL del capítulo 7, y el INTERSECT con un INNER JOIN."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## El CTE recursivo, que parece magia"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Un CTE puede llamarse a sí mismo. Suena raro y sirve para dos cosas muy\n",
    "concretas: recorrer jerarquías (el jefe del jefe del jefe) y\n",
    "**fabricar listas que no existen en ninguna tabla**, que es la que\n",
    "vas a usar tú."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "WITH RECURSIVE meses(mes) AS (\n",
    "    SELECT '2026-01'\n",
    "    UNION ALL\n",
    "    SELECT STRFTIME('%Y-%m', DATE(mes || '-01', '+1 month'))\n",
    "    FROM meses\n",
    "    WHERE mes < '2026-06'\n",
    ")\n",
    "SELECT mes FROM meses;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se lee en tres partes:\n",
    "\n",
    "- 1️⃣ **El primer escalón**, `SELECT '2026-01'`. De\n",
    "dónde arranca.\n",
    "\n",
    "- 2️⃣ **El escalón siguiente**, que se calcula a partir del\n",
    "anterior. Aquí, sumarle un mes.\n",
    "\n",
    "- 3️⃣ **Cuándo parar**, ese `WHERE mes < '2026-06'`.\n",
    "Si te lo olvidas, la consulta no termina nunca.\n",
    "\n",
    "¿Para qué quieres una lista de meses? Para el problema más común de todo\n",
    "reporte: **los meses sin ventas no salen en un GROUP BY**, porque\n",
    "no hay filas que agrupar. Con esta lista y un `LEFT JOIN` del\n",
    "capítulo 7, salen con cero."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "WITH meses(mes) AS (\n",
    "    SELECT '2026-01' UNION ALL SELECT '2026-02' UNION ALL SELECT '2026-03'\n",
    ")\n",
    "SELECT m.mes, COUNT(p.id) AS pedidos\n",
    "FROM meses m\n",
    "LEFT JOIN pedidos p ON STRFTIME('%Y-%m', p.fecha) = m.mes\n",
    "GROUP BY m.mes\n",
    "ORDER BY m.mes;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aquí los tres meses tienen pedidos, así que el ejemplo se ve tonto. El día\n",
    "que uno tenga cero, tu gráfico va a mostrar el hueco en vez de saltárselo, que\n",
    "es la diferencia entre un reporte honesto y uno que disimula 🌟"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La palabra RECURSIVE\n",
    "\n",
    "| PostgreSQL | `WITH RECURSIVE ... obligatoria` |\n",
    "|---|---|\n",
    "| MySQL | `WITH RECURSIVE ... obligatoria` |\n",
    "| SQL Server | `WITH ... sin la palabra RECURSIVE, que no existe` |\n",
    "| SQLite | `WITH RECURSIVE ... obligatoria` |\n",
    "\n",
    "Tres la piden y SQL Server la prohíbe, así que es una de las poquísimas líneas que hay que cambiar sí o sí al mover una consulta. Lo demás del CTE recursivo se escribe igual en los cuatro."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ejercicios"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Siete sobre `tienda.db`. Intenta antes de abrir 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. El pedido más grande\n",
    "\n",
    "Trae el pedido con el monto más alto, sin escribir el\n",
    "número a mano."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT id, monto FROM pedidos WHERE monto = (SELECT MAX(monto) FROM pedidos);\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "id  monto\n",
    "--  -------\n",
    "25  1399.98\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Con `ORDER BY monto DESC LIMIT 1` también sale, y es más rápido.\n",
    "La diferencia está en los empates: si dos pedidos tuvieran el mismo monto máximo,\n",
    "el `LIMIT 1` te da uno solo y esta consulta te da los dos. Casi\n",
    "siempre quieres los dos."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Clientes con más de una dirección\n",
    "\n",
    "Cuántos clientes tienen dos direcciones o más, con una\n",
    "subconsulta correlacionada."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS clientes_con_dos_direcciones\n",
    "FROM clientes c\n",
    "WHERE (SELECT COUNT(*) FROM direcciones d WHERE d.id_cliente = c.id) >= 2;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "clientes_con_dos_direcciones\n",
    "----------------------------\n",
    "33\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "33 de 120. Y esto mismo con `JOIN` + `GROUP BY` +\n",
    "`HAVING` da igual y corre mejor; lo hago así aquí para que veas la\n",
    "forma correlacionada en el `WHERE`, que es donde sí se usa mucho."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Los mejores clientes, con el listón calculado\n",
    "\n",
    "Los clientes que gastaron más de diez veces el ticket\n",
    "promedio de la tienda."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT c.nombre, c.ciudad, ROUND(SUM(p.monto), 2) AS soles\n",
    "FROM clientes c JOIN pedidos p ON p.id_cliente = c.id\n",
    "GROUP BY c.id, c.nombre, c.ciudad\n",
    "HAVING SUM(p.monto) > (SELECT AVG(monto) * 10 FROM pedidos)\n",
    "ORDER BY soles DESC\n",
    "LIMIT 4;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "nombre                      ciudad    soles\n",
    "--------------------------  --------  --------\n",
    "Distribuidora Paz 058       Lima      10131.72\n",
    "Almacenes Vega 067          Piura     9516.06\n",
    "Market Central 087          Chiclayo  8148.56\n",
    "Restaurante Miraflores 093  Trujillo  8119.95\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La subconsulta va dentro del `HAVING`, que es perfectamente legal y\n",
    "poca gente lo sabe. El listón sale de los datos, así que el día que suba el\n",
    "ticket promedio, el listón sube solo.\n",
    "\n",
    "Si le quitas el `ROUND` vas a ver un 8148.5599999999995, que es el\n",
    "`FLOAT` del capítulo 4 saludando 👋"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Los clientes que compran por los dos canales\n",
    "\n",
    "Cuántos clientes han comprado alguna vez por Web y alguna\n",
    "vez por WhatsApp."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS en_los_dos FROM (\n",
    "SELECT id_cliente FROM pedidos WHERE canal = 'Web' AND id_cliente IS NOT NULL\n",
    "INTERSECT\n",
    "SELECT id_cliente FROM pedidos WHERE canal = 'WhatsApp' AND id_cliente IS NOT NULL\n",
    ");\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "en_los_dos\n",
    "----------\n",
    "89\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "89 de los 119 que compraron usan los dos canales. O sea que la gente no elige\n",
    "canal, elige el que tenga a mano ese día. Eso cambia bastante cómo se piensa una\n",
    "campaña 💡\n",
    "\n",
    "Y ese `id_cliente IS NOT NULL` está puesto a propósito: sin él, el\n",
    "grupo de los nulos entra en los dos lados y el `INTERSECT` te suma un\n",
    "cliente que no existe."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Los que más pedidos grandes hacen\n",
    "\n",
    "Con un CTE: los 5 clientes con más pedidos de más de S/800,\n",
    "con su nombre y ciudad."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "WITH grandes AS (\n",
    "    SELECT id_cliente, COUNT(*) AS pedidos_grandes\n",
    "    FROM pedidos\n",
    "    WHERE monto > 800 AND id_cliente IS NOT NULL\n",
    "    GROUP BY id_cliente\n",
    ")\n",
    "SELECT c.nombre, c.ciudad, g.pedidos_grandes\n",
    "FROM grandes g\n",
    "JOIN clientes c ON c.id = g.id_cliente\n",
    "ORDER BY g.pedidos_grandes DESC, c.id\n",
    "LIMIT 5;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "nombre                  ciudad    pedidos_grandes\n",
    "----------------------  --------  ---------------\n",
    "Mayorista Peru 027      Cusco     7\n",
    "  MARKET CENTRAL 069    Trujillo  6\n",
    "Cafe del Puerto 092     Piura     6\n",
    "Minimarket El Sol 001   Piura     5\n",
    "Almacenes Vega 067      Piura     5\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Un CTE se puede unir con un `JOIN` igual que una tabla de verdad,\n",
    "y eso es lo que lo hace tan cómodo: calculas lo difícil arriba y abajo escribes\n",
    "una consulta normal y corriente."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. El NOT IN que miente\n",
    "\n",
    "Cuenta los clientes que nunca compraron, de las tres formas,\n",
    "y explica por qué una da cero."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS con_not_in\n",
    "FROM clientes\n",
    "WHERE id NOT IN (SELECT id_cliente FROM pedidos);\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "con_not_in\n",
    "----------\n",
    "0\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS con_not_exists\n",
    "FROM clientes c\n",
    "WHERE NOT EXISTS (SELECT 1 FROM pedidos p WHERE p.id_cliente = c.id);\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "con_not_exists\n",
    "--------------\n",
    "1\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS con_left_join\n",
    "FROM clientes c\n",
    "LEFT JOIN pedidos p ON p.id_cliente = c.id\n",
    "WHERE p.id IS NULL;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "con_left_join\n",
    "-------------\n",
    "1\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cero, uno y uno. Las dos últimas son correctas y la primera se rompe por los\n",
    "24 `id_cliente` nulos de la tabla de pedidos.\n",
    "\n",
    "Este ejercicio es el que más quiero que se te quede de todo el capítulo,\n",
    "porque el fallo no se ve, no avisa, y el resultado que devuelve es justo el que\n",
    "alguien quiere oír: \"no hay clientes inactivos\" 🫠"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7. Escríbelo para los cuatro\n",
    "\n",
    "Sin ejecutar: los clientes que nunca compraron, en los\n",
    "cuatro motores."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "-- Se escribe IGUAL en PostgreSQL, MySQL, SQL Server y SQLite.\n",
    "SELECT c.id, c.nombre\n",
    "FROM clientes c\n",
    "WHERE NOT EXISTS (SELECT 1 FROM pedidos p WHERE p.id_cliente = c.id);\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Una sola versión para los cuatro, y de las poquísimas del libro. Las\n",
    "subconsultas y `EXISTS` son de lo más estándar que tiene SQL: lo que\n",
    "cambia entre motores casi siempre son las funciones (texto, fechas, formato), no\n",
    "la estructura de la consulta.\n",
    "\n",
    "O sea que si algo de este capítulo te parece que cuesta, buena noticia: lo\n",
    "que aprendas aquí te sirve en los cuatro sin traducir nada 🐣"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## La subconsulta que mira hacia afuera"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Antes de cerrar, una que da miedo de verdad: una consulta mal escrita que no revienta, sino que contesta con toda tranquilidad 😳"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### La trampa\n",
    "\n",
    "Cruzas dos tablas con una subconsulta. Te equivocas de columna al escribirla, y en vez de un error te sale un resultado.\n",
    "\n",
    "```\n",
    "SELECT COUNT(*) FROM clientes\n",
    "WHERE nombre IN (SELECT nombre FROM pedidos);\n",
    "-- 120\n",
    "```\n",
    "\n",
    "**Qué está mal**\n",
    "\n",
    "Los 120. Todos 😳 Y `pedidos` no tiene ninguna columna que se llame `nombre`.\n",
    "\n",
    "Cuando SQL no encuentra una columna dentro de la subconsulta, **la busca fuera**. Y como `clientes` sí tiene `nombre`, la subconsulta se convierte en \"el nombre de este cliente, repetido una vez por cada pedido que hay\". Claro que está en la lista: es él mismo.\n",
    "\n",
    "Esto es una regla del lenguaje, no un fallo de SQLite: pasa igual en PostgreSQL y en SQL Server. Y es de las pocas cosas de SQL que producen un resultado con toda tranquilidad estando la consulta mal escrita.\n",
    "\n",
    "Se evita poniéndole alias a todo y usándolos siempre: `SELECT p.nombre FROM pedidos p` habría reventado con un *no such column*, que es justo lo que querías. Los alias no son para escribir menos, son para que el motor no adivine por ti."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Comprueba que lo tienes\n",
    "\n",
    "Buscas los clientes que no están en una lista con WHERE cliente_id NOT IN (SELECT cliente_id FROM devoluciones) y te devuelve cero filas, aunque sabes que hay clientes que nunca devolvieron.\n",
    "\n",
    "a) Si la subconsulta trae aunque sea un NULL, el NOT IN se queda vacío\n",
    "\n",
    "b) La subconsulta devuelve demasiadas filas y el motor se rinde\n",
    "\n",
    "c) Hay que poner DISTINCT en la subconsulta\n",
    "\n",
    "d) NOT IN no existe, hay que escribir NOT EXISTS\n",
    "\n",
    "---\n",
    "\n",
    "**La correcta es la a.**\n",
    "\n",
    "*b)* El número de filas no cambia el resultado a cero. Mira qué hay dentro de esas filas.\n",
    "\n",
    "*c)* El DISTINCT quitaría repetidos, y los repetidos no vacían el resultado.\n",
    "\n",
    "*d)* NOT IN existe y funciona. Cambiar a NOT EXISTS arregla este caso, y conviene saber por qué.\n",
    "\n",
    "Un NULL en la lista hace que la comparación nunca sea verdadera. Por eso NOT EXISTS es más seguro cuando la columna admite nulos."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lo que te llevas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 🔢 Una subconsulta escalar en el `WHERE` te deja poner un listón\n",
    "que se recalcula solo. Nunca escribas el número a mano.\n",
    "\n",
    "- 💣 `NOT IN` con un `NULL` en la lista devuelve cero\n",
    "filas, sin error, en los cuatro motores. Usa `NOT EXISTS` y\n",
    "listo.\n",
    "\n",
    "- 🧱 Una subconsulta en el `FROM` es una tabla derivada. Ponle\n",
    "alias siempre: tres de los cuatro motores te lo exigen.\n",
    "\n",
    "- 📖 `WITH` es la misma consulta escrita de arriba abajo. Está en\n",
    "los cuatro y es lo que hace legible una consulta larga.\n",
    "\n",
    "- 🐌 Una subconsulta correlacionada en el `SELECT` corre una vez\n",
    "por fila. Con pocas filas da igual; con muchas, pásala a\n",
    "`JOIN`.\n",
    "\n",
    "- ➕ `UNION` quita duplicados y `UNION ALL` no.\n",
    "`INTERSECT` y `EXCEPT` no existen en MySQL anterior a la\n",
    "8.0.31.\n",
    "\n",
    "- 🔁 `WITH RECURSIVE` fabrica listas que no están en ninguna tabla,\n",
    "como el calendario de meses para que no falten los ceros. En SQL Server va sin la\n",
    "palabra `RECURSIVE`.\n",
    "\n",
    "Y si de todo el capítulo te llevas una sola frase, que sea esta:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`NOT IN` contra una subconsulta es una bomba de tiempo. Se\n",
    "escribe `NOT EXISTS`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y esto de partir un problema grande en trozos con nombre no es de SQL:\n",
    "es lo mismo que hace una función, y por el mismo motivo. Está en el\n",
    "[libro de Python](https://missyera.com/guias/python-desde-cero/) 🪜\n",
    "\n",
    "En el capítulo 9 vienen las funciones de ventana, que es lo que te deja\n",
    "calcular un ranking, un acumulado o un \"cuánto creció respecto al mes pasado\"\n",
    "sin perder el detalle. Es la herramienta que más separa a alguien que sabe SQL de\n",
    "alguien que lo usa.\n",
    "\n",
    "Que tengas lindo día! 🌸"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "Ese era el capítulo 8 de **SQL desde cero**. El texto completo, con las salidas de cada bloque, está en https://missyera.com/guias/sql-desde-cero/subconsultas-y-ctes/\n",
    "\n",
    "Que tengas lindo día! 🌸"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}