SQL query

SELECT D.DNUM as ticket, D.DFECHA as fecha, D.DCANT as monto, (D.DCANT + D.DIVA) as montoIVA, D.DPAR1 as vendedor, I.CLICOD as cliente, I.ICOD as productosComprados, I.AICANTF as cantidad, I.AIALMACEN as almacen, Inv.IDESCR as descripcion, Inv.ILISTA3 as precioLista, Inv.IFAM3 as talla, Inv.IFAM4 as color, Inv.IFAM5 as temporada

FROM FDOC as D INNER JOIN FAXINV as I ON D.DNUM = I.FMOV INNER JOIN FINV as Inv ON I.ICOD = Inv.ICOD

Ejercicio 1:¶

Crea un diccionario llamado datos_climaticos donde cada clave es el nombre de una ciudad y el valor es una lista de temperaturas (en grados Celsius) registradas a lo largo de una semana. Para cada ciudad, calcula la temperatura promedio, la temperatura máxima y la mínima de la semana. Determina cuál fue la ciudad con la temperatura promedio más alta y la más baja durante la semana.

In [1]:
import numpy as np

datos_climaticos = {
    
    "CDMX": [32, 34, 35, 36, 34, 33, 31],
    "Chiapas": [22, 20, 21, 23, 24, 25, 19],
    "Nueva York": [30, 31, 29, 28, 27, 26, 30],
    "Jalisco": [28, 30, 29, 31, 32, 33, 34]
    
}

promedios = {
    i: {f'temperatura promedio': np.mean(datos_climaticos[i]),
        f'temperatura maxima': np.max(datos_climaticos[i]),
        f'temperatura minima': np.min(datos_climaticos[i])} for i in datos_climaticos.keys()}

print(promedios)

{'CDMX': {'temperatura promedio': 33.57142857142857, 'temperatura maxima': 36, 'temperatura minima': 31}, 'Chiapas': {'temperatura promedio': 22.0, 'temperatura maxima': 25, 'temperatura minima': 19}, 'Nueva York': {'temperatura promedio': 28.714285714285715, 'temperatura maxima': 31, 'temperatura minima': 26}, 'Jalisco': {'temperatura promedio': 31.0, 'temperatura maxima': 34, 'temperatura minima': 28}}

Ejercicio 2:¶

Asigna a cada letra minúscula un valor, desde 1 para la 'a' hasta 26 para la 'z'. Crea una función que pida al usuario una cadena de letras minúsculas y responde la suma de los valores de las letras en la cadena. (Ejemplo: hola = 8 + 15 + 12 + 1 = 36). Si el usuario te da un número o una letra mayúscula, pídele que lo cambie (Input: Hola. Output: Cambia a minúscula la letra “H” en la posición 1. Input: int2. Output: Cambia el número en la posición 4 por una letra minúscula).

In [1]:
import string

abecedario = list(string.ascii_lowercase)
valores = {abecedario[i]: i + 1 for i in range(0, len(abecedario))}
word = list(input('Ingresa una plabra'))
print(valores)

def parser(word):
    has_uppercase = {char: char.isupper() for char in word}
    is_number = {char: char.isdigit() for char in word}
    upper = any(is_number.values())
    number = any(is_number.values())
    if upper:
        uppers = [i for i in has_uppercase.keys() if has_uppercase[i] == True]
        u_indexes = [word.index(i) for i in uppers]
        print(f'Por favor cambia los siguientes caracteres en estas posiciones {u_indexes} a minúscula -> {uppers}')

    if number:
        numbers = [i for i in is_number.keys() if is_number[i] == True]
        n_indexes = [word.index(i) for i in numbers]
        print(f'Por favor cambia los siguientes caracteres numéricos en estas posiciones {n_indexes} a minúsculas -> {numbers}')
    
    if (not number) and (not upper):
        
        values = sum([ valores[i] for i in word ])
        print(f'Suma: {values}')

parser(word)

{'a': 1, 'b': 2, 'c': 3, 'd': 4, 'e': 5, 'f': 6, 'g': 7, 'h': 8, 'i': 9, 'j': 10, 'k': 11, 'l': 12, 'm': 13, 'n': 14, 'o': 15, 'p': 16, 'q': 17, 'r': 18, 's': 19, 't': 20, 'u': 21, 'v': 22, 'w': 23, 'x': 24, 'y': 25, 'z': 26}
Suma: 0

Ejercicio 4: Recomendador de libros Fuente: https://developer.nytimes.com/docs/books-product/1/overview Elementos a utilizar: Crea una cuenta de Developer en la página de New York Times para tener un API key (https://developer.nytimes.com/) Usa el “Books API”. ¿Qué debe poder hacer tu código? Pedirle al usuario decidir qué lista de “Best Sellers” quiere consultar. Poder escoger si quiere ver los “Best Sellers” actuales o de alguna fecha en específica. Poder escoger un precio específico del libro que quiere adquirir. Poder escoger un rango de edades dirigido para el libro. Resultado: Poder verlo de manera estructurada, poniendo la información clave del libro que estás recomendado para la información que te dio el usuario. Poder acceder rápidamente a la reseña generada por el NYT acerca del libro recomendado. Para los Best Sellers actuales, decirle al usuario dónde lo puede comprar. Poder mostrar toda esta información en una aplicación / pantalla / interfaz gráfica amigable para que el usuario pueda interactuar con los resultados.

In [1]:
import requests
import json
import dash
import dash_table
import dash_html_components as html
import pandas as pd
from dash.dependencies import Input, Output


fecha =  input('Inserta año de publicación (vacío para no filtrar por fecha)') 
precio = input('Inserta el precio (vacío para no filtrar por precio)') 


requestUrl = f"https://api.nytimes.com/svc/books/v3/lists/best-sellers/history.json?price={precio}&api-key=RdGm7emPb3Td7DwsuCUoEeck07CKTxw2"
requestHeaders = {
"Accept": "application/json"
}

response = requests.get(requestUrl, headers=requestHeaders)
best_sellers = json.loads(response.text)
# print(best_sellers['results'])

results = {
        'Titulo':[ str(i['title']).replace("'", '').replace('"', '') for i in best_sellers['results']],
        'Descripcion':[ str(i['description']) for i in best_sellers['results']],
        'Precio':[ str(i['price']) for i in best_sellers['results']],
        'Autor':[ str(i['author']) for i in best_sellers['results']],
        'Editorial':[ str(i['publisher']) for i in best_sellers['results']],
        'Fecha de estreno':[ str(i['ranks_history'][0]['published_date']) if  i['ranks_history'] != [] else 'None' for i in best_sellers['results']],
    }
    
# print(results)
df = pd.DataFrame(results)
df = df[df['Fecha de estreno'].str.contains(fecha)]

Examen técnico Explica con el mayor detalle las respuestas a estas preguntas (45 minutos).

1 - La información del ERP del cliente vive en un data center controlado por un proveedor, con reglas que no permiten adicionar campos, columnas, construir tablas o extraer información. ¿Qué pasos harías para pasar de la información en el ERP, cruzar y transformarla, y mandarla a una herramienta de Business Intelligence con actualizaciones automáticas?¶

  • Para mí lo ideal sería hablar con el proveedor para poder acceder a la información necesaria sin interactuar directamente con el ERP, como mediante una API, o desde un servicio cloud generando una réplica del recurso necesario como EC2 o S3, el cual por asegurar disponibilidad sería la mejor opción. El resto sería hacer un contenedor que haga el ETL de la información y mande las actualizaciones, que en caso de que existan servidores físicos alojarlo ahi o en la nube.

2 - El cliente de la pregunta 1 está evaluando adquirir un servidor físico o en la nube para poder alojar ahí un espejo de la información de su ERP. ¿Qué recomendarías en cuanto a costo o inversión y el rendimiento esperado dadas las características de cada una de las opciones?¶

  • Definitivamente, un servidor cloud, los casos donde es mejor un servidor físico son muy específicos, ya que requieren mucho mantenimiento y son muy caros.

3 - Un cliente solamente cuenta con un servidor de producción donde viven los datos, ¿qué sugerirías para no afectar el desempeño de esta al conectar BI?¶

¿Cuáles serían las implicaciones (tiempo, conocimiento, administración, costo) de esta sugerencia?

  • Generar un Data Warehouse, para separar las consultas del servidor productivo, a un servicio como AWS Redshift. Puede llevar tiempo dependiendo del personal disponible y su capacitación, los costos variarían dependiendo de los recursos como escalabilidad del servicio, su uso y plan de cobro.

4 - Un query que escribiste regresa 10,000 filas únicamente, pero su ejecución toma entre 3 y 4 horas. Por lo mismo, muchas veces no llegan los resultados completos a BI o se pierde la conexión mientras esto ocurre. ¿Qué harías?¶

  • Optimizar los índices de la base de datos, optimizar el query e incluir un cache si la información suele ser estática, ejecutarlo cuando la demanda a la BD es baja como en la madrugada.

5- Te busca un cliente para decirte que los resultados que ve en los tableros no le hacen sentido (ayer si mostraba resultados correctos). ¿Qué pasos harías para darle una respuesta al cliente?¶

  • Verificaría el problema porque muchas veces el cliente suele confundirse, si es que es correcto, vería si hay una solución inmediata y que tan urgente es el problema.
  • Si no hay una solución inmediata le diría al cliente que lo resolveré y mientras buscaría darle una solución inmediata aunque sea provisional mientras se resuelve, y lo atendería dependiendo del nivel de prioridad y otras tareas que haya pendientes.

6 - ¿Qué mejores prácticas sugerirías para garantizar la seguridad de la información del cliente, ya sea en un servidor físico o en la nube?¶

  • Indispensable que use credenciales de seguridad, roles de acceso a la información, contraseñas encriptadas y no repetidas, y una intranet en caso de que el servidor sea físico, en ambos casos una VPN para acceder.

7 - El director comercial de una empresa nos pide un proyecto para poder visualizar información en un tablero de BI con actualizaciones cada 15 minutos con información proveniente de distintas fuentes:¶

ERP (SQL Server) CRM (API) Catálogos de información (Excel) Google Analytics Google Ads Shopify Facebook Ads

Prepara una propuesta técnica que incluya tiempos, costos y herramientas a utilizar para entregarle al director comercial este tablero. En caso de que haya información que necesites para hacer esta propuesta y no la tengas, genera supuestos para los que sería valida la propuesta.

Herramientas y Tecnologías Propuestas ->¶

  • Integración de Datos: Microsoft SQL Server Integration Services (SSIS): Para extraer y transformar datos del ERP y catálogos de Excel. APIs personalizadas: Para conectar con CRM, Google Analytics, Google Ads, Shopify y Facebook Ads.
  • Almacenamiento de Datos: Microsoft SQL Server: Como data warehouse para consolidar todos los datos.
  • Visualización de Datos: Microsoft Power BI: Para el diseño y mantenimiento del tablero de BI, dada su integración natural con SQL Server y su capacidad para actualizaciones en tiempo real.

Proceso de Implementación¶

Extracción de Datos: 1 - Configuración de SSIS para automatizar la extracción de datos del ERP y Excel. 2 - Implementación de scripts para consumir APIs de CRM, Google Analytics, Google Ads, Shopify y Facebook Ads.

Transformación y Carga (ETL):¶

  • Desarrollo de paquetes SSIS para limpiar, transformar y cargar datos en el SQL Server.

Desarrollo del Tablero BI:¶

  • Creación de modelos de datos en Power BI.
  • Diseño de visualizaciones interactivas y paneles de control.

Costos Estimados¶

  • Licencias de Software: SQL Server: $2,000 (anual). Power BI Pro: $9.99 por usuario/mes.
  • Desarrollo y Implementación: Mano de obra: (dependiendo de la complejidad, los recursos necesarios y el presupuesto del equipo).
  • Mantenimiento: Soporte y actualizaciones: $500/mes. Supuestos
  • Acceso ininterrumpido y permisos adecuados para todas las fuentes de datos.
  • Capacidades de API para todas las plataformas externas (CRM, Google Analytics, Google Ads, Shopify, Facebook Ads).
  • Infraestructura de TI y redes capaces de soportar la transferencia y procesamiento de datos en tiempo real.
  • Toma de decisiones informada y rápida: Acceso a datos actualizados y consolidados para una mejor toma de decisiones.
  • Identificación de tendencias y oportunidades: Análisis en tiempo real para identificar rápidamente tendencias de mercado y oportunidades de negocio.
  • Optimización de la cadena de valor: Mejora continua a través de la visibilidad de toda la cadena de valor.

Proyectos Adjuntos¶

Página personal -> https://lergenesis.up.railway.app/¶

• Análisis de Abandono laboral -> https://lergenesis.up.railway.app/post/3¶

  • Objetivo: Desarrollar un modelo predictivo que identifique la probabilidad de que los empleados abandonen la empresa. El proyecto también incluirá un análisis del impacto financiero asociado con la rotación de personal, evaluando directa e indirectamente los costos involucrados.

  • Datos y Metodología: Recopilación de Datos: Utilizar datos históricos de RRHH que incluyan edad, salario, nivel de satisfacción, evaluaciones de rendimiento, duración del empleo, datos demográficos, y otros factores potencialmente relevantes.

• Forecaster de retiros monetarios (Bancos) -> https://lergenesis.up.railway.app/post/2¶

  • Predecir si un cliente retirará el 70% o más de sus ahorros en los próximos meses puede ofrecer a un banco una serie de ventajas financieras y estratégicas significativas. Aquí hay algunas de ellas:

  • Gestión de Riesgos: Una de las principales ventajas es la mejora en la gestión del riesgo. Si un banco puede anticipar grandes retiros de fondos, puede asegurarse de tener suficiente liquidez para cumplir con estas solicitudes sin perturbar sus operaciones diarias.

• Guía de precios -> https://lergenesis.up.railway.app/post/5¶

  • El propósito de este proyecto es generar precios precisos en el mercado automotriz, utilizando las series temporales de cada modelo para calcular el precio.

  • Procesa los datos para corregir cualquier valor atípico y cualquier brecha entre precios, elimina publicaciones falsas que pueden alterar los datos, genera diferentes conjuntos de información al particionar los datos en cada fuente que se utilizó en el proceso, y realiza varias agregaciones basadas en el tiempo de venta y la información geográfica para proporcionar una verdadera perspectiva sobre el comportamiento de ese vehículo en particular y su precio esperado durante los próximos tres meses.

• Algoritmo Depurador de anomalías -> https://lergenesis.up.railway.app/post/6¶

  • Este es un algoritmo hecho a mano, desarrollado para analizar datos de series temporales y corregir cualquier error, marcar cada valor atípico y luego generar un informe en formato CSV.
  • El algoritmo está calibrado para corregir valores atípicos en forma de "escalones", "picos" y la combinación de estos dos.
  • El propósito original de este proyecto era analizar bases de datos que se cargaban manualmente, algunos ejemplos se muestran para dar una idea de algunos casos de uso.

• ARIMAX edibles prices forecaster -> https://lergenesis.up.railway.app/post/10¶

  • El producto de datos de Importaciones de Alimentos de EE. UU. es un resumen estadístico publicado anualmente sobre el valor y el volumen de las importaciones de alimentos que ingresan a los Estados Unidos.

  • Los consumidores estadounidenses demandan variedad, calidad y conveniencia en los alimentos que consumen. A medida que los consumidores estadounidenses se han vuelto más ricos y étnicamente diversos, su cesta de alimentos refleja una creciente proporción de productos tropicales, especias y productos gourmet importados. Factores estacionales y climáticos impulsan las importaciones de alimentos de EE. UU. de tipos populares de frutas, verduras y productos tropicales, como el cacao y el café. Además, una creciente proporción de las importaciones estadounidenses puede atribuirse al comercio intra-industrial, donde las industrias de procesamiento agrícola con sede en los Estados Unidos llevan a cabo ciertos pasos de procesamiento en el extranjero e importan productos en diferentes niveles de procesamiento desde sus filiales en mercados extranjeros.

  • Se utilizó un pronosticador ARIMA y ARIMAX para predecir los valores de los alimentos sobre la relación de otros comestibles.

• Análisis de precios de inmuebles -> https://lergenesis.up.railway.app/post/4¶

  • En este cuaderno realizaremos un análisis del precio de algunos activos inmobiliarios ubicados en Nuevo Taipei / China. Este cuaderno introduce algunos conceptos básicos de estadística, como la covarianza o la función de vecinos más cercanos.

  • Ventajas de realizar este proyecto: Toma de decisiones informada: Ayuda a compradores y vendedores a tomar decisiones basadas en datos respecto a transacciones de propiedades, potencialmente maximizando los retornos de inversión o optimizando el momento de la compra.

  • Este proyecto aprovecha la ciencia de datos no solo para abordar la necesidad inmediata de valoraciones de propiedades precisas, sino también para equipar a los interesados con conocimientos estratégicos para la planificación a largo plazo y la toma de decisiones en el mercado inmobiliario.

In [1]: