{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL: preguntarle a una base de verdad\n",
    "\n",
    "Cuaderno de practica del libro **SQL desde cero** de Miss Yera.\n",
    "Corre de arriba abajo. No necesitas instalar nada si lo abres en Google Colab.\n",
    "\n",
    "La base es una distribuidora peruana con cinco tablas relacionadas de verdad y\n",
    "la suciedad que traen las bases reales: pedidos sin cliente, montos en NULL,\n",
    "clientes con varias direcciones y nombres con espacios de mas.\n",
    "\n",
    "Libro completo: https://missyera.com/guias/sql-desde-cero/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se baja la base, se abre una conexion y se define `q()`, que corre una consulta y devuelve el resultado como tabla de pandas. Todo lo demas del cuaderno es SQL de verdad: pandas solo lo muestra bonito."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import urllib.request\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "URL = \"https://missyera.com/static/datasets/tienda.db\"\n",
    "urllib.request.urlretrieve(URL, \"tienda.db\")\n",
    "con = sqlite3.connect(\"tienda.db\")\n",
    "\n",
    "def q(sql):\n",
    "    \"\"\"Corre una consulta y devuelve el resultado como tabla.\"\"\"\n",
    "    return pd.read_sql_query(sql, con)\n",
    "\n",
    "q(\"SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Entrar a una base que no conoces\n",
    "\n",
    "Es lo primero que vas a hacer en un trabajo y casi nunca te lo ensenan: antes de escribir una consulta, mira cuantas filas tiene cada tabla y como se llaman las columnas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for tabla in [\"clientes\", \"direcciones\", \"productos\", \"pedidos\", \"detalle\"]:\n",
    "    n = q(f\"SELECT COUNT(*) AS n FROM {tabla}\")[\"n\"][0]\n",
    "    print(f\"{tabla:12} {n:5} filas\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"SELECT * FROM pedidos LIMIT 5\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. SELECT, WHERE y ORDER BY\n",
    "\n",
    "Con estas tres palabras ya se contestan preguntas. `LIMIT` va siempre en las pruebas: te ahorra traerte un millon de filas por equivocacion."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT id, fecha, canal, monto\n",
    "FROM pedidos\n",
    "WHERE monto > 1000 AND canal = 'Web'\n",
    "ORDER BY monto DESC\n",
    "LIMIT 5\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. NULL no es cero, y esta es la trampa que mas cuesta\n",
    "\n",
    "Hay pedidos sin monto. Compara las dos cuentas de abajo antes de seguir leyendo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*)      AS filas,\n",
    "       COUNT(monto)  AS con_monto,\n",
    "       AVG(monto)    AS promedio\n",
    "FROM pedidos\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`COUNT(*)` cuenta filas y `COUNT(monto)` cuenta valores. La diferencia entre los dos numeros son los pedidos sin monto, y el `AVG` los ignoro: **divide entre los que si tienen dato, no entre el total**.\n",
    "\n",
    "Si tu quisieras que un pedido sin monto cuente como cero, tienes que decirlo:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT AVG(monto)               AS promedio_ignorando_nulos,\n",
    "       AVG(COALESCE(monto, 0))  AS promedio_contando_cero\n",
    "FROM pedidos\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Los dos numeros son correctos y contestan preguntas distintas. El error no es elegir mal: es no darte cuenta de que estabas eligiendo."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. GROUP BY, o la pregunta por canal\n",
    "\n",
    "Un `GROUP BY` parte la tabla en grupos y devuelve una fila por grupo. Es el 80% de lo que se pide en un trabajo."
   ]
  },
  {
   "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 venta\n",
    "FROM pedidos\n",
    "GROUP BY canal\n",
    "ORDER BY venta DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. JOIN, que es lo que hace que esto sea una base y no cinco Excel\n",
    "\n",
    "El ticket esta en `pedidos` y la ciudad esta en `clientes`. Sin JOIN esta pregunta no se puede contestar."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT c.ciudad,\n",
    "       COUNT(*)              AS pedidos,\n",
    "       ROUND(AVG(p.monto),2) AS ticket\n",
    "FROM pedidos p\n",
    "JOIN clientes c ON c.id = p.id_cliente\n",
    "GROUP BY c.ciudad\n",
    "ORDER BY ticket DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. El error mas caro del cuaderno\n",
    "\n",
    "Este es el que hay que ejecutar despacio. Las dos consultas de abajo suman la misma columna de la misma tabla."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS filas, ROUND(SUM(p.monto),2) AS suma\n",
    "FROM pedidos p\n",
    "JOIN detalle d ON d.id_pedido = p.id\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"SELECT COUNT(*) AS filas, ROUND(SUM(monto),2) AS suma FROM pedidos\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La venta de la tienda se triplico sola.\n",
    "\n",
    "Cada pedido tiene unas tres lineas de detalle, asi que el JOIN repite la fila del pedido una vez por linea, y con ella repite el monto. Se llama **fan-out** y no da error: da un numero grande y creible.\n",
    "\n",
    "La regla para no caer: **suma en el nivel donde el dato vive una sola vez**. El monto vive en `pedidos`; las cantidades viven en `detalle`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT c.ciudad, ROUND(SUM(d.cantidad * d.precio_unit),2) AS soles\n",
    "FROM clientes c\n",
    "JOIN pedidos  p ON p.id_cliente = c.id\n",
    "JOIN detalle  d ON d.id_pedido  = p.id\n",
    "GROUP BY c.ciudad\n",
    "ORDER BY soles DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Los que no hicieron pareja\n",
    "\n",
    "`INNER` deja solo lo que empareja; `LEFT` conserva todo lo de la izquierda. La diferencia entre los dos conteos son los pedidos huerfanos."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT (SELECT COUNT(*) FROM pedidos p JOIN clientes c ON c.id = p.id_cliente)      AS con_inner,\n",
    "       (SELECT COUNT(*) FROM pedidos p LEFT JOIN clientes c ON c.id = p.id_cliente) AS con_left\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y el patron que hay que memorizar, `LEFT JOIN` mas `IS NULL`, que es como se buscan los que faltan: clientes que nunca compraron, productos que nunca se vendieron, facturas sin pago."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT c.id, c.nombre, c.ciudad\n",
    "FROM clientes c\n",
    "LEFT JOIN pedidos p ON p.id_cliente = c.id\n",
    "WHERE p.id IS NULL\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. El WHERE que mata tu LEFT JOIN\n",
    "\n",
    "Esta es la que no da error y devuelve mal. Las dos consultas de abajo parecen la misma y no lo son."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sin_ceros = q(\"\"\"\n",
    "SELECT c.id, COUNT(p.id) AS pedidos_grandes\n",
    "FROM clientes c\n",
    "LEFT JOIN pedidos p ON p.id_cliente = c.id\n",
    "WHERE p.monto > 800\n",
    "GROUP BY c.id\n",
    "\"\"\")\n",
    "\n",
    "con_ceros = q(\"\"\"\n",
    "SELECT c.id, COUNT(p.id) AS pedidos_grandes\n",
    "FROM clientes c\n",
    "LEFT JOIN pedidos p ON p.id_cliente = c.id AND p.monto > 800\n",
    "GROUP BY c.id\n",
    "\"\"\")\n",
    "\n",
    "print(\"filtrando en el WHERE:\", len(sin_ceros), \"clientes\")\n",
    "print(\"filtrando en el ON   :\", len(con_ceros), \"clientes\")\n",
    "print(\"clientes con cero pedidos grandes:\", (con_ceros[\"pedidos_grandes\"] == 0).sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "El `WHERE` sobre la tabla derecha tira las filas que el `LEFT` acababa de rellenar con NULL, y tu LEFT JOIN se convirtio en INNER sin que nadie te avise.\n",
    "\n",
    "- Lo que filtra **la tabla derecha** va en el `ON`.\n",
    "- Lo que filtra **la tabla izquierda** va en el `WHERE`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Funciones de ventana\n",
    "\n",
    "Un `GROUP BY` te da el resumen y te quita el detalle. Una ventana calcula el resumen y **no aplasta las filas**. Es lo que mas separa a alguien que sabe SQL de alguien que lo usa."
   ]
  },
  {
   "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 6\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y la reina de las consultas, el top N por grupo. La ventana numera dentro de cada canal y el CTE permite filtrar por ese numero, porque el `WHERE` se ejecuta antes de que la ventana exista."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "WITH ordenados 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 * FROM ordenados WHERE puesto <= 2 ORDER BY canal, puesto\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Tu turno\n",
    "\n",
    "Tres preguntas sin respuesta escrita. Se contestan con lo que hay arriba y son las que de verdad ensenan:\n",
    "\n",
    "1. Que producto se vende mas, contado por unidades y no por numero de pedidos.\n",
    "2. Cuantos clientes compraron en 2025 y no volvieron en 2026.\n",
    "3. Cual es el segundo pedido mas caro de cada ciudad.\n",
    "\n",
    "Si la 3 se te resiste, mira otra vez la celda del top N."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lo que te llevas\n",
    "\n",
    "No hubo ningun truco: `SELECT`, `WHERE`, `GROUP BY`, `JOIN` y una ventana. Con eso se contesta casi todo lo que se pide en una empresa.\n",
    "\n",
    "Y las dos que hay que llevarse de memoria son las que no dan error: **NULL no es cero** y **un JOIN con detalle infla las sumas**. Cuenta filas antes y despues de cada JOIN, siempre."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}