Proyecto: Base de Datos E-commerce

En este proyecto final diseñarás y construirás el esquema completo de una base de datos e-commerce: tabla de usuarios con autenticación, productos con categorías anidadas, órdenes con múltiples ítems, historial de pagos y tabla de reseñas. Aplicarás normalización, constraints, índices y relaciones correctas.

Luego escribirás queries analíticas reales: los 10 productos más vendidos del mes, ingresos por categoría, tasa de conversión por campaña, usuarios que compraron A y también B. Estas son exactamente las queries que piden en entrevistas técnicas y que los equipos de datos usan a diario en producción.

-- ESQUEMA COMPLETO E-COMMERCE

CREATE TABLE users (
  id           SERIAL PRIMARY KEY,
  email        VARCHAR(255) UNIQUE NOT NULL,
  nombre       VARCHAR(100) NOT NULL,
  activo       BOOLEAN DEFAULT TRUE,
  creado_en    TIMESTAMP DEFAULT NOW()
);

CREATE TABLE categories (
  id        SERIAL PRIMARY KEY,
  nombre    VARCHAR(100) NOT NULL,
  padre_id  INTEGER REFERENCES categories(id)  -- categorías anidadas
);

CREATE TABLE products (
  id           SERIAL PRIMARY KEY,
  nombre       VARCHAR(200) NOT NULL,
  precio       DECIMAL(10,2) NOT NULL CHECK (precio >= 0),
  stock        INTEGER DEFAULT 0 CHECK (stock >= 0),
  category_id  INTEGER REFERENCES categories(id),
  activo       BOOLEAN DEFAULT TRUE
);

CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER NOT NULL REFERENCES users(id),
  estado     VARCHAR(20) DEFAULT 'pendiente',
  total      DECIMAL(12,2) NOT NULL,
  creado_en  TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_items (
  id          SERIAL PRIMARY KEY,
  order_id    INTEGER NOT NULL REFERENCES orders(id),
  product_id  INTEGER NOT NULL REFERENCES products(id),
  cantidad    INTEGER NOT NULL CHECK (cantidad > 0),
  precio      DECIMAL(10,2) NOT NULL  -- precio histórico al momento de compra
);

CREATE TABLE payments (
  id          SERIAL PRIMARY KEY,
  order_id    INTEGER NOT NULL REFERENCES orders(id),
  monto       DECIMAL(12,2) NOT NULL,
  metodo      VARCHAR(50),  -- 'tarjeta', 'paypal', 'transferencia'
  estado      VARCHAR(20) DEFAULT 'pendiente',
  procesado_en TIMESTAMP
);

-- Índices para rendimiento
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_items_order ON order_items(order_id);
CREATE INDEX idx_items_product ON order_items(product_id);
CREATE INDEX idx_payments_order ON payments(order_id);

-- QUERIES ANALÍTICAS AVANZADAS

-- 1. Reporte de ventas mensual con crecimiento
WITH ventas_mes AS (
  SELECT
    DATE_TRUNC('month', o.creado_en) AS mes,
    COUNT(DISTINCT o.id) AS num_ordenes,
    COUNT(DISTINCT o.user_id) AS clientes_unicos,
    SUM(o.total) AS ingresos
  FROM orders o
  WHERE o.estado = 'completado'
  GROUP BY DATE_TRUNC('month', o.creado_en)
)
SELECT
  TO_CHAR(mes, 'YYYY-MM') AS mes,
  num_ordenes,
  clientes_unicos,
  ingresos,
  ROUND(ingresos / num_ordenes, 2) AS ticket_promedio,
  ROUND((ingresos - LAG(ingresos) OVER (ORDER BY mes)) * 100.0
        / NULLIF(LAG(ingresos) OVER (ORDER BY mes), 0), 2) AS crecimiento_pct
FROM ventas_mes
ORDER BY mes;

-- 2. Top productos por ingresos con ranking por categoría
SELECT
  c.nombre AS categoria,
  p.nombre AS producto,
  SUM(oi.cantidad) AS unidades_vendidas,
  SUM(oi.precio * oi.cantidad) AS ingresos_totales,
  RANK() OVER (
    PARTITION BY c.id
    ORDER BY SUM(oi.precio * oi.cantidad) DESC
  ) AS rank_en_categoria
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN categories c ON c.id = p.category_id
JOIN orders o ON o.id = oi.order_id
WHERE o.estado = 'completado'
  AND o.creado_en >= NOW() - INTERVAL '12 months'
GROUP BY c.id, c.nombre, p.id, p.nombre
HAVING SUM(oi.precio * oi.cantidad) > 0
ORDER BY categoria, ingresos_totales DESC;

-- 3. Análisis RFM: Recency, Frequency, Monetary (segmentación de clientes)
WITH rfm_base AS (
  SELECT
    u.id,
    u.nombre,
    u.email,
    MAX(o.creado_en) AS ultima_compra,
    COUNT(o.id) AS frecuencia,
    SUM(o.total) AS monetario
  FROM users u
  JOIN orders o ON o.user_id = u.id
  WHERE o.estado = 'completado'
  GROUP BY u.id, u.nombre, u.email
)
SELECT
  nombre, email,
  EXTRACT(DAY FROM NOW() - ultima_compra) AS dias_sin_comprar,
  frecuencia AS num_compras,
  monetario AS gasto_total,
  CASE
    WHEN EXTRACT(DAY FROM NOW() - ultima_compra) <= 30
      AND frecuencia >= 5 AND monetario >= 500 THEN 'Campeón'
    WHEN EXTRACT(DAY FROM NOW() - ultima_compra) <= 90
      AND frecuencia >= 2 THEN 'Cliente Leal'
    WHEN EXTRACT(DAY FROM NOW() - ultima_compra) > 180 THEN 'En Riesgo'
    ELSE 'Cliente Regular'
  END AS segmento
FROM rfm_base
ORDER BY monetario DESC;