{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# El encargo entero\n",
    "\n",
    "\"Mira los datos de la tienda y dime cómo vamos\", contestado de punta a punta con todo lo del libro.\n",
    "\n",
    "Cuaderno de práctica del capítulo 22 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/proyecto-final/\n",
    "\n",
    "Los ejercicios están al final y traen una celda vacía debajo de cada uno. Las\n",
    "respuestas viven en el cuaderno de soluciones, y merece la pena pelearse un\n",
    "rato antes de abrirlo 💛"
   ]
  },
  {
   "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": [
    "## Antes de empezar\n",
    "\n",
    "Esta celda baja el ayudante que corrige tus ejercicios. Después, en cada\n",
    "ejercicio que se pueda corregir solo, vas a ver `%%revisa` arriba de la celda:\n",
    "escribe tu respuesta debajo, ejecuta, y te digo si te salió 💛"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import urllib.request\n",
    "\n",
    "# El ayudante de los cuadernos. Trae la corrección de los ejercicios y, en los\n",
    "# capítulos de consola, la celda mágica que ejecuta los comandos. Se baja en\n",
    "# vez de venir pegado aquí para que siempre sea el último.\n",
    "urllib.request.urlretrieve(\n",
    "    \"https://missyera.com/static/cuadernos/revisa.py\", \"revisa.py\")\n",
    "import revisa\n",
    "revisa.carga({\n",
    "    1: \"Y29ob3J0ZSAgY2xpZW50ZXMgIHBlZGlkb3MgIHNvbGVzICAgICBwZWRpZG9zX3Bvcl9jbGllbnRlCi0tLS0tLS0gIC0tLS0tLS0tICAtLS0tLS0tICAtLS0tLS0tLSAgLS0tLS0tLS0tLS0tLS0tLS0tLQoyMDI1LTAxICAxNCAgICAgICAgOTggICAgICAgNTg4NjQuNTIgIDcuMAoyMDI1LTAyICA4ICAgICAgICAgNDcgICAgICAgMjYxNjguMTEgIDUuOQoyMDI1LTAzICAxMCAgICAgICAgNzMgICAgICAgNDU2NTguNTUgIDcuMwoyMDI1LTA0ICAxMCAgICAgICAgODEgICAgICAgNDczMDMuNzMgIDguMQoyMDI1LTA1ICAxNCAgICAgICAgOTggICAgICAgNTc3OTIuMjQgIDcuMAoyMDI1LTA2ICA1ICAgICAgICAgNDQgICAgICAgMzAxMzIuMjMgIDguOAoyMDI1LTA3ICA3ICAgICAgICAgNTQgICAgICAgMzUwMjIuOTkgIDcuNwoyMDI1LTA4ICAxMSAgICAgICAgNjkgICAgICAgNDAzMjMuMzIgIDYuMw==\",\n",
    "    2: \"Y2xpZW50ZXMgIGNvbXByYXJvbl91bmFfdmV6ICBwY3QKLS0tLS0tLS0gIC0tLS0tLS0tLS0tLS0tLS0tICAtLS0KMTE5ICAgICAgIDEgICAgICAgICAgICAgICAgICAwLjg=\",\n",
    "    3: \"YW5pbyAgbWVzICAgICAgc29sZXMKLS0tLSAgLS0tLS0tLSAgLS0tLS0tLS0KMjAyNSAgMjAyNS0wMSAgMzY3NDMuOTQKMjAyNiAgMjAyNi0wNSAgMzM3MTEuOTc=\",\n",
    "    4: \"Y2l1ZGFkICBzZWdtZW50byAgICBwZWRpZG9zICBzb2xlcyAgICAgcGN0X2NpdWRhZAotLS0tLS0gIC0tLS0tLS0tLS0gIC0tLS0tLS0gIC0tLS0tLS0tICAtLS0tLS0tLS0tCkN1c2NvICAgTWF5b3Jpc3RhICAgMzggICAgICAgMjA3NTAuOTYgIDMxLjkKQ3VzY28gICBCb2RlZ2EgICAgICAzNyAgICAgICAxOTU2OC4xOSAgMzAuMQpDdXNjbyAgIE1pbmltYXJrZXQgIDE4ICAgICAgIDEyOTA1LjA1ICAxOS45CkN1c2NvICAgSG9yZWNhICAgICAgMTkgICAgICAgMTE3NDIuMDMgIDE4LjEKTGltYSAgICBIb3JlY2EgICAgICAzOSAgICAgICAyMzk1Ny41NSAgMzcuNgpMaW1hICAgIE1heW9yaXN0YSAgIDM3ICAgICAgIDIwOTAwLjg2ICAzMi44CkxpbWEgICAgQm9kZWdhICAgICAgMTcgICAgICAgMTEzNTEuNzIgIDE3LjgKTGltYSAgICBNaW5pbWFya2V0ICAxMSAgICAgICA3NTM4LjQgICAgMTEuOA==\",\n",
    "    5: \"bm9tYnJlICAgICAgICAgICAgICAgICAgICAgIGNpdWRhZCAgICBwZWRpZG9zICBzb2xlcyAgICAgZGlhc19zaW5fY29tcHJhcgotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSAgLS0tLS0tLS0gIC0tLS0tLS0gIC0tLS0tLS0tICAtLS0tLS0tLS0tLS0tLS0tCkRJU1RSSUJVSURPUkEgUEFaIDA1OCAgICAgICBMaW1hICAgICAgMTcgICAgICAgMTAxMzEuNzIgIDIzCkFMTUFDRU5FUyBWRUdBIDA2NyAgICAgICAgICBQaXVyYSAgICAgMTIgICAgICAgOTUxNi4wNiAgIDIyCk1BUktFVCBDRU5UUkFMIDA4NyAgICAgICAgICBDaGljbGF5byAgMTIgICAgICAgODE0OC41NiAgIDUzClJFU1RBVVJBTlRFIE1JUkFGTE9SRVMgMDkzICBUcnVqaWxsbyAgMTIgICAgICAgODExOS45NSAgIDQ4Ck1BUktFVCBDRU5UUkFMIDA3OCAgICAgICAgICBDaGljbGF5byAgMTQgICAgICAgNzk3Ny45ICAgIDYy\",\n",
    "    6: \"Y2l1ZGFkICAgIG5vbWJyZSAgICAgICAgICAgICAgICAgICAgICBzb2xlcwotLS0tLS0tLSAgLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gIC0tLS0tLS0tCkxpbWEgICAgICBESVNUUklCVUlET1JBIFBBWiAwNTggICAgICAgMTAxMzEuNzIKUGl1cmEgICAgIEFMTUFDRU5FUyBWRUdBIDA2NyAgICAgICAgICA5NTE2LjA2CkNoaWNsYXlvICBNQVJLRVQgQ0VOVFJBTCAwODcgICAgICAgICAgODE0OC41NgpUcnVqaWxsbyAgUkVTVEFVUkFOVEUgTUlSQUZMT1JFUyAwOTMgIDgxMTkuOTUKQXJlcXVpcGEgIENBRkUgREVMIFBVRVJUTyAwNjAgICAgICAgICA3OTQ4LjcxCkN1c2NvICAgICBNQVlPUklTVEEgUEVSVSAwMjcgICAgICAgICAgNzczMi40Ng==\",\n",
    "}, lenguaje=\"sql\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Trece capítulos de piezas sueltas. Este es el capítulo donde se juntan.\n",
    "\n",
    "Te voy a dar el encargo tal como llega en la vida real, que nunca llega en\n",
    "forma de consulta: *\"Yera, mira los datos de la tienda y dime cómo vamos\".*\n",
    "Nada más. Sin métricas, sin preguntas, sin qué es \"bien\".\n",
    "\n",
    "Lo que sigue es cómo trabajo yo un encargo así, en siete pasos, y las\n",
    "consultas que salen de cada uno 🐣\n",
    "\n",
    "Y antes de mirar una sola consulta, contéstate esto: **¿qué decisión quiere tomar la persona que te lo pidió?** Esa pregunta ordena el análisis entero, y es la que yo hago siempre antes de abrir la base 🎯"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Paso 1. Antes de nada, cuánto hay y de cuándo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La primera consulta de cualquier base nueva siempre es la misma: qué tamaño\n",
    "tiene esto y qué periodo cubre. Sin eso no puedes ni saber si un número es\n",
    "grande."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS pedidos,\n",
    "       COUNT(monto) AS con_monto,\n",
    "       COUNT(DISTINCT id_cliente) AS clientes,\n",
    "       MIN(fecha) AS desde, MAX(fecha) AS hasta,\n",
    "       ROUND(SUM(monto), 2) AS soles\n",
    "FROM pedidos;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ya con esa fila puedo escribir la primera línea del informe: 900 pedidos de\n",
    "119 clientes, entre enero de 2025 y junio de 2026, por S/532.654.\n",
    "\n",
    "Y ya tengo la primera pregunta incómoda: **900 pedidos pero solo 873 con\n",
    "monto**. Eso no se menciona al final, se menciona ahora."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Paso 2. Qué está roto"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Este paso se lo salta casi todo el mundo y es el que te salva de entregar un\n",
    "informe equivocado. Antes de analizar, audita."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT 'pedidos sin cliente' AS problema, COUNT(*) AS filas FROM pedidos WHERE id_cliente IS NULL\n",
    "UNION ALL\n",
    "SELECT 'pedidos sin monto', COUNT(*) FROM pedidos WHERE monto IS NULL\n",
    "UNION ALL\n",
    "SELECT 'clientes con nombre sucio', COUNT(*) FROM clientes WHERE nombre <> TRIM(nombre)\n",
    "UNION ALL\n",
    "SELECT 'clientes que nunca compraron', COUNT(*) FROM clientes c\n",
    "    WHERE NOT EXISTS (SELECT 1 FROM pedidos p WHERE p.id_cliente = c.id)\n",
    "UNION ALL\n",
    "SELECT 'pedidos sin lineas de detalle', COUNT(*) FROM pedidos p\n",
    "    WHERE NOT EXISTS (SELECT 1 FROM detalle d WHERE d.id_pedido = p.id);\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Esa tabla es el capítulo 8 (`NOT EXISTS`) y el 5\n",
    "(`TRIM`) trabajando juntos, y es lo primero que yo pego en un\n",
    "correo.\n",
    "\n",
    "Lo que dice, traducido a español de gerente:\n",
    "\n",
    "- 🕳️ **24 pedidos no tienen cliente**, así que cualquier reporte\n",
    "por cliente pierde 24 ventas. Alguien tiene que decidir si se recuperan o se\n",
    "descartan.\n",
    "\n",
    "- 💸 **27 pedidos no tienen monto.** No valen cero: no se sabe\n",
    "cuánto valen. La venta total real es mayor que la que voy a reportar.\n",
    "\n",
    "- 🧼 **18 clientes tienen el nombre sucio**, así que agrupar por\n",
    "nombre da resultados partidos.\n",
    "\n",
    "- 👤 **1 cliente no compró nunca.** Ese está bien, es información,\n",
    "no error.\n",
    "\n",
    "- ✅ **0 pedidos sin detalle.** Ese cero también se reporta:\n",
    "comprobado que esa parte está sana.\n",
    "\n",
    "Y ahora la decisión que hay que tomar en voz alta: **voy a analizar los\n",
    "873 pedidos con monto y voy a decirlo en cada tabla.** Lo que no se puede\n",
    "hacer es taparlo, ni contar los nulos como cero 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Paso 3. Cómo va el negocio en el tiempo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "WITH mes AS (\n",
    "    SELECT STRFTIME('%Y-%m', fecha) AS mes,\n",
    "           COUNT(*) AS pedidos,\n",
    "           ROUND(SUM(monto), 2) AS soles\n",
    "    FROM pedidos\n",
    "    GROUP BY mes\n",
    ")\n",
    "SELECT mes, pedidos, soles,\n",
    "       ROUND(soles - LAG(soles) OVER (ORDER BY mes), 2) AS variacion,\n",
    "       ROUND(AVG(soles) OVER (ORDER BY mes ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS media_3\n",
    "FROM mes\n",
    "ORDER BY mes;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dieciocho meses en una consulta que usa el capítulo 5\n",
    "(`STRFTIME`), el 6 (`GROUP BY`), el 8\n",
    "(`WITH`) y el 9 (`LAG` y la media móvil).\n",
    "\n",
    "Y aquí está lo que yo le diría al cliente, que no es \"subió\" ni \"bajó\":\n",
    "\n",
    "**No hay tendencia.** Mira la columna `media_3`,\n",
    "saltándote la primera fila que es enero él solito: en 2025 va entre 28 y 33 mil, y\n",
    "en 2026 entre 25 y 30 mil. La venta se mueve arriba y abajo mes a mes (febrero de\n",
    "2025 cayó 16 mil, marzo subió 16 mil) pero el nivel es el mismo. Un negocio\n",
    "plano.\n",
    "\n",
    "Y ese último mes, junio de 2026 con una caída de 8.418, es el mes en curso: la\n",
    "base termina el 24 de junio. **El último punto de una serie casi siempre\n",
    "está incompleto y casi siempre alguien lo lee como una caída**. Decirlo es\n",
    "parte del trabajo 🚩"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Paso 4. De dónde viene la venta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT c.ciudad,\n",
    "       COUNT(DISTINCT c.id) AS clientes,\n",
    "       COUNT(p.id) AS pedidos,\n",
    "       ROUND(SUM(p.monto), 2) AS soles,\n",
    "       ROUND(AVG(p.monto), 2) AS ticket,\n",
    "       ROUND(SUM(p.monto) / COUNT(DISTINCT c.id), 2) AS soles_por_cliente\n",
    "FROM clientes c\n",
    "LEFT JOIN pedidos p ON p.id_cliente = c.id\n",
    "GROUP BY c.ciudad\n",
    "ORDER BY soles DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Tres columnas que dicen tres cosas distintas y que la gente mezcla siempre:\n",
    "\n",
    "- 💰 **soles**: cuánto pesa la ciudad. Chiclayo manda.\n",
    "\n",
    "- 🎟️ **ticket**: cuánto vale un pedido. Aquí manda Piura con\n",
    "657, y Chiclayo es la última con 568.\n",
    "\n",
    "- 👤 **soles_por_cliente**: cuánto deja cada cliente al año.\n",
    "Chiclayo vuelve a subir.\n",
    "\n",
    "- 🏙️ Y Lima, la ciudad grande, es la que menos clientes tiene de las seis, con\n",
    "15. Esta tienda no es limeña.\n",
    "\n",
    "La lectura útil: **Chiclayo vende más porque tiene más clientes, no\n",
    "porque compre mejor.** Si el encargo fuera \"queremos crecer\", en Chiclayo\n",
    "la palanca es subir el ticket y en Piura es conseguir más clientes. Dos ciudades,\n",
    "dos planes distintos, y eso sale de mirar tres columnas en vez de una 💡"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT canal,\n",
    "       COUNT(*) AS pedidos,\n",
    "       ROUND(AVG(monto), 2) AS ticket,\n",
    "       ROUND(SUM(monto), 2) AS soles,\n",
    "       COUNT(DISTINCT id_cliente) AS clientes,\n",
    "       ROUND(1.0 * COUNT(*) / COUNT(DISTINCT id_cliente), 2) AS pedidos_por_cliente\n",
    "FROM pedidos\n",
    "GROUP BY canal\n",
    "ORDER BY soles DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Los cuatro canales están empatados: entre 206 y 234 pedidos, entre 598 y 616\n",
    "de ticket, entre 2,17 y 2,38 pedidos por cliente. La diferencia entre el primero\n",
    "y el último es del 18% en soles y nada en todo lo demás.\n",
    "\n",
    "Eso es un hallazgo, aunque no lo parezca: **el canal no explica\n",
    "nada**. Si alguien esperaba que el reporte dijera \"hay que apostar por\n",
    "Web\", la respuesta honesta es que los datos no dan para eso.\n",
    "\n",
    "Y cruzando con el capítulo 6: los clientes usan varios canales (89 de 119\n",
    "compran por Web y por WhatsApp), así que \"el canal\" ni siquiera es una\n",
    "característica del cliente. Es lo que tuvo a mano ese día 📱"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Paso 5. Quiénes son los clientes que importan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "WITH por_cliente AS (\n",
    "    SELECT c.id, TRIM(c.nombre) AS nombre, c.ciudad, c.segmento,\n",
    "           COUNT(p.id) AS pedidos,\n",
    "           ROUND(SUM(p.monto), 2) AS soles,\n",
    "           MAX(p.fecha) AS ultima_compra\n",
    "    FROM clientes c\n",
    "    LEFT JOIN pedidos p ON p.id_cliente = c.id\n",
    "    GROUP BY c.id, c.nombre, c.ciudad, c.segmento\n",
    "),\n",
    "con_rango AS (\n",
    "    SELECT *, NTILE(10) OVER (ORDER BY soles DESC) AS decil\n",
    "    FROM por_cliente\n",
    "    WHERE soles IS NOT NULL\n",
    ")\n",
    "SELECT decil, COUNT(*) AS clientes, ROUND(SUM(soles), 2) AS soles,\n",
    "       ROUND(100.0 * SUM(soles) / SUM(SUM(soles)) OVER (), 1) AS porcentaje\n",
    "FROM con_rango\n",
    "GROUP BY decil\n",
    "ORDER BY decil;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "El primer decil, o sea los 12 mejores clientes, se lleva el 18,7% de la venta.\n",
    "El último decil, el 3%.\n",
    "\n",
    "Y ahora una cosa importante y que va a contramano de lo que se espera:\n",
    "**esto NO es un 80/20**. Los tres primeros deciles, o sea el 30% de\n",
    "los clientes, hacen el 46% de la venta. Está concentrado, pero suavemente.\n",
    "\n",
    "Yo he visto muchas presentaciones donde alguien fuerza el 80/20 porque queda\n",
    "bien en la diapositiva. Si tus datos dicen 46/30, lo que va en la diapositiva es\n",
    "46/30. La gracia de medir es poder decir cuando algo *no* pasa 🌟"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Paso 6. Quién se está yendo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Esta es la pregunta que nadie pide y que siempre agradecen, porque es la\n",
    "única del informe que se puede accionar mañana."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "WITH por_cliente AS (\n",
    "    SELECT c.id, TRIM(c.nombre) AS nombre, c.ciudad,\n",
    "           COUNT(p.id) AS pedidos,\n",
    "           ROUND(SUM(p.monto), 2) AS soles,\n",
    "           MAX(p.fecha) AS ultima_compra,\n",
    "           CAST(JULIANDAY('2026-06-24') - JULIANDAY(MAX(p.fecha)) AS INTEGER) AS dias_sin_comprar\n",
    "    FROM clientes c\n",
    "    JOIN pedidos p ON p.id_cliente = c.id\n",
    "    GROUP BY c.id, c.nombre, c.ciudad\n",
    ")\n",
    "SELECT CASE\n",
    "         WHEN dias_sin_comprar <= 30  THEN '1. activo (30 dias)'\n",
    "         WHEN dias_sin_comprar <= 90  THEN '2. tibio (31 a 90)'\n",
    "         WHEN dias_sin_comprar <= 180 THEN '3. frio (91 a 180)'\n",
    "         ELSE                              '4. perdido (mas de 180)'\n",
    "       END AS estado,\n",
    "       COUNT(*) AS clientes,\n",
    "       ROUND(SUM(soles), 2) AS soles_historicos\n",
    "FROM por_cliente\n",
    "GROUP BY estado\n",
    "ORDER BY estado;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "119 clientes repartidos en cuatro cajones. 48 activos, 40 tibios, 21 fríos y\n",
    "10 perdidos.\n",
    "\n",
    "Los cortes (30, 90, 180) los elegí yo y no salen de ningún sitio: son una\n",
    "decisión de negocio, no un cálculo. En un encargo de verdad esa es una pregunta\n",
    "para el cliente, y mientras la contesta pones los tuyos y lo dices. Lo que no\n",
    "vale es presentarlos como si fueran una verdad de la base 🚩\n",
    "\n",
    "Y ahora la tabla que de verdad sirve: los que se están enfriando y encima\n",
    "gastan bien. Lo primero que sale escribirla es así, y no corre."
   ]
  },
  {
   "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 c.id, TRIM(c.nombre) AS nombre, ROUND(SUM(p.monto), 2) AS soles\n",
    "    FROM clientes c\n",
    "    JOIN pedidos p ON p.id_cliente = c.id\n",
    "    WHERE MAX(p.fecha) < '2026-03-24'\n",
    "    GROUP BY c.id, c.nombre;\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: misuse of aggregate: MAX()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mismo error que el `WHERE COUNT(*)` del capítulo 6, y por el mismo\n",
    "motivo: cuando el `WHERE` hace su trabajo, los grupos todavía no\n",
    "existen, así que no hay ningún `MAX` que calcular.\n",
    "\n",
    "La última compra de un cliente solo existe *después* de agrupar, así\n",
    "que hay que calcularla en un CTE y filtrar fuera, exactamente como con las\n",
    "ventanas del capítulo 9."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "WITH por_cliente AS (\n",
    "    SELECT c.id, TRIM(c.nombre) AS nombre, c.ciudad,\n",
    "           ROUND(SUM(p.monto), 2) AS soles,\n",
    "           MAX(p.fecha) AS ultima_compra,\n",
    "           CAST(JULIANDAY('2026-06-24') - JULIANDAY(MAX(p.fecha)) AS INTEGER) AS dias\n",
    "    FROM clientes c JOIN pedidos p ON p.id_cliente = c.id\n",
    "    GROUP BY c.id, c.nombre, c.ciudad\n",
    ")\n",
    "SELECT nombre, ciudad, soles, ultima_compra, dias\n",
    "FROM por_cliente\n",
    "WHERE dias > 90 AND soles > 4000\n",
    "ORDER BY soles DESC\n",
    "LIMIT 8;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Esto no es un análisis, es una lista de llamadas.** Ocho\n",
    "nombres, con su ciudad, cuánto dejaron y cuándo fue la última vez. Alguien puede\n",
    "coger el teléfono hoy."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Los días que lleva un cliente sin comprar\n",
    "\n",
    "| PostgreSQL | `CURRENT_DATE - MAX(fecha)   -- con columnas DATE la resta da días` |\n",
    "|---|---|\n",
    "| MySQL | `DATEDIFF(CURDATE(), MAX(fecha))` |\n",
    "| SQL Server | `DATEDIFF(day, MAX(fecha), CAST(GETDATE() AS DATE))` |\n",
    "| SQLite | `CAST(JULIANDAY('now') - JULIANDAY(MAX(fecha)) AS INTEGER)` |\n",
    "\n",
    "Es la única línea de todo este informe que hay que traducir al cambiar de motor. El WITH, el JOIN, el GROUP BY y el CASE de arriba se escriben igual en los cuatro."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fíjate que cinco de los ocho son de Piura, la ciudad del ticket más alto. Eso\n",
    "sí es una recomendación con nombre: *empezar por Piura*.\n",
    "\n",
    "Y si te preguntas por qué no hay un modelo de machine learning aquí: porque\n",
    "para esta pregunta no hace falta. Una consulta de doce líneas contesta lo que\n",
    "alguien va a hacer mañana. Si después quieres predecir quién se va a ir antes de\n",
    "que se vaya, ahí sí, y esa es\n",
    "[el libro de machine learning](https://missyera.com/guias/machine-learning-desde-cero/) 🐣"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Paso 7. Qué se vende"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT pr.categoria,\n",
    "       SUM(d.cantidad) AS unidades,\n",
    "       ROUND(SUM(d.cantidad * d.precio_unit), 2) AS soles,\n",
    "       ROUND(100.0 * SUM(d.cantidad * d.precio_unit) / SUM(SUM(d.cantidad * d.precio_unit)) OVER (), 1) AS pct\n",
    "FROM detalle d\n",
    "JOIN productos pr ON pr.id = d.id_producto\n",
    "GROUP BY pr.categoria\n",
    "ORDER BY soles DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Otra vez plano: cuatro categorías entre el 20% y el 23%, y Cuidado personal\n",
    "un poco más abajo con 14%. Ninguna manda.\n",
    "\n",
    "Acuérdate del aviso del capítulo 7: en esta base el monto del pedido y la\n",
    "suma de sus líneas no cuadran, así que estos soles son \"soles de línea de\n",
    "detalle\" y no se pueden sumar con los del paso 3. En un informe eso va escrito\n",
    "al pie de la tabla, no en la cabeza de quien la hizo 📝"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "WITH ranking AS (\n",
    "    SELECT pr.categoria, pr.nombre,\n",
    "           ROUND(SUM(d.cantidad * d.precio_unit), 2) AS soles,\n",
    "           ROW_NUMBER() OVER (PARTITION BY pr.categoria ORDER BY SUM(d.cantidad * d.precio_unit) DESC) AS puesto\n",
    "    FROM detalle d JOIN productos pr ON pr.id = d.id_producto\n",
    "    GROUP BY pr.categoria, pr.id, pr.nombre\n",
    ")\n",
    "SELECT categoria, nombre, soles FROM ranking WHERE puesto = 1 ORDER BY soles DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "El más vendido de cada categoría, con el patrón del capítulo 9 que ya conoces\n",
    "de memoria: CTE, `ROW_NUMBER` con `PARTITION BY`, filtrar\n",
    "por el puesto fuera."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## El informe, en seis líneas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Todo lo de arriba se resume en esto, y esto es lo que se lee:\n",
    "\n",
    "- 📊 900 pedidos de 119 clientes entre enero de 2025 y junio de 2026, por\n",
    "S/532.654. **27 pedidos no tienen monto**, así que la venta real es\n",
    "mayor.\n",
    "\n",
    "- ➖ **El negocio está plano.** Dieciocho meses sin tendencia, entre\n",
    "25 y 33 mil al mes. Junio de 2026 está a medias, no es una caída.\n",
    "\n",
    "- 🏙️ **Chiclayo vende más por volumen y Piura por ticket.** Son\n",
    "dos planes de crecimiento distintos.\n",
    "\n",
    "- 📱 **El canal no explica nada**: los cuatro están empatados y la\n",
    "gente usa varios.\n",
    "\n",
    "- 👥 **La concentración es suave**: el 30% de los clientes hace el\n",
    "46%. No es un 80/20.\n",
    "\n",
    "- 📞 **31 clientes llevan más de 90 días sin comprar**, y ocho de\n",
    "ellos gastaron más de S/4.000. Esa lista va adjunta y es lo único de este informe\n",
    "que se puede accionar mañana.\n",
    "\n",
    "Fíjate en algo: **ninguna de las seis dice una consulta.** El SQL\n",
    "es cómo llegaste, no es lo que entregas. Lo que entregas son frases que alguien\n",
    "puede usar para decidir 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ejercicios"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Siete, y son el encargo entero otra vez con preguntas nuevas. Intenta antes de\n",
    "abrir 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. La cohorte de alta\n",
    "\n",
    "Por mes de alta del cliente: cuántos entraron, cuántos\n",
    "pedidos hicieron y cuántos por cliente."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 1\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Cuántos compraron una sola vez\n",
    "\n",
    "De los que compraron, cuántos lo hicieron una única vez."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 2\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. El mejor mes de cada año\n",
    "\n",
    "Con ventanas: el mes de más venta de 2025 y el de 2026."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 3\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Ciudad y segmento a la vez\n",
    "\n",
    "Cuántos soles deja cada combinación de ciudad y segmento, y\n",
    "cuánto pesa dentro de su ciudad."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 4\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. La vista que entregas con el informe\n",
    "\n",
    "Deja una vista con la ficha de cada cliente, para que quien\n",
    "la pida no tenga que escribir nada de esto."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 5\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. El top de cada ciudad, desde la vista\n",
    "\n",
    "El mejor cliente de cada ciudad, usando la vista del\n",
    "ejercicio anterior."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 6\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7. Escríbelo para los cuatro\n",
    "\n",
    "Sin ejecutar: el informe de clientes en riesgo, listo para\n",
    "correr en cualquiera de los cuatro motores contra una base de verdad."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## La caída que no fue una caída"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y para terminar el libro, la trampa que más veces he visto llegar hasta una reunión. La consulta está bien escrita, el número está bien calculado, y la conclusión es falsa 📉"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### La trampa\n",
    "\n",
    "Te piden cómo va el negocio. Comparas el último mes con el anterior, que es lo primero que haría cualquiera, y sale una caída para preocuparse.\n",
    "\n",
    "```\n",
    "SELECT substr(fecha, 1, 7) AS mes,\n",
    "       ROUND(SUM(monto), 0) AS soles\n",
    "FROM pedidos GROUP BY mes ORDER BY mes DESC LIMIT 2;\n",
    "-- 2026-06   25293\n",
    "-- 2026-05   33712\n",
    "```\n",
    "\n",
    "**¿Qué está mal?** La respuesta está en el cuaderno de soluciones. Míralo tú primero."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Comprueba que lo tienes\n",
    "\n",
    "Terminas el análisis y el resultado te sale redondo: la ciudad que peor vende es justo la que sospechabas. ¿Qué haces antes de mandarlo?\n",
    "\n",
    "a) Repetirlo sobre otros cortes, a ver si el hallazgo aguanta\n",
    "\n",
    "b) Mandarlo, que confirma lo que ya se sabía\n",
    "\n",
    "c) Añadir un gráfico para que se entienda mejor\n",
    "\n",
    "d) Repetir la consulta a ver si da lo mismo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lo que te llevas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 📏 Empieza siempre por cuánto hay y de cuándo. Sin eso no sabes si un número\n",
    "es grande.\n",
    "\n",
    "- 🔍 Audita antes de analizar, y reporta los ceros comprobados igual que los\n",
    "problemas.\n",
    "\n",
    "- 🗣️ Di en voz alta qué dejaste fuera y por qué. Aquí: 27 pedidos sin\n",
    "monto.\n",
    "\n",
    "- ➖ \"No hay tendencia\" y \"el canal no explica nada\" son hallazgos. Poder decir\n",
    "que algo *no* pasa es la mitad del valor de medir.\n",
    "\n",
    "- 🚩 El último punto de una serie casi siempre está incompleto.\n",
    "\n",
    "- 📞 Termina con algo accionable. Una lista de ocho nombres vale más que veinte\n",
    "gráficos.\n",
    "\n",
    "- 🧾 Lo que entregas son frases, no consultas. El SQL es cómo llegaste.\n",
    "\n",
    "Y si de todo el capítulo te llevas una sola frase, que sea esta:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Un encargo no se contesta con una consulta. Se contesta con una decisión\n",
    "que alguien pueda tomar."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "El capítulo 23 es el último y es cortito: dónde está la documentación oficial\n",
    "de cada motor, y qué leer después de este libro.\n",
    "\n",
    "Que tengas lindo día! 🌸"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Los datos ya los sabes sacar. Lo que sigue es qué hace la IA con ellos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQL es la habilidad que más rápido se nota en el trabajo y ya la tienes: sacas el dato sin pedírselo a nadie y sabes si el número que sale tiene sentido.\n",
    "\n",
    "El siguiente paso natural no es más SQL, es qué se construye encima. Un sistema de IA que trabaja sobre tus tablas y que puedes demostrar que funciona es lo que armamos en [el curso de ingeniería de IA en producción](https://missyera.com/cursos/ingenieria-de-ia/), durante cuatro semanas y sobre un proceso real de tu trabajo.\n",
    "\n",
    "Y si lo tuyo es análisis y no construir, el camino es otro, es igual de válido y paga bien. No todo el mundo tiene que terminar programando 🐣\n",
    "\n",
    "Por ahí, lo que más rinde después de SQL es poder mostrar lo que encontraste, y eso son Excel y Power BI. Esas clases las tengo grabadas en [mis cursos de datos](https://missyera.com/cursos/datos/), con el mismo SQL que acabas de leer pero dado en aula, por si aprendes mejor viéndome resolverlo que leyéndolo."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "Ese era el capítulo 22 de **SQL desde cero**. El texto completo, con las salidas de cada bloque, está en https://missyera.com/guias/sql-desde-cero/proyecto-final/\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
}