{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Funciones de ventana, sin perder el detalle\n",
    "\n",
    "Rankings, acumulados y el crecimiento mes a mes, con la misma sintaxis en los cuatro motores.\n",
    "\n",
    "Cuaderno de práctica del capítulo 9 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/funciones-de-ventana/\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: \"Y2l1ZGFkICAgIG5vbWJyZSAgICAgICAgICAgICAgICAgICAgICBzb2xlcwotLS0tLS0tLSAgLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gIC0tLS0tLS0tCkxpbWEgICAgICBEaXN0cmlidWlkb3JhIFBheiAwNTggICAgICAgMTAxMzEuNzIKUGl1cmEgICAgIEFsbWFjZW5lcyBWZWdhIDA2NyAgICAgICAgICA5NTE2LjA2CkNoaWNsYXlvICBNYXJrZXQgQ2VudHJhbCAwODcgICAgICAgICAgODE0OC41NgpUcnVqaWxsbyAgUmVzdGF1cmFudGUgTWlyYWZsb3JlcyAwOTMgIDgxMTkuOTUKQXJlcXVpcGEgIENhZmUgZGVsIFB1ZXJ0byAwNjAgICAgICAgICA3OTQ4LjcxCkN1c2NvICAgICBNYXlvcmlzdGEgUGVydSAwMjcgICAgICAgICAgNzczMi40Ng==\",\n",
    "    2: \"ZmVjaGEgICAgICAgc29sZXMgICAgZGlhX2FudGVyaW9yICBkaWFfc2lndWllbnRlCi0tLS0tLS0tLS0gIC0tLS0tLS0gIC0tLS0tLS0tLS0tLSAgLS0tLS0tLS0tLS0tLQoyMDI2LTA2LTAxICAxODU0LjAxICAgICAgICAgICAgICAgIDExMzMuOTMKMjAyNi0wNi0wMiAgMTEzMy45MyAgMTg1NC4wMSAgICAgICA0MTMuMzgKMjAyNi0wNi0wMyAgNDEzLjM4ICAgMTEzMy45MyAgICAgICA4OTAuMTUKMjAyNi0wNi0wNCAgODkwLjE1ICAgNDEzLjM4ICAgICAgICA3NTYuNTEKMjAyNi0wNi0wNSAgNzU2LjUxICAgODkwLjE1ICAgICAgICAxNTI1LjE4\",\n",
    "    3: \"aWQgIGNhbmFsICAgICAgICBtb250byAgIHByb21lZGlvX2RlbF9jYW5hbCAgZGlmZXJlbmNpYQotLSAgLS0tLS0tLS0tLS0gIC0tLS0tLSAgLS0tLS0tLS0tLS0tLS0tLS0tICAtLS0tLS0tLS0tCjEgICBXZWIgICAgICAgICAgODkyLjA2ICA2MTUuOTkgICAgICAgICAgICAgIDI3Ni4wNwoyICAgTWFya2V0cGxhY2UgIDczMS4wOSAgNjExLjUgICAgICAgICAgICAgICAxMTkuNTkKMyAgIFRpZW5kYSAgICAgICA0MDcuMzkgIDU5OC40MSAgICAgICAgICAgICAgLTE5MS4wMgo0ICAgVGllbmRhICAgICAgIDM1OC4yMyAgNTk4LjQxICAgICAgICAgICAgICAtMjQwLjE4CjUgICBXZWIgICAgICAgICAgODQ0Ljk0ICA2MTUuOTkgICAgICAgICAgICAgIDIyOC45NQ==\",\n",
    "    4: \"c2VnbWVudG8gICAgY2xpZW50ZXMgIGZpbGEgIHJhbmdvICByYW5nb19kZW5zbwotLS0tLS0tLS0tICAtLS0tLS0tLSAgLS0tLSAgLS0tLS0gIC0tLS0tLS0tLS0tCkhvcmVjYSAgICAgIDM2ICAgICAgICAxICAgICAxICAgICAgMQpCb2RlZ2EgICAgICAzNSAgICAgICAgMiAgICAgMiAgICAgIDIKTWluaW1hcmtldCAgMjUgICAgICAgIDMgICAgIDMgICAgICAzCk1heW9yaXN0YSAgIDI0ICAgICAgICA0ICAgICA0ICAgICAgNA==\",\n",
    "    5: \"Y2FuYWwgICBpZCAgIG1vbnRvICAgIGVsX21heW9yX2RlbF9jYW5hbCAgcmVzcGVjdG9fYWxfbWF5b3IKLS0tLS0tICAtLS0gIC0tLS0tLS0gIC0tLS0tLS0tLS0tLS0tLS0tLSAgLS0tLS0tLS0tLS0tLS0tLS0KVGllbmRhICAxNTEgIDEyMzYuOTcgIDEyMzYuOTcgICAgICAgICAgICAgMTAwLjAKVGllbmRhICAxNDIgIDExNzAuMyAgIDEyMzYuOTcgICAgICAgICAgICAgOTQuNgpUaWVuZGEgIDIzNiAgMTE3MC4wOCAgMTIzNi45NyAgICAgICAgICAgICA5NC42ClRpZW5kYSAgMzYxICAxMTUwLjA0ICAxMjM2Ljk3ICAgICAgICAgICAgIDkzLjA=\",\n",
    "    6: \"bWVzICAgICAgc29sZXMgICAgIG1lZGlhX2RlX3RvZG9zICBhY3VtdWxhZG8KLS0tLS0tLSAgLS0tLS0tLS0gIC0tLS0tLS0tLS0tLS0tICAtLS0tLS0tLS0KMjAyNS0wMSAgMzY3NDMuOTQgIDI5NTkxLjg4ICAgICAgICAzNjc0My45NAoyMDI1LTAyICAyMDIzMS4yMyAgMjk1OTEuODggICAgICAgIDU2OTc1LjE3CjIwMjUtMDMgIDM2Mjc0Ljg3ICAyOTU5MS44OCAgICAgICAgOTMyNTAuMDQKMjAyNS0wNCAgMzU2ODAuNTMgIDI5NTkxLjg4ICAgICAgICAxMjg5MzAuNTc=\",\n",
    "}, lenguaje=\"sql\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`GROUP BY` tiene un precio que casi nadie te cuenta: te quedas sin\n",
    "el detalle. Preguntas el promedio por canal y te devuelve cuatro filas; los 900\n",
    "pedidos desaparecieron.\n",
    "\n",
    "Y hay preguntas que necesitan las dos cosas a la vez. \"¿Cuánto se aleja este\n",
    "pedido del promedio de su canal?\" pide el promedio del canal (un resumen) y el\n",
    "monto de este pedido (el detalle), en la misma fila.\n",
    "\n",
    "Eso es lo que hacen las **funciones de ventana**: calculan un\n",
    "resumen y *no* aplastan las filas. Es lo que más separa a alguien que sabe\n",
    "SQL de alguien que lo usa, y una vez que las entiendes ya no hay vuelta 🌟\n",
    "\n",
    "Y una pregunta antes de empezar: **¿alguna vez te pidieron el top 3 de cada categoría?** Con lo de los capítulos anteriores eso se hace a mano y con dolor. Con ventanas son cuatro líneas 🪟"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## OVER (), o el resumen que se queda al lado"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT id, canal, monto,\n",
    "       ROUND(AVG(monto) OVER (), 2) AS promedio_global,\n",
    "       ROUND(monto - AVG(monto) OVER (), 2) AS diferencia\n",
    "FROM pedidos\n",
    "WHERE monto IS NOT NULL\n",
    "ORDER BY id\n",
    "LIMIT 5;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ese `OVER ()` es toda la diferencia. Sin él,\n",
    "`AVG(monto)` te habría dado una sola fila; con él, el mismo 610,14 se\n",
    "repite al lado de cada pedido y puedes restarlo.\n",
    "\n",
    "Piensa el `OVER ()` como una **ventana**: es el\n",
    "conjunto de filas que la función mira para calcular su número. Vacío significa\n",
    "\"míralas todas\"."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PARTITION BY, o una ventana por grupo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT id, canal, monto,\n",
    "       ROUND(AVG(monto) OVER (PARTITION BY canal), 2) AS promedio_del_canal\n",
    "FROM pedidos\n",
    "WHERE monto IS NOT NULL\n",
    "ORDER BY id\n",
    "LIMIT 5;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahora cada fila ve solo las de su canal. El pedido 1 es Web y le sale 615,99;\n",
    "el 3 es Tienda y le sale 598,41. Los mismos números del capítulo 6, pero sin\n",
    "perder las 873 filas.\n",
    "\n",
    "**`PARTITION BY` es a las ventanas lo que\n",
    "`GROUP BY` es a los grupos**, con una diferencia enorme: no\n",
    "reduce nada. Si lo tienes claro, ya entendiste la mitad del capítulo 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Rankings: las tres formas de numerar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT ciudad, COUNT(*) AS clientes,\n",
    "       ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS fila,\n",
    "       RANK() OVER (ORDER BY COUNT(*) DESC) AS rango,\n",
    "       DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rango_denso\n",
    "FROM clientes\n",
    "GROUP BY ciudad;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aquí está toda la teoría en una tabla, y menos mal que Trujillo y Cusco\n",
    "empatan con 17, porque el empate es lo único que las diferencia:\n",
    "\n",
    "- 🔢 `ROW_NUMBER` numera 1, 2, 3, 4, 5, 6. No sabe de empates: a\n",
    "uno le toca el 4 y al otro el 5, y cuál es cuál lo decide el motor.\n",
    "\n",
    "- 🥇 `RANK` les da 4 a los dos y después **salta al 6**.\n",
    "Es el podio de una carrera: dos cuartos, no hay quinto.\n",
    "\n",
    "- 🎗️ `DENSE_RANK` les da 4 a los dos y sigue en 5. No deja\n",
    "huecos.\n",
    "\n",
    "Cuál usar depende de la pregunta. Para \"el top 3 de verdad\", `RANK`\n",
    "o `DENSE_RANK`, porque si hay empate en el tercer puesto quieres los\n",
    "dos. Para \"dame exactamente una fila por grupo\",\n",
    "`ROW_NUMBER`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## El top N por grupo, que es la reina de las consultas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\"El pedido más grande de cada canal\" es de las cosas que más te van a pedir y\n",
    "de las que peor se resuelven sin ventanas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT canal, id, monto,\n",
    "       ROW_NUMBER() OVER (PARTITION BY canal ORDER BY monto DESC) AS puesto\n",
    "FROM pedidos\n",
    "WHERE monto IS NOT NULL\n",
    "ORDER BY canal, puesto\n",
    "LIMIT 8;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La ventana numera de nuevo dentro de cada canal: el más caro de Marketplace es\n",
    "el puesto 1, el siguiente el 2, y cuando cambia el canal vuelve a empezar en 1.\n",
    "Ahora solo falta quedarse con los primeros. Y aquí viene el tropiezo clásico."
   ]
  },
  {
   "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 canal, id, monto\n",
    "    FROM pedidos\n",
    "    WHERE ROW_NUMBER() OVER (PARTITION BY canal ORDER BY monto DESC) = 1;\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 window function ROW_NUMBER()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*misuse of window function*. Acuérdate del orden de ejecución del\n",
    "capítulo 6: el `WHERE` pasa en el paso 2 y las ventanas se calculan\n",
    "en el paso 5, junto con el `SELECT`. Cuando el `WHERE`\n",
    "mira, el puesto todavía no existe.\n",
    "\n",
    "Lo mismo pasa con `HAVING` y `GROUP BY`. La solución es\n",
    "siempre la misma: **calcula la ventana en un CTE y filtra fuera**,\n",
    "que es exactamente para lo que aprendimos `WITH` en el capítulo 8."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "WITH ranking AS (\n",
    "    SELECT canal, id, monto,\n",
    "           ROW_NUMBER() OVER (PARTITION BY canal ORDER BY monto DESC) AS puesto\n",
    "    FROM pedidos\n",
    "    WHERE monto IS NOT NULL\n",
    ")\n",
    "SELECT canal, id, monto, puesto\n",
    "FROM ranking\n",
    "WHERE puesto <= 2\n",
    "ORDER BY canal, puesto;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Los dos pedidos más grandes de cada canal, en ocho filas. Ese patrón\n",
    "(`WITH` + `ROW_NUMBER` + `WHERE puesto <= N`)\n",
    "lo vas a escribir mil veces, y funciona igual en los cuatro motores."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Filtrar por el resultado de una ventana\n",
    "\n",
    "| PostgreSQL | `CTE y filtrar fuera. No tiene QUALIFY.` |\n",
    "|---|---|\n",
    "| MySQL | `CTE y filtrar fuera` |\n",
    "| SQL Server | `CTE y filtrar fuera` |\n",
    "| SQLite | `CTE y filtrar fuera` |\n",
    "\n",
    "Los cuatro igual, y por una vez la incomodidad es pareja. Si alguna vez lees código con QUALIFY, que hace esto en una línea, es de Snowflake o de BigQuery: ninguno de estos cuatro lo tiene."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## El acumulado, o cómo va el año"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cuando el `OVER` lleva `ORDER BY`, la ventana deja de\n",
    "ser \"todas las filas del grupo\" y pasa a ser \"todas las de aquí para atrás\". Y\n",
    "eso es un acumulado."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "WITH ventas_mes AS (\n",
    "    SELECT STRFTIME('%Y-%m', fecha) AS mes, ROUND(SUM(monto), 2) AS soles\n",
    "    FROM pedidos\n",
    "    WHERE fecha >= '2026-01-01'\n",
    "    GROUP BY mes\n",
    ")\n",
    "SELECT mes, soles,\n",
    "       ROUND(SUM(soles) OVER (ORDER BY mes), 2) AS acumulado\n",
    "FROM ventas_mes\n",
    "ORDER BY mes;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "168.167 soles en el primer semestre de 2026, y la columna de al lado te dice\n",
    "cómo se llegó ahí. Es la típica que pide un gerente y que en Excel es una\n",
    "fórmula que se arrastra y se rompe 📈\n",
    "\n",
    "Esa ventana se puede escribir a mano, y a veces hay que hacerlo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "WITH ventas_mes AS (\n",
    "    SELECT STRFTIME('%Y-%m', fecha) AS mes, ROUND(SUM(monto), 2) AS soles\n",
    "    FROM pedidos GROUP BY mes\n",
    ")\n",
    "SELECT mes, soles,\n",
    "       ROUND(AVG(soles) OVER (ORDER BY mes ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS media_movil_3\n",
    "FROM ventas_mes\n",
    "ORDER BY mes\n",
    "LIMIT 6;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` es \"esta fila y las dos\n",
    "de antes\", o sea una media móvil de tres meses. Sirve para ver la tendencia sin\n",
    "que un mes raro te la tape: enero fue 36.743 y febrero 20.231, y a partir de la\n",
    "segunda fila la media de tres va suavecita entre 28 y 34 mil.\n",
    "\n",
    "Fíjate en las dos primeras filas: enero no tiene dos meses antes, así que su\n",
    "media es él solo. Las medias móviles siempre arrancan cojas y eso hay que\n",
    "decirlo en el reporte 🌸"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Mirar la fila de al lado: LAG y LEAD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La pregunta más frecuente del mundo de los reportes: *¿cuánto crecimos\n",
    "respecto al mes pasado?*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "WITH ventas_mes AS (\n",
    "    SELECT STRFTIME('%Y-%m', fecha) AS mes, ROUND(SUM(monto), 2) AS soles\n",
    "    FROM pedidos\n",
    "    WHERE fecha >= '2026-01-01'\n",
    "    GROUP BY mes\n",
    ")\n",
    "SELECT mes, soles,\n",
    "       LAG(soles) OVER (ORDER BY mes) AS mes_anterior,\n",
    "       ROUND(soles - LAG(soles) OVER (ORDER BY mes), 2) AS diferencia,\n",
    "       ROUND(100.0 * (soles - LAG(soles) OVER (ORDER BY mes)) / LAG(soles) OVER (ORDER BY mes), 1) AS variacion\n",
    "FROM ventas_mes\n",
    "ORDER BY mes;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`LAG` trae el valor de la fila anterior y `LEAD` el de\n",
    "la siguiente. Con eso, comparar meses deja de ser un rompecabezas de\n",
    "subconsultas y pasa a ser una columna más.\n",
    "\n",
    "Enero está vacío en las tres columnas, y está bien: no hay mes anterior, así\n",
    "que es `NULL`. Del capítulo 4 ya sabemos que cualquier cuenta con\n",
    "`NULL` da `NULL`, por eso la diferencia y la variación\n",
    "también salen vacías.\n",
    "\n",
    "Y mira lo que dice el negocio: febrero cae 18%, mayo sube 22%, junio cae 25%.\n",
    "Junio no está terminado en esta base, o sea que esa caída puede ser mitad realidad\n",
    "y mitad calendario. **El último mes de un reporte casi siempre está\n",
    "incompleto y casi siempre alguien lo lee como una caída** 🫠"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Desde qué versión hay funciones de ventana\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 las de ranking, desde 2012 el resto` |\n",
    "| SQLite | `desde la 3.25, año 2018` |\n",
    "\n",
    "La sintaxis es la misma en los cuatro, que es la buena noticia. La mala es MySQL 5.7 y SQLite viejito, que no las tienen y todavía andan por ahí: si tu consulta da error de sintaxis en el OVER, mira la versión antes de volverte loca."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Repartir en grupos: NTILE"
   ]
  },
  {
   "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",
    "en_cuartiles AS (\n",
    "    SELECT nombre, soles, NTILE(4) OVER (ORDER BY soles DESC) AS cuartil\n",
    "    FROM por_cliente\n",
    ")\n",
    "SELECT cuartil, COUNT(*) AS clientes,\n",
    "       ROUND(MIN(soles), 2) AS el_mas_bajo,\n",
    "       ROUND(MAX(soles), 2) AS el_mas_alto,\n",
    "       ROUND(SUM(soles), 2) AS soles\n",
    "FROM en_cuartiles\n",
    "GROUP BY cuartil\n",
    "ORDER BY cuartil;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`NTILE(4)` parte la lista ordenada en cuatro montones del mismo\n",
    "tamaño. Como son 119 clientes y no se divide exacto, el último se queda con 29 y\n",
    "los otros tres con 30.\n",
    "\n",
    "Y ahí tienes una segmentación de clientes hecha en SQL, sin machine learning\n",
    "ni nada: **el cuartil de arriba, 30 clientes, se lleva 208.380 soles de los\n",
    "517.442 totales, o sea el 40%.** Los 29 de abajo aportan 59.795, un 12%.\n",
    "Eso es una conversación de negocio completa 💡"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ventanas sobre grupos, que parece imposible y no lo es"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT canal, COUNT(*) AS pedidos,\n",
    "       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS porcentaje\n",
    "FROM pedidos\n",
    "GROUP BY canal\n",
    "ORDER BY pedidos DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ese `SUM(COUNT(*)) OVER ()` es raro de ver la primera vez y es\n",
    "perfectamente legal: primero el `GROUP BY` arma los cuatro grupos con\n",
    "su cuenta, y después la ventana suma esas cuatro cuentas. Un\n",
    "`COUNT` de `COUNT`.\n",
    "\n",
    "Así sacas porcentajes sobre el total sin ninguna subconsulta, y es de las\n",
    "cosas que más quedan bien en un reporte.\n",
    "\n",
    "Con `PARTITION BY` el porcentaje se calcula sobre el grupo que\n",
    "quieras. Aquí, cuánto pesa cada cliente dentro de su propia ciudad."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT c.ciudad, c.nombre, ROUND(SUM(p.monto), 2) AS soles,\n",
    "       ROUND(100.0 * SUM(p.monto) / SUM(SUM(p.monto)) OVER (PARTITION BY c.ciudad), 1) AS peso_en_su_ciudad\n",
    "FROM clientes c JOIN pedidos p ON p.id_cliente = c.id\n",
    "WHERE c.ciudad = 'Lima'\n",
    "GROUP BY c.id, c.ciudad, c.nombre\n",
    "ORDER BY soles DESC\n",
    "LIMIT 5;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Distribuidora Paz 058 es el 15,9% de toda Lima ella sola. Cuando un cliente\n",
    "pesa así, el riesgo tiene nombre y apellido 😬"
   ]
  },
  {
   "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 mejor cliente de cada ciudad\n",
    "\n",
    "Un solo cliente por ciudad: el que más soles dejó."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 1\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. El día anterior y el siguiente\n",
    "\n",
    "Ventas de los primeros días de junio de 2026, con la del día\n",
    "de antes y la del día de después al lado."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 2\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Cuánto se aleja cada pedido del promedio de su canal\n",
    "\n",
    "Los primeros pedidos con su monto, el promedio de su canal\n",
    "y la diferencia."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 3\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Ranking de segmentos\n",
    "\n",
    "Los segmentos por número de clientes, con las tres formas de\n",
    "numerar."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 4\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Comparar contra el mejor\n",
    "\n",
    "Los pedidos de Tienda, con el mayor del canal al lado y qué\n",
    "porcentaje representa cada uno."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 5\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. El acumulado del año pasado\n",
    "\n",
    "Los primeros meses de 2025 con la media de todos los meses y\n",
    "el acumulado, escribiendo el marco a mano."
   ]
  },
  {
   "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 pedido más caro de cada canal, en los\n",
    "cuatro motores."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## El acumulado que empieza acumulado"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y ahora la trampa de las ventanas. El total final sale bien, que es justo lo que hace que nadie la mire 🔍"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### La trampa\n",
    "\n",
    "Sacas el acumulado de ventas por fecha con una función de ventana. Corre a la primera y las cifras suben, como debe ser.\n",
    "\n",
    "```\n",
    "SELECT fecha, SUM(monto) OVER (ORDER BY fecha) AS acumulado\n",
    "FROM pedidos ORDER BY fecha LIMIT 2;\n",
    "-- 2025-01-01   657.1\n",
    "-- 2025-01-01   657.1\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",
    "Numeras las ventas por monto y dos empatan en el mismo importe. Quieres que las dos lleven el número 1 y que la siguiente sea la 2.\n",
    "\n",
    "a) DENSE_RANK\n",
    "\n",
    "b) ROW_NUMBER\n",
    "\n",
    "c) RANK\n",
    "\n",
    "d) NTILE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lo que te llevas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 🪟 Una función de ventana calcula un resumen **sin aplastar las\n",
    "filas**. La marca es el `OVER`.\n",
    "\n",
    "- 🧩 `PARTITION BY` es el `GROUP BY` de las ventanas, y\n",
    "la diferencia es que no reduce nada.\n",
    "\n",
    "- 🥇 `ROW_NUMBER` nunca empata, `RANK` empata y salta,\n",
    "`DENSE_RANK` empata y no salta. Sin empates las tres son\n",
    "iguales.\n",
    "\n",
    "- 🚫 No se puede filtrar por una ventana en el `WHERE`: se calcula\n",
    "después. Se mete en un CTE y se filtra fuera.\n",
    "\n",
    "- 📈 Con `ORDER BY` dentro del `OVER` tienes acumulados;\n",
    "con `ROWS BETWEEN` tienes medias móviles.\n",
    "\n",
    "- ↔️ `LAG` y `LEAD` traen la fila anterior y la\n",
    "siguiente. Es el crecimiento mes a mes en una columna.\n",
    "\n",
    "- 📊 `NTILE(4)` parte en cuartiles: el 25% de arriba de esta tienda\n",
    "son 30 clientes y el 40% de la venta.\n",
    "\n",
    "- 🗓️ Están en los cuatro motores con la misma sintaxis, pero MySQL las tiene\n",
    "desde la 8.0 y SQLite desde la 3.25, las dos de 2018.\n",
    "\n",
    "Y si de todo el capítulo te llevas una sola frase, que sea esta:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Si tu ventana lleva `ORDER BY`, escribe el marco. El de por\n",
    "defecto casi nunca es el que querías."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y para saber si el acumulado que acabas de sacar significa algo o es\n",
    "ruido del mes, eso ya es otra caja de herramientas:\n",
    "[libro de estadística](https://missyera.com/guias/estadistica-desde-cero/) 📐\n",
    "\n",
    "En el capítulo 10 dejamos de leer y empezamos a escribir: crear tablas,\n",
    "tipos, claves y los cuatro autoincrementos, que es donde los motores se vuelven a\n",
    "separar del todo.\n",
    "\n",
    "Que tengas lindo día! 🌸"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "Ese era el capítulo 9 de **SQL desde cero**. El texto completo, con las salidas de cada bloque, está en https://missyera.com/guias/sql-desde-cero/funciones-de-ventana/\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
}