Consultas útiles

Las consultas que más te van a pedir para dashboards y reportes: ventas por mes, productos más vendidos, mejores clientes, ticket promedio, comparativas, búsquedas y detección de duplicados. Usan el esquema e-commerce, pero se adaptan fácilmente a cualquier proyecto.

Pedidos válidos

En casi todos los reportes filtramos estado NOT IN ('pendiente','cancelado','reembolsado') para contar sólo ventas reales. Ajusta esa lista a tu negocio.

Ventas

-- Top 10 productos más vendidos (unidades e ingresos) en los últimos 30 días
SELECT pi.producto_id, pi.nombre_producto,
       SUM(pi.cantidad) AS unidades,
       SUM(pi.total)    AS ingresos
FROM pedido_items pi
JOIN pedidos p ON p.id = pi.pedido_id
WHERE p.estado NOT IN ('pendiente','cancelado','reembolsado')
  AND p.creado_en >= CURDATE() - INTERVAL 30 DAY
GROUP BY pi.producto_id, pi.nombre_producto
ORDER BY unidades DESC
LIMIT 10;

-- Ventas por mes del año actual (número de pedidos, total y ticket promedio)
SELECT DATE_FORMAT(creado_en, '%Y-%m') AS mes,
       COUNT(*)            AS pedidos,
       SUM(total)          AS ventas,
       ROUND(AVG(total), 2) AS ticket_promedio
FROM pedidos
WHERE estado NOT IN ('pendiente','cancelado','reembolsado')
  AND YEAR(creado_en) = YEAR(CURDATE())
GROUP BY mes
ORDER BY mes;

-- Ventas por día de los últimos 14 días, incluyendo días SIN ventas (CTE recursiva)
WITH RECURSIVE dias AS (
  SELECT CURDATE() - INTERVAL 13 DAY AS dia
  UNION ALL
  SELECT dia + INTERVAL 1 DAY FROM dias WHERE dia < CURDATE()
)
SELECT d.dia, COALESCE(SUM(p.total), 0) AS ventas, COUNT(p.id) AS pedidos
FROM dias d
LEFT JOIN pedidos p ON DATE(p.creado_en) = d.dia
  AND p.estado NOT IN ('pendiente','cancelado','reembolsado')
GROUP BY d.dia
ORDER BY d.dia;

-- Este mes vs. mes anterior (variación porcentual)
SELECT
  SUM(CASE WHEN creado_en >= DATE_FORMAT(CURDATE(), '%Y-%m-01') THEN total ELSE 0 END) AS mes_actual,
  SUM(CASE WHEN creado_en <  DATE_FORMAT(CURDATE(), '%Y-%m-01')
            AND creado_en >= DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m-01') THEN total ELSE 0 END) AS mes_anterior
FROM pedidos
WHERE estado NOT IN ('pendiente','cancelado','reembolsado')
  AND creado_en >= DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m-01');
-- variación = (mes_actual - mes_anterior) / mes_anterior * 100  (calcúlala en tu código)

-- Ventas por categoría con porcentaje del total (funciones de ventana, MySQL 8+)
SELECT c.nombre AS categoria,
       SUM(pi.total) AS ventas,
       ROUND(100 * SUM(pi.total) / SUM(SUM(pi.total)) OVER (), 1) AS porcentaje
FROM pedido_items pi
JOIN pedidos p    ON p.id = pi.pedido_id
JOIN productos pr ON pr.id = pi.producto_id
LEFT JOIN categorias c ON c.id = pr.categoria_id
WHERE p.estado NOT IN ('pendiente','cancelado','reembolsado')
GROUP BY c.id, c.nombre
ORDER BY ventas DESC;

-- Utilidad por producto (requiere la columna costo)
SELECT pr.nombre,
       SUM(pi.cantidad) AS unidades,
       SUM(pi.total) AS ingresos,
       SUM(pi.cantidad * pr.costo) AS costo_total,
       SUM(pi.total) - SUM(pi.cantidad * pr.costo) AS utilidad
FROM pedido_items pi
JOIN productos pr ON pr.id = pi.producto_id
JOIN pedidos p ON p.id = pi.pedido_id
WHERE p.estado = 'entregado'
GROUP BY pr.id
ORDER BY utilidad DESC;

-- Horas pico de compra
SELECT HOUR(creado_en) AS hora, COUNT(*) AS pedidos
FROM pedidos GROUP BY hora ORDER BY pedidos DESC;

-- Acumulado del año mes a mes (running total)
SELECT mes, ventas, SUM(ventas) OVER (ORDER BY mes) AS acumulado
FROM (
  SELECT DATE_FORMAT(creado_en, '%Y-%m') AS mes, SUM(total) AS ventas
  FROM pedidos
  WHERE estado NOT IN ('pendiente','cancelado','reembolsado') AND YEAR(creado_en) = YEAR(CURDATE())
  GROUP BY mes
) t;

Clientes

-- Mejores clientes (por monto gastado)
SELECT c.id, c.nombre, c.email,
       COUNT(p.id) AS pedidos,
       SUM(p.total) AS total_gastado,
       MAX(p.creado_en) AS ultima_compra
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id AND p.estado NOT IN ('pendiente','cancelado','reembolsado')
GROUP BY c.id
ORDER BY total_gastado DESC
LIMIT 20;

-- Clientes que no compran desde hace más de 90 días (para campaña de reactivación)
SELECT c.nombre, c.email, MAX(p.creado_en) AS ultima_compra
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id
WHERE c.acepta_promos = 1
GROUP BY c.id
HAVING ultima_compra < NOW() - INTERVAL 90 DAY;

-- Nuevos vs. recurrentes por mes
SELECT DATE_FORMAT(p.creado_en, '%Y-%m') AS mes,
       SUM(p.creado_en = primera.fecha) AS pedidos_clientes_nuevos,
       SUM(p.creado_en > primera.fecha) AS pedidos_recurrentes
FROM pedidos p
JOIN (SELECT cliente_id, MIN(creado_en) AS fecha FROM pedidos GROUP BY cliente_id) primera
  ON primera.cliente_id = p.cliente_id
GROUP BY mes ORDER BY mes;

-- Ranking de clientes por ciudad (el mejor cliente de cada ciudad)
SELECT * FROM (
  SELECT c.nombre, d.ciudad, SUM(p.total) AS gastado,
         ROW_NUMBER() OVER (PARTITION BY d.ciudad ORDER BY SUM(p.total) DESC) AS posicion
  FROM clientes c
  JOIN direcciones d ON d.cliente_id = c.id AND d.predeterminada = 1
  JOIN pedidos p ON p.cliente_id = c.id
  GROUP BY c.id, d.ciudad
) t WHERE posicion = 1;

Búsquedas

-- Búsqueda simple sin distinguir acentos ni mayúsculas (collation _ci / _ai)
SELECT id, nombre FROM productos
WHERE nombre LIKE CONCAT('%', 'cafe', '%') COLLATE utf8mb4_0900_ai_ci;   -- encuentra "Café"

-- Búsqueda en varias tablas con UNION (buscador global de un panel)
SELECT 'producto' AS tipo, id, nombre AS titulo FROM productos WHERE nombre LIKE '%ana%'
UNION ALL
SELECT 'cliente', id, CONCAT(nombre, ' ', COALESCE(apellidos, '')) FROM clientes WHERE nombre LIKE '%ana%' OR email LIKE '%ana%'
UNION ALL
SELECT 'pedido', id, folio FROM pedidos WHERE folio LIKE '%ana%'
LIMIT 20;

-- Texto completo en modo booleano: debe contener "café", no "descafeinado", "oaxaca" suma relevancia
SELECT nombre FROM productos
WHERE MATCH(nombre, descripcion) AGAINST ('+café -descafeinado oaxaca*' IN BOOLEAN MODE);

Mantenimiento y calidad de datos

-- Correos duplicados
SELECT email, COUNT(*) AS veces FROM clientes GROUP BY email HAVING veces > 1;

-- Productos que nunca se han vendido
SELECT pr.id, pr.nombre FROM productos pr
LEFT JOIN pedido_items pi ON pi.producto_id = pr.id
WHERE pi.id IS NULL;

-- Pedidos cuyo total no cuadra con sus partidas
SELECT p.id, p.total, p.subtotal, SUM(pi.total) AS suma_items
FROM pedidos p JOIN pedido_items pi ON pi.pedido_id = p.id
GROUP BY p.id
HAVING ABS(p.subtotal - suma_items) > 0.01;

-- Tamaño de cada tabla (MB)
SELECT table_name AS tabla, ROUND((data_length + index_length) / 1024 / 1024, 2) AS mb, table_rows AS filas_aprox
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY mb DESC;

-- Carritos abandonados (más de 3 días sin cambios) para limpiar
DELETE FROM carritos WHERE cliente_id IS NULL AND actualizado_en < NOW() - INTERVAL 3 DAY;

Paginación eficiente

-- Offset clásico (lento en páginas altas)
SELECT * FROM pedidos ORDER BY id DESC LIMIT 20 OFFSET 4000;

-- Por cursor (siempre rápido): pasa el último id que recibiste
SELECT * FROM pedidos WHERE id < 81234 ORDER BY id DESC LIMIT 20;

Rendimiento

Antes de poner una consulta en un dashboard, ejecútala con EXPLAIN delante. Si ves type: ALL sobre una tabla grande, falta un índice. Para reportes con filtros por fecha, un índice compuesto como (estado, creado_en) hace maravillas.

Funciones sobre columnas indexadas

WHERE DATE(creado_en) = '2026-10-18' no puede usar el índice de creado_en. Escríbelo como rango: WHERE creado_en >= '2026-10-18' AND creado_en < '2026-10-19'.