{
 "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 soluciones 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",
    "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": [
    "`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": [
    "q(\"\"\"\n",
    "WITH por_ciudad AS (\n",
    "    SELECT c.ciudad, 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.ciudad, c.id, c.nombre\n",
    "),\n",
    "ranking AS (\n",
    "    SELECT ciudad, nombre, soles,\n",
    "           ROW_NUMBER() OVER (PARTITION BY ciudad ORDER BY soles DESC) AS puesto\n",
    "    FROM por_ciudad\n",
    ")\n",
    "SELECT ciudad, nombre, ROUND(soles, 2) AS soles\n",
    "FROM ranking\n",
    "WHERE puesto = 1\n",
    "ORDER BY soles DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "ciudad    nombre                      soles\n",
    "--------  --------------------------  --------\n",
    "Lima      Distribuidora Paz 058       10131.72\n",
    "Piura     Almacenes Vega 067          9516.06\n",
    "Chiclayo  Market Central 087          8148.56\n",
    "Trujillo  Restaurante Miraflores 093  8119.95\n",
    "Arequipa  Cafe del Puerto 060         7948.71\n",
    "Cusco     Mayorista Peru 027          7732.46\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dos CTE encadenados: uno agrupa y el otro numera. Se podría hacer en uno\n",
    "solo, pero así se lee mejor y en SQL eso vale mucho."
   ]
  },
  {
   "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": [
    "q(\"\"\"\n",
    "WITH ventas_dia AS (\n",
    "    SELECT fecha, ROUND(SUM(monto), 2) AS soles\n",
    "    FROM pedidos\n",
    "    WHERE fecha BETWEEN '2026-06-01' AND '2026-06-10'\n",
    "    GROUP BY fecha\n",
    ")\n",
    "SELECT fecha, soles,\n",
    "       LAG(soles) OVER (ORDER BY fecha) AS dia_anterior,\n",
    "       LEAD(soles) OVER (ORDER BY fecha) AS dia_siguiente\n",
    "FROM ventas_dia\n",
    "ORDER BY fecha\n",
    "LIMIT 5;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "fecha       soles    dia_anterior  dia_siguiente\n",
    "----------  -------  ------------  -------------\n",
    "2026-06-01  1854.01                1133.93\n",
    "2026-06-02  1133.93  1854.01       413.38\n",
    "2026-06-03  413.38   1133.93       890.15\n",
    "2026-06-04  890.15   413.38        756.51\n",
    "2026-06-05  756.51   890.15        1525.18\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ojo con una trampa que no se ve: si un día no tuvo ventas, no hay fila, así\n",
    "que `LAG` te trae el día que sí vendió, no \"el día de ayer\". Para que\n",
    "sea de verdad ayer, necesitas el calendario recursivo del capítulo 8."
   ]
  },
  {
   "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": [
    "q(\"\"\"\n",
    "SELECT id, canal, monto,\n",
    "       ROUND(AVG(monto) OVER (PARTITION BY canal), 2) AS promedio_del_canal,\n",
    "       ROUND(monto - AVG(monto) OVER (PARTITION BY canal), 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": [
    "```\n",
    "id  canal        monto   promedio_del_canal  diferencia\n",
    "--  -----------  ------  ------------------  ----------\n",
    "1   Web          892.06  615.99              276.07\n",
    "2   Marketplace  731.09  611.5               119.59\n",
    "3   Tienda       407.39  598.41              -191.02\n",
    "4   Tienda       358.23  598.41              -240.18\n",
    "5   Web          844.94  615.99              228.95\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Esa columna de diferencia es el primer paso para buscar valores raros: si un\n",
    "pedido se aleja muchísimo del promedio de su canal, o es una venta buenísima o\n",
    "es un error de carga, y las dos cosas valen la pena mirarlas."
   ]
  },
  {
   "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": [
    "q(\"\"\"\n",
    "SELECT segmento, 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 segmento;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "segmento    clientes  fila  rango  rango_denso\n",
    "----------  --------  ----  -----  -----------\n",
    "Horeca      36        1     1      1\n",
    "Bodega      35        2     2      2\n",
    "Minimarket  25        3     3      3\n",
    "Mayorista   24        4     4      4\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aquí las tres columnas dan lo mismo, y ese es el punto: **sin empates\n",
    "las tres son idénticas**. Por eso la diferencia no se nota hasta el día\n",
    "que hay un empate y el reporte sale mal."
   ]
  },
  {
   "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": [
    "q(\"\"\"\n",
    "SELECT canal, id, monto,\n",
    "       FIRST_VALUE(monto) OVER (PARTITION BY canal ORDER BY monto DESC) AS el_mayor_del_canal,\n",
    "       ROUND(100.0 * monto / FIRST_VALUE(monto) OVER (PARTITION BY canal ORDER BY monto DESC), 1) AS respecto_al_mayor\n",
    "FROM pedidos\n",
    "WHERE monto IS NOT NULL AND canal = 'Tienda'\n",
    "ORDER BY monto DESC\n",
    "LIMIT 4;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "canal   id   monto    el_mayor_del_canal  respecto_al_mayor\n",
    "------  ---  -------  ------------------  -----------------\n",
    "Tienda  151  1236.97  1236.97             100.0\n",
    "Tienda  142  1170.3   1236.97             94.6\n",
    "Tienda  236  1170.08  1236.97             94.6\n",
    "Tienda  361  1150.04  1236.97             93.0\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`FIRST_VALUE` trae el primer valor de la ventana. Con\n",
    "`ORDER BY monto DESC`, el primero es el mayor. Y existe\n",
    "`LAST_VALUE`, que tiene fama de traicionera porque por defecto la\n",
    "ventana termina en la fila actual: para que sea el último de verdad hay que\n",
    "escribirle `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`\n",
    "a mano."
   ]
  },
  {
   "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": [
    "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 (), 2) AS media_de_todos,\n",
    "       ROUND(SUM(soles) OVER (ORDER BY mes ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS acumulado\n",
    "FROM ventas_mes\n",
    "ORDER BY mes\n",
    "LIMIT 4;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "mes      soles     media_de_todos  acumulado\n",
    "-------  --------  --------------  ---------\n",
    "2025-01  36743.94  29591.88        36743.94\n",
    "2025-02  20231.23  29591.88        56975.17\n",
    "2025-03  36274.87  29591.88        93250.04\n",
    "2025-04  35680.53  29591.88        128930.57\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ese `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` es\n",
    "exactamente lo que hace `OVER (ORDER BY mes)` por defecto. Lo escribo\n",
    "entero aquí para que lo reconozcas cuando lo veas, porque en código de trabajo\n",
    "aparece muchísimo.\n",
    "\n",
    "Y fíjate que `AVG(soles) OVER ()`, sin `ORDER BY`, sí\n",
    "mira todas las filas: 29.591 es la media de los 18 meses, y se repite igual en\n",
    "todas."
   ]
  },
  {
   "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": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "-- Se escribe IGUAL en PostgreSQL, MySQL 8, SQL Server y SQLite 3.25+.\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 FROM ranking WHERE puesto = 1;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Otra vez una sola versión para los cuatro. Las ventanas llegaron tarde a\n",
    "todos los motores, así que cuando llegaron ya estaban estandarizadas y nadie se\n",
    "inventó su propia sintaxis 🌟\n",
    "\n",
    "Lo único que cambia es la versión mínima, y la que más muerde es MySQL: si tu\n",
    "empresa sigue en 5.7, esta consulta no corre y hay que resolverla con\n",
    "subconsultas correlacionadas, que es bastante más feo."
   ]
  },
  {
   "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**\n",
    "\n",
    "La primera fila ya trae el acumulado de las dos 🔍 El primer pedido es de 97,15 y ahí dice 657,10.\n",
    "\n",
    "Cuando pones `ORDER BY` en una ventana sin decir el marco, el marco por defecto es `RANGE`, y RANGE trabaja por **valor**: todas las filas con la misma fecha son el mismo escalón y se suman juntas de golpe. Si quieres fila a fila, hay que pedirlo: `ROWS UNBOUNDED PRECEDING`.\n",
    "\n",
    "Y el motivo por el que esto engaña tanto es que el acumulado sigue subiendo y el total final sale bien. Solo están mal las filas de en medio, que son las que alguien va a mirar para decir \"el día 3 llevábamos tanto\".\n",
    "\n",
    "Con fechas siempre hay empates, así que en la práctica: si tu ventana lleva ORDER BY, escribe el marco. `ROWS` casi siempre es lo que querías decir."
   ]
  },
  {
   "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\n",
    "\n",
    "---\n",
    "\n",
    "**La correcta es la a.**\n",
    "\n",
    "*b)* Numera del uno al final sin repetir nunca, así que a una de las dos le tocaría el 2 y sería arbitrario cuál.\n",
    "\n",
    "*c)* Empata las dos en 1, y después salta al 3. Casi es lo que quieres, pero mira qué pasa con la siguiente.\n",
    "\n",
    "*d)* Reparte las filas en grupos del mismo tamaño, que es otra cosa.\n",
    "\n",
    "Las tres numeran distinto cuando hay empates, y por eso existen las tres."
   ]
  },
  {
   "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
}