Examen SQL Developer

Perfil: SQL Developer

Duración estimada: 60–75 minutos
Nivel de dificultad: Progresivo
Objetivo: Evaluar capacidad de análisis del modelo relacional, interpretación de requerimientos
funcionales complejos, uso de SQL avanzado, y toma de decisiones correctas ante modelos
con ruido o desnormalización. Optimización de Consultas.

Contexto:

El cliente nos ha solicitado apoyar al equipo de Comercial en algunas de las consultas y

requerimientos. Además de las consultas y requerimientos, no han comentado que algunas de
las consultas que ellos realizan contienen datos equivocados o que el performance no es
óptimo. El equipo de Levantamiento técnico hizo un dump de las estructuras de la base de
datos SQL para apoyar al equipo de desarrollo de SQL de Arkon pueda revisar y realizar las
tareas indicadas. Han validado que nada tiene que ver con la infraestructura y que todos los
problemas que comentan se enfocan en las capas lógicas y semánticas.

Estructura de la base de datos:

##1. clientes
CREATE TABLE clientes (
cliente_id VARCHAR(100) PRIMARY KEY,
nombre TEXT,
correo TEXT,
activo VARCHAR(5)
);

##2. ordenes
CREATE TABLE ordenes (
orden_id INT PRIMARY KEY,
cliente_id VARCHAR(100),
fecha_orden TEXT,
total_orden TEXT,
estatus VARCHAR(20)
);

##3. detalle_orden
CREATE TABLE detalle_orden (
detalle_id SERIAL PRIMARY KEY,
orden_id INT,
producto_id TEXT,
cantidad TEXT,
precio_unitario TEXT
);
##4. productos
CREATE TABLE productos (
producto_id TEXT PRIMARY KEY,
nombre_producto TEXT,
precio_lista TEXT
);

##5. productos_atributos
# Guarda los atributos de los productos (categoria, color, tamaño, peso)
# | producto_id | atributo_code | valor |
# | 1 | "categoria" | "Tecnologia" |
# | 1 | "categoria" | "Audio" |
# | 1 | "color" | "Rojo" |
# | 1 | "color" | "Rojo" |
# | 2 | "categoria" | "Ropa" |
# | 2 | "categoria" | "Playeras" |
# | 2 | "color" | "Negro" |
# | 2 | "talla" | "S" |
CREATE TABLE productos_atributos (
producto_id TEXT,
atributo_code TEXT,
valor TEXT
);

##6. devoluciones
CREATE TABLE devoluciones (
devolucion_id INT PRIMARY KEY,
orden_id TEXT,
producto_id TEXT,
cantidad_devuelta TEXT
);

##7. usuarios_login
CREATE TABLE usuarios_login (
usuario_id INT PRIMARY KEY,
cliente_id TEXT,
fecha_registro TEXT,
navegador TEXT,
ubicacion_ip INT
);

##8. comentarios
CREATE TABLE comentarios (
comentario_id INT PRIMARY KEY,
producto_id TEXT,
cliente_id TEXT,
calificacion TEXT,
texto TEXT
);

##9. cupones_redimidos
CREATE TABLE cupones_redimidos (
cupon_id TEXT PRIMARY KEY,
cliente_id TEXT,
codigo TEXT,
monto_descuento TEXT,
fecha TEXT
);
Tus tareas:

1.​ Analiza el modelo de datos de la manera más conveniente para entender el panorama
de la arquitectura lógica que tiene el cliente y facilitar los análisis Si crees necesario,
realiza los diagramas necesarios.​

2.​ El cliente nos ha dado el siguiente requerimiento:

Requerimiento de Negocio (AN100).

Generar un reporte que muestre los clientes activos que hayan comprado productos de al
menos 3 categorías distintas en el último año calendario.

Para cada uno, mostrar su cliente_id, nombre, última fecha de compra, el

promedio gastado por orden, y la categoría más frecuente entre sus productos
comprados (en caso de empate, devolver alfabéticamente la primera categoría).

Solo deben considerarse órdenes con estatus 'completada', y que no hayan sido
completamente devueltas (es decir, si el total de productos en la orden fueron devueltos, se
debe excluir).

Construye un query (o conjunto de CTEs) que cumplan el requerimiento.

3. En una orden de servicio adicional el cliente nos ha indicado que el siguiente query
tiene un performance bastante deficiente (alrededor de 2 horas de ejecución
terminando en un timeout). Nos solicitan:

●​ Hacer una revisión y sugerencias para mejorar el rendimiento de los análisis.

●​ Revisar que el query que entregó el analista cumple con las expectativas del
AN100.
●​ Se realice una sugerencia de query que resuelva los problemas de performance.

Requerimiento de Negocio (AN100).

Identificar a los clientes activos que han comprado al menos una vez por mes durante los
 últimos 6 meses completos (de forma continua), y calcular su índice de lealtad.

Para cada cliente elegible, mostrar:

●​ cliente_id
●​ nombre
●​ cantidad_total_ordenes_6m
●​ monto_promedio_mensual (de los últimos 6 meses)
●​ mes_con_mayor_gasto (en formato 'YYYY-MM')
●​ indice_lealtad (porcentaje de meses con al menos una compra sobre los 6
meses evaluados — debería ser 100% si cumple condición, pero dejarlo calculado)​

Condiciones:

●​ Solo considerar órdenes completadas y no totalmente devueltas.

●​ Solo contar meses completos (es decir, si hoy es 19 de junio, evaluar desde
diciembre a mayo, no incluir junio).
●​ Excluir órdenes de clientes con actividad promocional (clientes que hayan usado
cupones en ese período).
●​ Excluir productos sin categoría válida registrada en productos_atributos
(atributo_code = 'categoria').

SELECT
c.cliente_id,
[Link],
COUNT(o.orden_id) AS cantidad_total_ordenes_6m,
SUM(o.total_orden) / 6 AS monto_promedio_mensual,
TO_CHAR(o.fecha_orden, 'YYYY-MM') AS mes_con_mayor_gasto,
(COUNT(DISTINCT DATE_TRUNC('month', o.fecha_orden)) / 6.0) * 100 AS indice_lealtad
FROM clientes c
JOIN ordenes o ON CAST(c.cliente_id AS TEXT) = o.cliente_id
JOIN detalle_orden d ON o.orden_id = d.orden_id
JOIN productos_atributos pa ON d.producto_id = pa.producto_id
WHERE [Link] = 'true'
AND [Link] = 'completada'
AND o.fecha_orden >= CURRENT_DATE - INTERVAL '6 months'
AND pa.atributo_code = 'categoria'
GROUP BY c.cliente_id, [Link], TO_CHAR(o.fecha_orden, 'YYYY-MM')
HAVING COUNT(DISTINCT DATE_TRUNC('month', o.fecha_orden)) = 6;

Resultado del Query del Cliente:

cliente_id nombre cantidad_total_ordenes_6m monto_promedio_mensu mes_con_mayor_gast indice_lealta

al o d

123 Juan Pérez 9 1000 2024-03 83.33

124 Laura Gómez 6 800 2024-04 100

125 Ana Ruiz 12 1400 2024-06 100

 126 Carlos Díaz 6 600 2024-05 83.33

127 Sofía López 7 950 2024-06 83.33

128 Martín Herrera 5 700 2024-06 66.67

129 Lucía Torres 8 1100 2024-06 100

130 Diego Ramos 9 1300 2024-05 83.33

131 Elena Castro 6 780 2024-06 100

132 Marcos Peña 7 970 2024-03 100

133 Valentina 10 1500 2024-06 100

Suárez

134 Fernando Ríos 11 1600 2024-06 100

135 Camila Duarte 6 880 2024-04 83.33

136 Pablo Silva 12 1700 2024-06 100

137 Andrea 5 720 2024-05 50

Morales

138 Jorge León 4 600 2024-06 50

139 Daniela Vega 13 1750 2024-06 100

140 Raúl Estrada 7 920 2024-06 83.33

141 Natalia 6 810 2024-06 100

Paredes

4. Nota: Para realizar en conjunto del comité.

El área financiera solicita un reporte que muestre, para cada proveedor, el total de facturas
aprobadas en un periodo dado, con los siguientes campos:

●​ vendor_name
●​ invoice_num
●​ invoice_date
●​ invoice_amount
●​ approval_status

El sistema fuente es Oracle E-Business Suite, y el reporte debe construirse desde las tablas
base del sistema, no desde vistas de negocio ya existentes. Revisando la documentación
encontramos que la pista que las tablas involucradas son tablas nativas de Oracle EBS R12.

a.​ Determinar qué tablas de Oracle EBS contienen estos campos y cómo están
relacionadas entre sí.
b.​ Explicar cómo encontraste esa información (¿usaste Oracle Help, blogs, ChatGPT,
consultaste la documentación de R12?).
c.​ Proporcionar un query que conecte ambas tablas y devuelva los campos
solicitados (puede ser un SELECT con JOIN, sin filtros complejos).​