Diseñar bases de datos y escribir SQL con Cerewro: schema, queries y optimización

Diseña el schema SQL para un sistema de e-commerce con: usuarios, productos con categorías y variantes, carritos, pedidos con líneas de pedido, pagos y reseñas. MySQL 8, con índices y claves foráneas.

CREATE TABLE users (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  email       VARCHAR(255) UNIQUE NOT NULL,
  password    VARCHAR(255) NOT NULL,
  name        VARCHAR(100),
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_email (email)
);

CREATE TABLE categories (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  parent_id   INT,
  FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TABLE products (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  category_id  INT NOT NULL,
  name         VARCHAR(255) NOT NULL,
  price        DECIMAL(10,2) NOT NULL,
  stock        INT DEFAULT 0,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id),
  INDEX idx_category (category_id),
  INDEX idx_price (price)
);

CREATE TABLE orders (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  user_id     INT NOT NULL,
  status      ENUM('pending','paid','shipped','delivered','cancelled') DEFAULT 'pending',
  total       DECIMAL(10,2) NOT NULL,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_user_status (user_id, status)
);

Escribe una query MySQL que muestre los 10 mejores clientes por ventas del último año, con su nombre, total gastado, número de pedidos y ranking.

WITH customer_stats AS (
  SELECT
    u.id,
    u.name,
    u.email,
    COUNT(o.id)       AS num_pedidos,
    SUM(o.total)      AS total_gastado
  FROM users u
  JOIN orders o ON o.user_id = u.id
  WHERE o.status = 'delivered'
    AND o.created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
  GROUP BY u.id, u.name, u.email
)
SELECT
  RANK() OVER (ORDER BY total_gastado DESC) AS ranking,
  name,
  email,
  num_pedidos,
  ROUND(total_gastado, 2) AS total_gastado
FROM customer_stats
ORDER BY total_gastado DESC
LIMIT 10;

Esta query tarda 8 segundos en producción (MySQL). Analiza el plan de ejecución con EXPLAIN, identifica el problema y propón los índices o reescritura necesarios:

SELECT * FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
WHERE u.country = 'ES' AND o.created_at > '2024-01-01'
ORDER BY o.total DESC