Introducción

En este documento Markdown se presentará el código utilizado para realizar la prueba, junto con sus respectivas respuestas y elementos visuales. El contenido estará organizado por el orden de las diferentes preguntas abarcadas en la prueba. Para los elementos visuales se utilizo power bi, se realizo un informe en power bi que lo pueden encontrar publicado en el siguiente link:https://app.powerbi.com/reportEmbed?reportId=da93c6e9-52a0-4b0b-89be-580a5d25671d&autoAuth=true&ctid=79ad402c-ae6c-4756-ae09-651fc7a4e0ab .

1. Entrega de códigos SQL y Notebook de Python

Codigo de Python

# -*- coding: utf-8 -*-
"""
Created on Tue Jul  8 08:29:57 2025

@author: juliclaroj
"""

import pandas as pd
import numpy as np

#cargar bases de datos
consumo = pd.read_csv("C:/Users/juliclaroj/OneDrive - colsubsidio.com/prueba_tecnico/Data/Consumo.csv", delimiter=";", decimal=",")
empresa = pd.read_csv("C:/Users/juliclaroj/OneDrive - colsubsidio.com/prueba_tecnico/Data/Empresa.csv", encoding='latin-1', delimiter=";")
persona = pd.read_csv("C:/Users/juliclaroj/OneDrive - colsubsidio.com/prueba_tecnico/Data/Persona.csv", encoding='latin-1', delimiter=";")

#ajustes data
consumo["Afiliado"] = np.where(consumo["NumIdPersona"].isin(persona["NumIdPersona"]), "SI", "NO")
consumo["Periodo"] = pd.to_datetime(consumo["Periodo"], format="%Y%m")
consumo["Valor"] = pd.to_numeric(consumo["Valor"])
empresa["Afiliado"] = np.where(empresa["id_empresa"].isin(persona["id_empresa"]), "SI", "NO")

#union de bases
consumo_persona = consumo.merge(persona[["NumIdPersona", "id_empresa",  "Genero", "FechaNacimiento", "Edad", "Edad", "Salario", "Categoria",
                                         "Segmento_poblacional", "segmento_grupo_familiar", "cx_persona", "cy_persona", "DepartamentoPersona",
                                         "MunicipioPersona", "EstratoPersona"]], on= "NumIdPersona", how = "left")

empresa_persona = empresa.merge(persona[["NumIdPersona", "id_empresa", "Genero", "FechaNacimiento", "Edad", "Salario",
                                         "Categoria", "Segmento_poblacional", "segmento_grupo_familiar", "cx_persona", "cy_persona", 
                                         "DepartamentoPersona", "MunicipioPersona", "EstratoPersona"]], on = "id_empresa", how = "left")


#punto 1 ¿Existen temporadas de mayor venta de productos?
ventas_x_temporada = consumo.groupby("Periodo")["NumTransacciones"].sum().reset_index(name="cantidad_ventas")
print(ventas_x_temporada)

#punto 2 ¿Cuál es la participación de consumo de personas afiliadas y no afiliadas?
afiliados_si = consumo[consumo["Afiliado"] == "SI"]
afiliados_no = consumo[consumo["Afiliado"] == "NO"]
consumo_total = sum(consumo["Valor"])
consumo_afiliados = sum(afiliados_si["Valor"])
consumo_no_afiliados = sum(afiliados_no["Valor"])
participacion_afiliados = (consumo_afiliados / consumo_total) * 100
participacion_no_afiliados = (consumo_no_afiliados / consumo_total) * 100
print(participacion_afiliados)
print(participacion_no_afiliados)

#punto 3 ¿Cuál es el consumo total por unidad de negocio?
consumo_x_ues = consumo.groupby("UES")["Valor"].sum().reset_index(name="consumo_ues")
print(consumo_x_ues)

#punto 4 ¿Cuáles son las unidades y productos de mayor uso en cada categoría?
productos_x_categoria = consumo_persona.groupby(["Categoria", "Producto"])["NumTransacciones"].sum().reset_index(name="uso_producto")
productos_x_categoria = productos_x_categoria.sort_values("uso_producto", ascending = False).drop_duplicates("Categoria")
print(productos_x_categoria)

unidad_x_categoria = consumo_persona.groupby(["Categoria", "UES"])["NumTransacciones"].sum().reset_index(name="uso_unidad")
unidad_x_categoria = unidad_x_categoria.sort_values("uso_unidad", ascending = False).drop_duplicates("Categoria")
print(unidad_x_categoria)

#punto 5 Identifique los clientes (afiliados y no afiliados) con mayor frecuencia de uso y mayor valor neto de venta.

clientes_val = consumo.groupby(["NumIdPersona"])["Valor"].sum().reset_index(name="valor_clientes")
clientes_val = clientes_val.sort_values("valor_clientes", ascending = False)
print(clientes_val)

clientes_frec = consumo.groupby(["NumIdPersona"])["NumTransacciones"].sum().reset_index(name="frec_clientes")
clientes_frec = clientes_frec.sort_values("frec_clientes", ascending = False)

#punto 6 ¿Cómo ha sido el porcentaje histórico de penetración en la población afiliada de los servicios Colsubsidio?
pen_afi = afiliados_si["Afiliado"].count()
pen_no_afi = afiliados_no["Afiliado"].count()
pen_vol = consumo["Afiliado"].count()
penetracion_afiliados = (pen_afi / pen_vol) *100
penetracion_no_afiliados = (pen_no_afi / pen_vol) *100
print(penetracion_afiliados)
print(penetracion_no_afiliados)

#punto 7 ¿Cuáles son los productos más consumidos en el cada segmento poblacional?
consumo_x_poblacion_transa = consumo_persona.groupby(["Segmento_poblacional", "Producto"])["NumTransacciones"].sum().reset_index(name="consumo_poblacion")
consumo_x_poblacion_transa = consumo_x_poblacion_transa.sort_values("consumo_poblacion", ascending = False).drop_duplicates("Segmento_poblacional")
print(consumo_x_poblacion_transa)

consumo_x_poblacion_valor = consumo_persona.groupby(["Segmento_poblacional", "Producto"])["Valor"].sum().reset_index(name="consumo_poblacion")
consumo_x_poblacion_valor = consumo_x_poblacion_valor.sort_values("consumo_poblacion", ascending = False).drop_duplicates("Segmento_poblacional")
print(consumo_x_poblacion_valor)

#punto 8 ¿Cuáles son las mejores empresas en cuanto a consumo individual de sus empleados?
empresa_afiliado = empresa_persona[empresa_persona["Afiliado"] == "SI"]
empresa_afiliado["Afiliado2"] = np.where(empresa_afiliado["NumIdPersona"].isin(consumo["NumIdPersona"]), "SI", "NO")
empresa_afiliado = empresa_afiliado.merge(consumo[["NumIdPersona", "Periodo", "Valor", "NumTransacciones", "UES",
                                                         "Producto"]], on = "NumIdPersona", how = "left")
empresa_afiliado = empresa_afiliado[empresa_afiliado["Afiliado2"] == "SI"]
consumo_x_empresa = empresa_afiliado.groupby("Piramide1")["Valor"].sum().reset_index(name="consumo_empresa")
print(consumo_x_empresa)


#exportar bases de datos

consumo_persona["Valor"] = consumo_persona["Valor"].astype(int)
empresa_afiliado["Valor"] = empresa_afiliado["Valor"].astype(int)
empresa_afiliado.to_csv("C:/Users/juliclaroj/OneDrive - colsubsidio.com/prueba_tecnico/exportada/empresa_afiliado.csv", index=False)
consumo_persona.to_csv("C:/Users/juliclaroj/OneDrive - colsubsidio.com/prueba_tecnico/exportada/consumo_persona.csv", index=False)

Codigo en SQL

USE prueba_tecnico;

-- Creación de tablas base
CREATE TABLE dbo.Consumo (
    NumIdPersona INT,
    Periodo DATE,
    Valor FLOAT,
    NumTransacciones NUMERIC(18, 0),
    UES NVARCHAR(50),
    Producto NVARCHAR(50)
);

CREATE TABLE dbo.Empresa (
    id_empresa INT,
    Piramide1 NVARCHAR(50),
    Piramide2 NVARCHAR(50),
    cx_empresa DECIMAL(18, 10),
    cy_empresa DECIMAL(18, 10),
    DepartamentoEmpresa NVARCHAR(50),
    MunicipioEmpresa NVARCHAR(50),
    SectorCIIU NVARCHAR(100),
    DescripcionCIIU NVARCHAR(100)
);

CREATE TABLE dbo.Persona (
    NumIdPersona INT NOT NULL,
    id_empresa INT NOT NULL,
    Genero NVARCHAR(50) NOT NULL,
    FechaNacimiento DATE NOT NULL,
    Edad NUMERIC(18, 0) NOT NULL,
    Salario INT NOT NULL,
    Categoria NVARCHAR(50) NOT NULL,
    Segmento_poblacional NVARCHAR(50) NOT NULL,
    segmento_grupo_familiar NVARCHAR(50),
    cx_persona DECIMAL(18, 10),
    cy_persona DECIMAL(18, 10),
    DepartamentoPersona TEXT,
    MunicipioPersona TEXT,
    EstratoPersona NUMERIC(18, 0)
);

-- unión de bases
SELECT 
    c.*, 
    CASE WHEN p.NumIdPersona IS NOT NULL THEN 'SI' ELSE 'NO' END AS Afiliado
INTO #consumo_temp
FROM dbo.Consumo c
LEFT JOIN dbo.Persona p ON c.NumIdPersona = p.NumIdPersona;

SELECT 
    e.*, 
    CASE WHEN p.id_empresa IS NOT NULL THEN 'SI' ELSE 'NO' END AS Afiliado
INTO #empresa_temp
FROM dbo.Empresa e
LEFT JOIN dbo.Persona p ON e.id_empresa = p.id_empresa;

SELECT 
    c.*, 
    p.id_empresa, p.Genero, p.FechaNacimiento, p.Edad, p.Salario, p.Categoria,
    p.Segmento_poblacional, p.segmento_grupo_familiar, p.cx_persona, p.cy_persona,
    p.DepartamentoPersona, p.MunicipioPersona, p.EstratoPersona
INTO #consumo_persona
FROM #consumo_temp c
LEFT JOIN dbo.Persona p ON c.NumIdPersona = p.NumIdPersona;

SELECT 
    e.*, 
    p.NumIdPersona, p.Genero, p.FechaNacimiento, p.Edad, p.Salario, p.Categoria,
    p.Segmento_poblacional, p.segmento_grupo_familiar, p.cx_persona, p.cy_persona,
    p.DepartamentoPersona, p.MunicipioPersona, p.EstratoPersona
INTO #empresa_persona
FROM #empresa_temp e
LEFT JOIN dbo.Persona p ON e.id_empresa = p.id_empresa;

-- punto 1 ¿Existen temporadas de mayor venta de productos?
SELECT 
    Periodo, 
    SUM(NumTransacciones) AS cantidad_ventas
FROM dbo.Consumo
GROUP BY Periodo
ORDER BY Periodo;

-- punto 2 ¿Cuál es la participación de consumo de personas afiliadas y no afiliadas?
SELECT 
    SUM(CASE WHEN Afiliado = 'SI' THEN Valor ELSE 0 END) * 100.0 / SUM(Valor) AS participacion_afiliados,
    SUM(CASE WHEN Afiliado = 'NO' THEN Valor ELSE 0 END) * 100.0 / SUM(Valor) AS participacion_no_afiliados
FROM #consumo_temp;

-- punto 3 ¿Cuál es el consumo total por unidad de negocio?
SELECT 
    UES, 
    SUM(Valor) AS consumo_ues
FROM dbo.Consumo
GROUP BY UES
ORDER BY consumo_ues DESC;

-- punto 4 ¿Cuáles son las unidades y productos de mayor uso en cada categoría?
WITH RankedProductos AS (
    SELECT Categoria, Producto, SUM(NumTransacciones) AS uso_producto,
           ROW_NUMBER() OVER (PARTITION BY Categoria ORDER BY SUM(NumTransacciones) DESC) AS rn
    FROM #consumo_persona
    GROUP BY Categoria, Producto
)
SELECT Categoria, Producto, uso_producto
FROM RankedProductos
WHERE rn = 1;

-- punto 5: Identifique los clientes (afiliados y no afiliados) con mayor frecuencia de uso y mayor valor neto de venta
SELECT 
    NumIdPersona, 
    SUM(Valor) AS valor_clientes
FROM dbo.Consumo
GROUP BY NumIdPersona
ORDER BY valor_clientes DESC;

SELECT 
    NumIdPersona, 
    SUM(NumTransacciones) AS frec_clientes
FROM dbo.Consumo
GROUP BY NumIdPersona
ORDER BY frec_clientes DESC;

-- punto 6: ¿Cómo ha sido el porcentaje histórico de penetración en la población afiliada?
SELECT 
    COUNT(CASE WHEN Afiliado = 'SI' THEN 1 END) * 100.0 / COUNT(*) AS penetracion_afiliados,
    COUNT(CASE WHEN Afiliado = 'NO' THEN 1 END) * 100.0 / COUNT(*) AS penetracion_no_afiliados
FROM #consumo_temp;

-- punto 7: ¿Cuáles son los productos más consumidos en cada segmento poblacional?
WITH productos_rank_transa AS (
    SELECT Segmento_poblacional, Producto, SUM(NumTransacciones) AS consumo_poblacion,
           ROW_NUMBER() OVER (PARTITION BY Segmento_poblacional ORDER BY SUM(NumTransacciones) DESC) AS rn
    FROM #consumo_persona
    GROUP BY Segmento_poblacional, Producto
)
SELECT Segmento_poblacional, Producto, consumo_poblacion
FROM productos_rank_transa
WHERE rn = 1;

WITH productos_rank_valor AS (
    SELECT Segmento_poblacional, Producto, SUM(Valor) AS consumo_poblacion,
           ROW_NUMBER() OVER (PARTITION BY Segmento_poblacional ORDER BY SUM(Valor) DESC) AS rn
    FROM #consumo_persona
    GROUP BY Segmento_poblacional, Producto
)
SELECT Segmento_poblacional, Producto, consumo_poblacion
FROM productos_rank_valor
WHERE rn = 1;

-- punto 8: ¿Cuáles son las mejores empresas en cuanto a consumo individual de sus empleados?
SELECT 
    ep.*, 
    c.Periodo, c.Valor, c.NumTransacciones, c.UES, c.Producto
INTO #empresa_afiliado
FROM #empresa_persona ep
JOIN dbo.Consumo c ON ep.NumIdPersona = c.NumIdPersona
WHERE ep.Afiliado = 'SI';

SELECT Piramide1, SUM(Valor) AS consumo_empresa
FROM #empresa_afiliado
GROUP BY Piramide1
ORDER BY consumo_empresa DESC;

2. ¿Existen temporadas de mayor venta de productos?

Si consideramos la cantidad de transacciones como equivalente al volumen de ventas, se observan claramente temporadas con un mayor número de ventas. En los meses de septiembre y octubre de 2019 se registraron los niveles más altos de transacciones. Aunque noviembre y diciembre también mostraron volúmenes significativos, en 2020 comenzó una reduccion de estos volumenes, alcanzando en febrero su punto más bajo con una caída notable.

3. ¿Cuál es la participación de consumo de personas afiliadas y no afiliadas?

Las personas afiliadas representan el 23,51 % del valor total del consumo, en contraste con las personas no afiliadas, quienes representan el 76,49 % restante.

4. ¿Cuál es el consumo total por unidad de negocio?

El consumo total por unidad de negocio fue de $407.701.283.396, en la siguiente tabla se puede ver el consumo por cada unidad de negocio.

5. ¿Cuáles son las unidades y productos de mayor uso en cada categoría?

Si analizamos los datos desde la perspectiva de la cantidad de transacciones, observamos que en la categoría A, el producto de mayor uso es el POS y la unidad más utilizada es Salud. En la categoría B, el producto más usado son los supermercados, y la unidad con mayor participación es Mercadeo Social. Por último, en la categoría C, el producto con más uso son los supermercados y Mercadeo Social es la unidad más utilizada.

6. Identifique los clientes (afiliados y no afiliados) con mayor frecuencia de uso y mayor valor neto de venta.

En la tabla de la izquierda se observa el consumo por cliente, ordenado de forma descendente. Por otro lado, la tabla de la derecha muestra la cantidad de transacciones realizadas por cada cliente. Es importante destacar que, en el conjunto de datos entregado, los clientes están identificados mediante un ID, por esta razón, se encuentran clasificados de esa manera en ambas tablas.

7. ¿Cómo ha sido el porcentaje histórico de penetración en la población afiliada de los servicios Colsubsidio?

Si tomamos la cantidad de transacciones que han realizado los usuarios, encontramos que del total de 503.564 transacciones realizadas, el 51,15% son personas afiliadas, mientras que el 48,85% restante son personas no afiliadas.

8. ¿Cuáles son los productos más consumidos en el cada segmento poblacional?

Al analizar la cantidad de transacciones realizadas por segmento poblacional, se observa que:

En el segmento Alto, el producto más consumido son las droguerías.
En el segmento Medio, son los supermercados.
En el segmento Joven, son las POS.
En el segmento Básico, también son las POS.

Por otro lado, si consideramos el valor de consumo, se observa lo siguiente:

Para el segmento Alto, el mayor consumo corresponde a los supermercados.
Para el segmento Joven, a los productos de no libranza.
Para el segmento Básico, los supermercados.
Para el segmento Medio, igualmente a los supermercados.

9. ¿Cuáles son las mejores empresas en cuanto a consumo individual de sus empleados?

Si agrupamos las empresas por categorías, como se muestra en la tabla de la derecha, observamos que las empresas micro registran el mayor consumo por parte de sus empleados. En cambio, en la tabla de la izquierda se presenta el consumo de las empresas agrupadas según su ID, lo que permite identificar las empresas de manera individual.

Prueba tecnico de datos

Julian Claro

2025-07-09