Análisis de datos bancarios

Author

Daniel Villar

1 Introducción

  • En este proyecto se pretende dar respuesta de alto valor estratégico a través de consultas SQL a todas las preguntas de negocio que se proponen a continuación. Para ello, se han clasificado las preguntas por bloques según el tipo de pregunta a responder. La fecha del análisis se realiza en Diciembre de 2019.

2 Conexión a BBDD

Code 
# Cargar librerías necesarias
library(DBI)
library(odbc)
library(dplyr)
library(skimr)
library(dlookr)
library(tidyr)
library(huxtable)
library(scales)
library(corrplot)
library(echarts4r)

# Conexión a BBDD
con <- dbConnect(odbc::odbc(), Driver = "ODBC Driver 17 for SQL Server", 
    Server = "LAPTOP-P7645H6F", Database = "datos_banco", Trusted_Connection = "Yes")

3 EDA de las tablas de la BBDD

Code 
# Obtención de las tablas para EDA

cards_data <- dbGetQuery (con, "SELECT * FROM cards_data")
mcc_codes <- dbGetQuery (con, "SELECT * FROM mcc_codes")
transacciones_19 <- dbGetQuery (con, "SELECT * FROM transacciones WHERE date BETWEEN '20190101' AND '20190630'")
users_data <- dbGetQuery (con, "SELECT * FROM users_data")
EDA tabla cards_data
Code 
#EDA
diagnose(cards_data) %>% 
  huxtable() %>% 
  theme_blue() %>%
  set_all_borders() %>%
  set_all_border_colors("black") %>%
  set_font_size(10) %>%
  set_align("center") %>%
  set_position("center")
variablestypesmissing_countmissing_percentunique_countunique_rate
idinteger0061461
client_idinteger0020000.325
card_brandcharacter0040.000651
card_typecharacter0030.000488
card_numberinteger640061461
expirescharacter002590.0421
cvvinteger009980.162
has_chiplogical0020.000325
num_cards_issuedinteger0030.000488
credit_limitnumeric0036540.595
acct_open_datecharacter003030.0493
year_pin_last_changedinteger00190.00309
card_on_dark_webcharacter0010.000163
fecha_expiracionDate002590.0421
Code 
# Obtención los dataframes de las huxtables
df_dn <- diagnose_numeric(cards_data)
df_ou <- diagnose_outlier(cards_data)

# Unimos por la columna "variables"
combined_df <- left_join(df_dn, df_ou, by = "variables")

# Convertimos el dataframe combinado a huxtable
combined_table <- huxtable(combined_df) %>%
  theme_blue() %>%
  set_all_borders() %>%
  set_all_border_colors("black") %>%
  set_font_size(8) %>%
  set_align("center") %>%
  set_position("center")

# Visualizamos la tabla combinada
combined_table %>% 
  select(-outliers_cnt,-outliers_ratio, -outliers_mean, -with_mean)
variablesminQ1meanmedianQ3maxzerominusoutlierwithout_mean
id01.54e+033.07e+033.07e+034.61e+036.14e+031003.07e+03
client_id04929959921.49e+032e+03400995
cvv0257506516756999400506
num_cards_issued111.51230001.5
credit_limit07.04e+031.43e+041.26e+041.92e+041.51e+053102341.28e+04
year_pin_last_changed2e+032.01e+032.01e+032.01e+032.02e+032.02e+030002.01e+03
EDA tabla mcc_codes
Code 
#EDA
diagnose(mcc_codes) %>% 
  huxtable() %>% 
  theme_blue() %>%
  set_all_borders() %>%
  set_all_border_colors("black") %>%
  set_font_size(10) %>%
  set_align("center") %>%
  set_position("center")
variablestypesmissing_countmissing_percentunique_countunique_rate
mccinteger001091
valorcharacter001080.991

Al no tener un ratio único en la columna “valor”, ejecutamos una consulta en busca de posibles duplicados.

Code 

SELECT
valor,
COUNT (*) AS conteo
FROM mcc_codes
GROUP BY
valor
HAVING
COUNT (*) > 1
valorconteo
Passenger Railways2

Una vez localizado el duplicado, buscamos sus códigos mcc

Code 

SELECT
mcc,
valor
FROM mcc_codes
WHERE
valor = 'Passenger Railways'
mccvalor
3722Passenger Railways
4112Passenger Railways
EDA tabla transacciones (primer semestre 2019)
Code 
#EDA
diagnose(transacciones_19) %>% 
  huxtable() %>% 
  theme_blue() %>%
  set_all_borders() %>%
  set_all_border_colors("black") %>%
  set_font_size(10) %>%
  set_align("center") %>%
  set_position("center")
variablestypesmissing_countmissing_percentunique_countunique_rate
idinteger006861241
datePOSIXct002089710.305
client_idinteger0012060.00176
card_idinteger0034120.00497
amountnumeric00303450.0442
use_chipcharacter0034.37e-06
merchant_idinteger00223320.0325
merchant_citycharacter0061620.00898
merchant_statecharacter001050.000153
zipcharacter00117400.0171
mccinteger001090.000159
errorscharacter00202.91e-05
Code 
# Obtención los dataframes de las huxtables
df_dn <- diagnose_numeric(transacciones_19)
df_ou <- diagnose_outlier(transacciones_19)

# Unimos por la columna "variables"
combined_df <- left_join(df_dn, df_ou, by = "variables")

# Convertimos el dataframe combinado a huxtable
combined_table <- huxtable(combined_df) %>%
  theme_blue() %>%
  set_all_borders() %>%
  set_all_border_colors("black") %>%
  set_font_size(8) %>%
  set_align("center") %>%
  set_position("center")

# Visualizamos la tabla combinada
combined_table %>% 
  select(-outliers_cnt,-outliers_ratio, -outliers_mean, -with_mean)
variablesminQ1meanmedianQ3maxzerominusoutlierwithout_mean
id2.23e+072.25e+072.28e+072.28e+072.3e+072.32e+070002.28e+07
client_id05251.03e+031.07e+031.53e+032e+03644001.03e+03
card_id02.4e+033.48e+033.6e+034.89e+036.14e+03179003.48e+03
amount-5008.8842.928.963.36.61e+03514335805497836.4
merchant_id22.59e+044.77e+044.58e+046.8e+041e+050004.77e+04
mcc1.71e+035.3e+035.57e+035.5e+035.81e+039.4e+03001121815.45e+03
EDA users_data
Code 
#EDA
diagnose(users_data) %>% 
  huxtable() %>% 
  theme_blue() %>%
  set_all_borders() %>%
  set_all_border_colors("black") %>%
  set_font_size(10) %>%
  set_align("center") %>%
  set_position("center")
variablestypesmissing_countmissing_percentunique_countunique_rate
idinteger0020001
current_ageinteger00800.04
retirement_ageinteger00290.0145
birth_yearinteger00800.04
birth_monthinteger00120.006
gendercharacter0020.001
addresscharacter0019991
latitudenumeric009890.494
longitudenumeric0012240.612
per_capita_incomenumeric0017540.877
yearly_incomenumeric0019480.974
total_debtnumeric0018800.94
credit_scoreinteger003210.16
num_credit_cardsinteger0090.0045
Code 
# Obtención los dataframes de las huxtables
df_dn <- diagnose_numeric(users_data)
df_ou <- diagnose_outlier(users_data)

# Unimos por la columna "variables"
combined_df <- left_join(df_dn, df_ou, by = "variables")

# Convertimos el dataframe combinado a huxtable
combined_table <- huxtable(combined_df) %>%
  theme_blue() %>%
  set_all_borders() %>%
  set_all_border_colors("black") %>%
  set_font_size(8) %>%
  set_align("center") %>%
  set_position("center")

# Visualizamos la tabla combinada
combined_table %>% 
  select(-outliers_cnt,-outliers_ratio, -outliers_mean, -with_mean)
variablesminQ1meanmedianQ3maxzerominusoutlierwithout_mean
id05001e+031e+031.5e+032e+031001e+03
current_age183045.4445810100145.4
retirement_age506566.26668790021066.6
birth_year1.92e+031.96e+031.97e+031.97e+031.99e+032e+030011.97e+03
birth_month136.44710120006.44
latitude2133.27e+033.43e+033.75e+034.09e+036.11e+03001883.74e+03
longitude-1.59e+04-9.66e+03-8.21e+03-8.43e+03-7.62e+03-70302000236-9.09e+03
per_capita_income01.68e+042.31e+042.06e+042.63e+041.63e+051501232.14e+04
yearly_income13.28e+044.57e+044.07e+045.27e+043.07e+05001184.24e+04
total_debt02.4e+046.37e+045.83e+048.91e+045.16e+051020515.9e+04
credit_score4806817107127538500067716
num_credit_cards123.0734900203.02

4 Preguntas de negocio

4.1 Segmentación Demográfica y Socioeconómica:

Estas preguntas buscan segmentar a los clientes por sus características demográficas o socioeconómicas, lo que es esencial para conocer el comportamiento y las necesidades de los distintos grupos.

4.1.1 ANALISIS GENERAL: Consumo en compras, número de transacciones e importes de compras promedio por género y edad

Code 

WITH T1 AS
(
SELECT
  gender,
  CASE 
    WHEN u.current_age >= 18 AND u.current_age <= 25 THEN 'jovenes'
    WHEN u.current_age > 25 AND u.current_age <= 45 THEN 'adultos'
    WHEN u.current_age > 45 AND u.current_age <= 65 THEN 'mayores'
    ELSE 'seniors' END AS edad,
  COUNT (DISTINCT t.id) AS transacciones,
  AVG (t.amount) AS avg_gasto,
  SUM (t.amount) AS total_gasto
FROM
  users_data u
INNER JOIN
  transacciones t
ON
  u.id = t.client_id
GROUP BY 
  u.gender,
  CASE 
    WHEN u.current_age >= 18 AND u.current_age <= 25 THEN 'jovenes'
    WHEN u.current_age > 25 AND u.current_age <= 45 THEN 'adultos'
    WHEN u.current_age > 45 AND u.current_age <= 65 THEN 'mayores'
    ELSE 'seniors' END
)
SELECT
  DENSE_RANK () OVER (ORDER BY total_gasto DESC) AS rank_gasto,
  DENSE_RANK () OVER (ORDER BY transacciones DESC) AS rank_transacc,
  DENSE_RANK () OVER (ORDER BY avg_gasto DESC) AS rank_avg_gasto,
  gender,
  edad,
  total_gasto,
  transacciones,
  avg_gasto
FROM
  T1
ORDER BY
  rank_gasto ASC
rank_gastorank_transaccrank_avg_gastogenderedadtotal_gastotransaccionesavg_gasto
112Femalemayores133.184.6813.082.02843.2
225Malemayores129.362.0433.010.02343
334Maleadultos 91.245.1992.118.98443.1
446Femaleadultos 89.233.2192.083.24942.8
557Femaleseniors 69.029.6331.613.30242.8
668Maleseniors 57.597.1411.353.54442.6
771Femalejovenes 1.862.013 37.33749.9
883Malejovenes 321.595 7.44843.2
  • CONCLUSIONES: el grupo de personas que ha gastado más y que además es el que más transacciones ha realizado es el de mujeres mayores (entre los 45 y 65 años), pero el grupo de personas que más gasta por promedio de transacción es el de mujeres jovenes (entre los 18 a 25 años.)

4.1.2 ¿Cuál es el límite de crédito promedio de los clientes según su género?

Code 

SELECT
  u.gender,
  AVG (c.credit_limit) AS lim_cred_prom
FROM
  cards_data c
INNER JOIN
  users_data u
ON
  c.client_id = u.id
GROUP BY
  u.gender
genderlim_cred_prom
Male14.305,02
Female14.388,18

4.1.3 ¿Cuál es el promedio de edad de los clientes con un límite de crédito superior a $10,000?

Code 

SELECT
  AVG (u.current_age) AS prom_edad
FROM
  users_data u
INNER JOIN
  cards_data c
ON
  u.id = c.client_id
WHERE
  c.credit_limit > 10000
prom_edad
49

4.1.4 ¿Cuántos clientes con ingresos superiores a $60,000 han realizado más de 20 transacciones en 2019?

Code 
WITH T1 AS
(
SELECT
  t.client_id,
  COUNT (DISTINCT t.id) AS nro_transacciones
FROM
  transacciones t
INNER JOIN
  cards_data c
ON 
  t.client_id = c.client_id
INNER JOIN
  users_data u
ON 
  u.id = t.client_id
WHERE
  u.yearly_income > 60000 AND
  YEAR (t.date) = 2019
GROUP BY
  t.client_id
HAVING
  COUNT (DISTINCT t.id) > 20
)
SELECT
  COUNT (1) AS nro_clientes
FROM T1
nro_clientes
197

4.1.5 ¿Cuántos clientes con un puntaje de crédito superior a 750 tienen más de 5 tarjetas activas?

Code 
WITH T1 AS
(
SELECT
  c.client_id,
  SUM (c.num_cards_issued) AS nro_tarjetas
FROM
  cards_data c
INNER JOIN
  users_data u
ON
  c.client_id = u.id
WHERE
  u.credit_score > 750 AND
  -- para averiguar las tarjetas activas, se filtra desde la fecha actual hasta la última fecha de expiración
  c.fecha_expiracion BETWEEN '20191201' AND '20241231'
GROUP BY
  c.client_id
HAVING
  SUM (c.num_cards_issued) > 5
)
SELECT
COUNT (1) AS nro_clientes
FROM T1
nro_clientes
122

4.1.6 ¿Cuántos clientes con una edad superior a 50 años tienen más de tres tarjetas de crédito activas?

Code 

WITH T1 AS
(
SELECT
  c.client_id,
  SUM (c.num_cards_issued) AS nro_tarjetas
FROM
  users_data u
INNER JOIN
  cards_data c
ON
  u.id = c.client_id
WHERE
  u.current_age > 50 AND
  -- para averiguar las tarjetas activas, se filtra desde la fecha actual hasta la última fecha de expiración
  c.fecha_expiracion BETWEEN '20191201' AND '20241231'  
GROUP BY
  c.client_id
HAVING  
  SUM (c.num_cards_issued) > 3
)
SELECT
COUNT (1) AS nro_clientes
FROM T1
nro_clientes
487

4.1.7 ¿Cuántos clientes con ingresos inferiores a $30,000 tienen más de 3 tarjetas de crédito activas?

Code 

WITH T1 AS
(
SELECT
  c.client_id,
  SUM (c.num_cards_issued) AS nro_tarjetas
FROM
  users_data u
INNER JOIN
  cards_data c
ON
  u.id = c.client_id
WHERE
  u.yearly_income < 30000 AND
  -- para averiguar las tarjetas activas, se filtra desde la fecha actual hasta la última fecha de expiración
  c.fecha_expiracion BETWEEN '20191201' AND '20241231' 
GROUP BY
  c.client_id
HAVING
  SUM (c.num_cards_issued) > 3
)
SELECT
COUNT (1) AS nro_clientes
FROM T1
nro_clientes
176

4.1.8 ¿Cuál es la relación entre la edad y el puntaje de crédito de los clientes?

Code 

SELECT
current_age,
credit_score
FROM
users_data
Code 
correlacion <- cor(p7$current_age, p7$credit_score) # obtención de la correlación lineal de dos variables
COEFICIENTE DE CORRELACION
-0.00547

CONCLUSION: NO existe apenas correlación entre las variables (la correlación se mide entre 1 y -1, donde 0 es que no existe ningún tipo de correlación entre variables).

4.1.9 ¿Cuántos clientes con un puntaje de crédito superior a 700 han realizado compras por más de $5,000 en comercios con el código MCC 5732 (electrónica)?

Code 

WITH T1 AS
(
SELECT
  u.id,
  SUM (t.amount) AS total_compras
FROM
  users_data u
INNER JOIN
  transacciones t
ON 
  u.id = t.client_id
WHERE
  t.mcc = 5732
GROUP BY
  u.id
HAVING
  SUM (t.amount) > 5000
)
SELECT
  COUNT (1) AS nro_clientes
FROM T1
nro_clientes
32

4.2 Análisis de Tarjetas de Crédito y Débito:

Estas preguntas buscan entender el comportamiento de los clientes en relación con las tarjetas que poseen, sus límites de crédito y su comportamiento de compra.

4.2.1 ANALISIS GENERAL: promedio general de días transcurridos entre transacciones en los codigos mcc que tengan menos transacciones

Code 

WITH T1 AS
(
SELECT 
  t.client_id,
  t.mcc,
  m.valor,
  CAST (date AS date) AS fecha,
  COUNT (1) OVER (PARTITION BY t.client_id) AS nro_transacc
FROM
  mcc_codes m
LEFT JOIN
  transacciones t
ON 
  m.mcc = t.mcc
WHERE
  m.mcc IN (3075, 3144, 5733) -- los tres códigos con menos transacciones
), T2 AS
(
SELECT
  client_id,
  mcc,
  valor,
  fecha,
  LAG (fecha, 1) OVER (PARTITION BY client_id, mcc ORDER BY fecha) AS fecha_ant
FROM 
  T1
WHERE
  nro_transacc > 3 -- filtramos por aquellos clientes que tienen más de 3 transacciones
), T3 AS
(
SELECT
  *,
  COALESCE (fecha_ant, fecha) AS fecha_ant_v2, -- sustituimos los valores nulos 
  DATEDIFF (DAY, COALESCE (fecha_ant, fecha), fecha) AS dias_transc
FROM
  T2
WHERE
  DATEDIFF (DAY, COALESCE (fecha_ant, fecha), fecha) > 0 -- eliminamos aquellas transacc. de clientes que solo tenga 1 transacc.
) -- RESULTADO
SELECT
  mcc,
  valor,
  AVG (dias_transc) AS avg_dias -- agrupamos los mcc para obtener el promedio de días transcurridos de los mcc con menos transacc.
FROM
  T3
GROUP BY
  mcc,
  valor
mccvaloravg_dias
5733Music Stores - Musical Instruments646
3075Bolt, Nut, Screw, Rivet Manufacturing772
3144Floor Covering Stores989

4.2.2 ¿Cuál es la relación entre el número de tarjetas activas y la deuda total de los clientes?

Code 

SELECT
  c.num_cards_issued,
  u.total_debt
FROM
  cards_data c
INNER JOIN
  users_data u
ON
  c.client_id = u.id
WHERE
-- para averiguar las tarjetas activas, se filtra desde la fecha actual hasta la última fecha de expiración
  c.fecha_expiracion BETWEEN '20191201' AND '20241231'
Code 
correlacion_p9 <- cor(p9$num_cards_issued, p9$total_debt) # obtención de la correlación lineal de dos variables
COEFICIENTE DE CORRELACION
0.025

CONCLUSION: NO existe apenas correlación entre las variables (la correlación se mide entre 1 y -1, donde 0 es que no existe ningún tipo de correlación entre variables).

4.2.3 ¿Cuál es el límite de crédito promedio de los clientes que tienen más de 2 tarjetas activas?

Code 

SELECT
AVG (credit_limit) AS lim_cred_promedio
FROM
cards_data
WHERE
num_cards_issued > 2 AND
fecha_expiracion BETWEEN '20191201' AND '20241231'
lim_cred_promedio
10.285,43

4.2.4 ¿Cuántos clientes con más de 5 tarjetas activas tienen un límite de crédito superior a $10,000?

Code 

WITH T1 AS
(
SELECT
  client_id,
  SUM (num_cards_issued) AS total_tarjetas
FROM
  cards_data
WHERE
  credit_limit > 10000 AND
  fecha_expiracion BETWEEN '20191201' AND '20241231' 
GROUP BY
  client_id
HAVING
  SUM (num_cards_issued) > 5
)
SELECT
COUNT (1) AS nro_clientes
FROM T1
nro_clientes
139

4.2.5 ¿Cuántos clientes con tarjeta de crédito tienen una deuda superior a $20,000?

Code 

SELECT
  COUNT (DISTINCT u.id) AS nro_clientes
FROM
  users_data u
INNER JOIN
  cards_data c
ON
  u.id = c.client_id
WHERE
  c.card_type = 'credit' AND
  u.total_debt > 20000
nro_clientes
1.009

4.2.6 ¿Cuántos clientes con tarjeta de crédito tienen un límite de crédito inferior a $1,000?

Code 

SELECT
  COUNT (DISTINCT client_id) AS nro_clientes
FROM
  cards_data
WHERE
  card_type = 'credit' AND
  credit_limit < 1000
nro_clientes
42

4.2.7 ¿Cuál es el límite de crédito promedio de los clientes que han realizado más de 500 transacciones en 2019?

Code 

WITH T1 AS
(
SELECT
  t.client_id,
  c.credit_limit,
  COUNT (t.id) AS nro_transac 
FROM
  transacciones t
INNER JOIN
  cards_data c
ON
  t.client_id = c.client_id
WHERE
  YEAR (t.date) = 2019
GROUP BY
  t.client_id,
  c.credit_limit
HAVING
  COUNT (t.id) > 500
)
SELECT
  AVG (credit_limit) AS lim_cred_prom
FROM T1
lim_cred_prom
14.942,76

4.2.8 ¿Cuántos clientes con tarjeta de crédito han realizado compras superiores a $250 en comercios con el código MCC 5812 (restaurantes)?

Code 

SELECT
  COUNT (DISTINCT c.client_id) AS nro_clientes
FROM
  cards_data c
INNER JOIN
  transacciones t
ON
  c.client_id = t.client_id
WHERE
  c.card_type = 'credit' AND
  t.amount > 250 AND
  t.mcc = 5812
nro_clientes
3

4.3 Análisis de Transacciones y Comportamiento de Compra:

Aquí, las preguntas están enfocadas en el volumen, tipo y frecuencia de transacciones, lo que puede ayudar a identificar tendencias de gasto.

4.3.1 ANALISIS GENERAL: Análisis de frecuencia de compra

Code 


WITH general AS
(
SELECT
  DATENAME (WEEKDAY, date) AS dia,
  COUNT (1) AS transacc_dia
FROM
  transacciones
GROUP BY
  DATENAME (WEEKDAY, date)
), 
  acum AS
(
SELECT
  *,
  SUM (transacc_dia) OVER () AS total_transacc,
  SUM (transacc_dia) OVER (ORDER BY transacc_dia DESC) AS acum_transacc
FROM
  general
)
SELECT
  dia,
  CAST (CAST (acum_transacc AS float) / total_transacc * 100 AS decimal (8,2)) AS pct_transacc
FROM
  acum
Code 


WITH general AS
(
SELECT
  DATENAME (MONTH, date) AS mes,
  COUNT (1) AS transacc_mes
FROM
  transacciones
GROUP BY
  DATENAME (MONTH, date)
), 
  acum AS
(
SELECT
  *,
  SUM (transacc_mes) OVER () AS total_transacc,
  SUM (transacc_mes) OVER (ORDER BY transacc_mes DESC) AS acum_transacc
FROM
  general
)
SELECT
  mes,
  CAST (CAST (acum_transacc AS float) / total_transacc * 100 AS decimal (8,2)) AS pct_transacc
FROM
  acum
Frecuencia de compra por días
diapct_transacc
Thursday14.4
Saturday28.7
Sunday43
Tuesday57.2
Monday71.5
Wednesday85.8
Friday100
Frecuencia de compra por meses
mespct_transacc
August8.69
July17.4
October26
May34.6
March43.2
January51.8
June60.2
September68.6
April76.9
December84.7
February92.5
November100

CONCLUSION: Según el porcentaje de Pareto aplicado; más del 80 % de las transacciones se realizan de Sábados a Jueves y entre los meses comprendidos que van de Marzo a Octubre y entre Diciembre y Enero

4.3.2 ¿Cuántas transacciones fueron realizadas por clientes con tarjeta de crédito en el año 2017?

Code 

SELECT
  COUNT (DISTINCT t.id) AS nro_transacciones
FROM
  transacciones t
INNER JOIN
  cards_data c
ON
  t.client_id = c.client_id
WHERE
  c.card_type = 'credit' AND
  YEAR (t.date) = 2017
nro_transacciones
1.027.858

4.3.3 ¿Cuál es el importe total de las transacciones realizadas por clientes con tarjeta de crédito en 2017?

Code 

SELECT
  SUM (t.amount) AS importe_total
FROM
  transacciones t
INNER JOIN
  cards_data c
ON
  t.client_id = c.client_id
WHERE
  c.card_type = 'credit' AND
  YEAR (t.date) = 2017
importe_total
74.193.501

4.3.4 ¿Cuántos clientes han realizado compras por más de $2,000 en comercios con el código MCC 5732 (electrónica)?

Code 

SELECT
  COUNT (DISTINCT client_id) AS nro_clientes
FROM
  transacciones
WHERE
  amount > 2000 AND
  mcc = 5732
nro_clientes
7

4.3.5 ¿Cuántos clientes con más de 2 tarjetas activas realizaron transacciones superiores a $1,000 durante 2016?

Code 

SELECT
  COUNT (DISTINCT t.client_id) AS nro_clientes
FROM
  transacciones t
INNER JOIN
  cards_data c
ON
  t.client_id = c.client_id
WHERE
  c.num_cards_issued > 2 AND
  c.fecha_expiracion BETWEEN '20191201' AND '20241231' AND
  t.amount > 1000 AND
  YEAR (t.date) = 2016
nro_clientes
8

4.3.6 ¿Cuántos clientes han realizado más de 10 transacciones en comercios con el código MCC 5732 (electrónica) entre 2015 y 2019?

Code 

WITH T1 AS
(
SELECT
  t.client_id,
  COUNT (DISTINCT t.id) AS nro_transacciones
FROM
  transacciones t
INNER JOIN
  cards_data c
ON
  t.client_id = c.client_id
WHERE
  t.mcc = 5732 AND
  t.date BETWEEN '20150101' AND '20191231'
GROUP BY
  t.client_id
HAVING
  COUNT (DISTINCT t.id) > 10
)
SELECT 
  COUNT (1) AS nro_clientes
FROM T1
nro_clientes
61

4.3.7 ¿Cuál es el importe total de las compras realizadas en comercios con el código MCC 5732 (electrónica)?

Code 

SELECT
  SUM (amount) AS importe_total
FROM
  transacciones
WHERE
  mcc = 5732
importe_total
1.063.471,09

4.3.8 ¿Cuántos clientes han realizado más de 40 transacciones en comercios con el código MCC 5732 (electrónica) entre 2010 y 2019?

Code 

WITH T1 AS
(
SELECT
  client_id,
  COUNT (DISTINCT id) AS nro_transacciones
FROM
  transacciones
WHERE
  mcc = 5732 AND
  date BETWEEN '20100101' AND '20191231'
GROUP BY
  client_id
HAVING
  COUNT (DISTINCT id) > 40
)
SELECT
  COUNT (1) AS nro_clientes
FROM T1
nro_clientes
3

4.4 Análisis de Deuda y Crédito:

Este tipo de preguntas ayuda a evaluar el riesgo crediticio de los clientes y la relación entre su deuda y las transacciones realizadas.

4.4.1 ¿Cuál es la relación entre la deuda total de los clientes y la cantidad de transacciones realizadas en 2019?

Code 

SELECT
  u.total_debt,
  COUNT (DISTINCT t.id) AS nro_transacciones
FROM
  transacciones t
INNER JOIN
  users_data u
ON
  t.client_id = u.id
WHERE
  YEAR (t.date) = 2019
GROUP BY
  u.total_debt
Code 
correlacion_p23 <- cor(p23$total_debt, p23$nro_transacciones) # obtención de la correlación lineal de dos variables
COEFICIENTE DE CORRELACION
-0.0305

CONCLUSION: NO existe apenas correlación negativa entre las variables (la correlación se mide entre 1 y -1, donde 0 es que no existe ningún tipo de correlación entre variables).

4.4.2 ¿Cuántos clientes con una deuda superior a $15,000 han realizado compras en comercios con el código MCC 5732 (electrónica)?

Code 

SELECT
  COUNT (DISTINCT t.client_id) AS nro_clientes
FROM
  transacciones t
INNER JOIN
  users_data u
ON
  t.client_id = u.id
WHERE
  u.total_debt > 15000 AND
  t.mcc = 5732
nro_clientes
845

4.4.3 ¿Cuál es la deuda promedio de los clientes que realizaron más de 10 transacciones en comercios con el código MCC 5732 (electrónica)?

Code 

WITH T1 AS
(
SELECT
  t.client_id,
  u.total_debt,
  COUNT (DISTINCT t.id) AS nro_transacciones
FROM
  transacciones t
INNER JOIN
  users_data u
ON
  t.client_id = u.id
WHERE
  t.mcc = 5732
GROUP BY
  t.client_id,
  u.total_debt
HAVING
  COUNT (DISTINCT t.id) > 10
)
SELECT
  AVG (total_debt) AS deuda_promedio
FROM T1
deuda_promedio
51.832,13

4.4.4 ¿Cuántos clientes con una deuda superior a $15,000 y un puntaje de crédito inferior a 600 han realizado transacciones?

Code 

SELECT
  COUNT (DISTINCT t.client_id) AS nro_clientes
FROM
  transacciones t
INNER JOIN
  users_data u
ON
  t.client_id = u.id
WHERE
  u.total_debt > 15000 AND
  u.credit_score < 600
nro_clientes
60

4.4.5 ¿Cuál es la deuda promedio de los clientes que tienen más de 1 tarjeta activa?

Code 

SELECT
  AVG (u.total_debt) AS deuda_promedio
FROM
  cards_data c
INNER JOIN
  users_data u
ON
  c.client_id = u.id
WHERE
  c.num_cards_issued > 1 AND
  c.fecha_expiracion BETWEEN '20191201' AND '20241231'
deuda_promedio
59.608,15

4.4.6 ¿Cuántos clientes con tarjeta de crédito han realizado compras superiores a $1,500 en comercios con el código MCC 5732 (electrónica)?

Code 

SELECT
  COUNT (DISTINCT t.client_id) AS nro_clientes 
FROM
  transacciones t
INNER JOIN
  cards_data c
ON
  c.client_id = t.client_id
WHERE
  c.card_type = 'credit' AND
  t.amount > 1500 AND
  t.mcc = 5732
nro_clientes
6

4.5 Análisis de Segmentación por Comercios (MCC):

Estas preguntas permiten entender cómo los clientes interactúan con diferentes tipos de comercios, lo cual es útil para estrategias de marketing y ventas.

4.5.1 ¿Cuántos clientes han realizado compras en comercios con códigos MCC 5732 (electrónica) y 5812 (restaurantes) durante 2018?

Code 

SELECT
  mcc,
  COUNT (DISTINCT client_id) AS nro_clientes
FROM
  transacciones
WHERE
  mcc IN (5732, 5812) AND
  YEAR (date) = 2018
GROUP BY
  mcc
mccnro_clientes
5732 441
58121.153

4.5.2 ¿Cuántos clientes han realizado compras superiores a $1,000 en 2019 en comercios con el código MCC 5732 (electrónica)?

Code 

SELECT
  COUNT (DISTINCT client_id) AS nro_clientes
FROM
  transacciones
WHERE
  amount > 1000 AND
  YEAR (date) = 2019 AND
  mcc = 5732
nro_clientes
1

4.5.3 ¿Cuántos clientes con tarjeta de crédito han realizado transacciones por más de $500 en comercios con el código MCC 5732 (electrónica)?

Code 

SELECT
  COUNT (DISTINCT c.client_id) AS nro_clientes
FROM
  transacciones t
INNER JOIN
  cards_data c
ON 
  t.client_id = c.client_id
WHERE
  c.card_type = 'credit' AND
  t.amount > 500 AND
  t.mcc = 5732
nro_clientes
56

4.5.4 ¿Cuántos clientes con tarjeta de débito han realizado compras en comercios con el código MCC 5732 (electrónica) en 2018?

Code 

SELECT
  COUNT (DISTINCT c.client_id) AS nro_clientes
FROM
  transacciones t
INNER JOIN
  cards_data c
ON 
  t.client_id = c.client_id
WHERE
  c.card_type = 'debit' AND
  t.mcc = 5732 AND
  YEAR (t.date) = 2018
nro_clientes
409

4.5.5 ¿Cuál es el importe total de transacciones realizadas en comercios con el código MCC 5411 (supermercados) durante 2017?

Code 

SELECT
  SUM (amount) AS importe_total
FROM
  transacciones
WHERE
  mcc = 5411 AND
  YEAR (date) = 2017
importe_total
4.271.476,38

4.5.6 ¿Cuántos clientes con tarjeta de crédito han realizado compras en comercios con el código MCC 5411 (supermercados) en 2017?

Code 

SELECT
  COUNT (DISTINCT t.client_id) AS nro_clientes
FROM
  transacciones t
INNER JOIN
  cards_data c
ON
  t.client_id = c.client_id
WHERE
  c.card_type = 'credit' AND
  t.mcc = 5411 AND
  YEAR (t.date) = 2017
nro_clientes
890

4.5.7 ¿Cuántos clientes han realizado compras superiores a $2,000 en comercios con el código MCC 5814 (bares)?

Code 

SELECT
  COUNT (DISTINCT client_id) AS nro_clientes
FROM
  transacciones
WHERE
  amount > 2000 AND
  mcc = 5814
nro_clientes
0

4.6 Análisis de Frecuencia y Volumen de Transacciones:

Estas preguntas son útiles para identificar patrones y frecuencias de compra que pueden ser útiles para segmentar a los clientes en función de su lealtad y comportamiento.

4.6.1 ¿Cuántos clientes han realizado más de 10 transacciones en comercios con el código MCC 5732 (electrónica) entre 2017 y 2019?

Code 

WITH T1 AS
(
SELECT
  client_id,
  COUNT (DISTINCT id) AS nro_transacciones
FROM
  transacciones
WHERE
  mcc = 5732 AND
  date BETWEEN '20170101' AND '20191231'
GROUP BY
  client_id
HAVING
  COUNT (DISTINCT id) > 10
)
SELECT
  COUNT (1) AS nro_clientes
FROM T1
nro_clientes
12

4.6.2 ¿Cuántos clientes con tarjeta de crédito han realizado más de 50 transacciones en comercios con el código MCC 5411 (supermercados) durante 2019?

Code 

WITH T1 AS
(
SELECT
  t.client_id,
  COUNT (DISTINCT t.id) AS nro_transacciones
FROM
  transacciones t
INNER JOIN
  cards_data c
ON
  t.client_id = c.client_id
WHERE
  c.card_type = 'credit' AND
  t.mcc = 5411 AND
  YEAR (t.date) = 2019
GROUP BY
  t.client_id
HAVING
  COUNT (DISTINCT t.id) > 50
)
SELECT
  COUNT (1) AS nro_clientes
FROM T1
nro_clientes
669

4.6.3 ¿Cuántos clientes han realizado más de 5 transacciones en comercios con el código MCC 5411 (supermercados) durante 2019?

Code 

WITH T1 AS
(
SELECT
  client_id,
  COUNT (DISTINCT id) AS nro_transacciones
FROM
  transacciones 
WHERE
  mcc = 5411 AND
  YEAR (date) = 2019
GROUP BY
  client_id
HAVING
  COUNT (DISTINCT id) > 5
)
SELECT
  COUNT (1) AS nro_clientes
FROM T1
nro_clientes
1.206

4.6.4 ¿Cuántos clientes han realizado más de 10 transacciones en comercios con el código MCC 5732 (electrónica) entre 2010 y 2019?

Code 

WITH T1 AS
(
SELECT
  client_id,
  COUNT (DISTINCT id) AS nro_transacciones
FROM
  transacciones 
WHERE
  mcc = 5732 AND
  date BETWEEN '20100101' AND '20191231'
GROUP BY
  client_id
HAVING
  COUNT (DISTINCT id) > 10
)
SELECT
  COUNT (1) AS nro_clientes
FROM T1
nro_clientes
179

4.6.5 ¿Cuántos clientes han realizado más de 15 transacciones en comercios con el código MCC 5732 (electrónica)?

Code 

WITH T1 AS
(
SELECT
  client_id,
  COUNT (DISTINCT id) AS nro_transacciones
FROM
  transacciones 
WHERE
  mcc = 5732 
GROUP BY
  client_id
HAVING
  COUNT (DISTINCT id) > 15
)
SELECT
  COUNT (1) AS nro_clientes
FROM T1
nro_clientes
96

4.6.6 ¿Cuántos clientes han realizado transacciones superiores a $3,000 en comercios con el código MCC 5732 (electrónica)?

Code 

SELECT
  COUNT (DISTINCT client_id) AS nro_clientes
FROM
  transacciones 
WHERE
  amount > 3000 AND
  mcc = 5732
nro_clientes
6

4.6.7 ¿Cuántos clientes han realizado transacciones superiores a $150 en comercios con el código MCC 5814 (bares) durante 2018?

Code 

SELECT
  COUNT (DISTINCT client_id) AS nro_clientes
FROM
  transacciones 
WHERE
  amount > 150 AND
  mcc = 5814 AND
  YEAR (date) = 2018
nro_clientes
15

4.7 Análisis de Comportamiento de Género:

Las preguntas relacionadas con el género pueden ser útiles para entender la diferencia de comportamiento de compra entre hombres y mujeres.

4.7.1 ¿Cuál es la diferencia entre el límite de crédito promedio de clientes masculinos y femeninos con tarjeta de crédito?

Code 

WITH T1 AS
(
SELECT
  AVG (CASE WHEN u.gender = 'male' THEN c.credit_limit ELSE 0 END) AS avg_male,
  AVG (CASE WHEN u.gender = 'female' THEN c.credit_limit ELSE 0 END) AS avg_female
FROM
  cards_data c
INNER JOIN
  users_data u
ON
  c.client_id = u.id
WHERE
  c.card_type = 'credit'
)
SELECT
*,
avg_male - avg_female AS diferencia
FROM T1
avg_maleavg_femalediferencia
5.524,745.649,64-124,89

EL PROMEDIO ES MAYOR EN EL GENERO FEMENINO, CON UNA DIFERENCIA DE 124,89 $ A SU FAVOR

4.7.2 ¿Cuántos clientes femeninos con un puntaje de crédito superior a 700 han realizado más de 30 transacciones en comercios con el código MCC 5732 (electrónica)?

Code 

WITH T1 AS
(
SELECT
  u.id,
  COUNT (DISTINCT t.id) AS nro_transacciones
FROM
  users_data u
INNER JOIN
  transacciones t
ON 
  u.id = t.client_id
WHERE
  u.gender = 'female' AND
  u.credit_score > 700 AND
  t.mcc = 5732
GROUP BY
  u.id
HAVING
  COUNT (DISTINCT t.id) > 30
)
SELECT
COUNT (1) AS nro_clientes
FROM T1
nro_clientes
3

4.7.3 ¿Cuántos clientes masculinos han realizado transacciones superiores a $200 en 2019 en comercios con el código MCC 5814 (bares)?

Code 

SELECT
  COUNT (DISTINCT u.id) AS nro_clientes
FROM
  users_data u
INNER JOIN
  transacciones t
ON 
  u.id = t.client_id
WHERE
  u.gender = 'male' AND
  t.amount > 200 AND
  YEAR (t.date) = 2019 AND
  t.mcc = 5814
nro_clientes
2

4.7.4 ¿Cuántos clientes de género masculino tienen más de 3 tarjetas activas y realizaron transacciones superiores a $2,000?

Code 

WITH T1 AS
(
SELECT
  u.id,
  SUM (c.num_cards_issued) AS total_tarjetas
FROM
  users_data u
INNER JOIN
  cards_data c
ON
  u.id = c.client_id
INNER JOIN
  transacciones t
ON 
  t.client_id = c.client_id
WHERE
  u.gender = 'male' AND
  c.fecha_expiracion BETWEEN '20191201' AND '20241231' AND-- FILTRAMOS PARA VER LAS TARJETAS ACTIVAS DESDE LA FECHA ACTUAL
  t.amount > 2000
GROUP BY
  u.id
HAVING
  SUM (c.num_cards_issued) > 3
)
SELECT
  COUNT (1) AS nro_clientes
FROM T1
nro_clientes
50

4.7.5 ¿Cuántos clientes femeninos tienen más de 5 tarjetas activas y un puntaje de crédito superior a 700?

Code 

WITH T1 AS
(
SELECT
  u.id,
  SUM (c.num_cards_issued) AS total_tarjetas
FROM
  users_data u
INNER JOIN
  cards_data c
ON
  u.id = c.client_id
WHERE
  u.gender = 'female' AND
  c.fecha_expiracion BETWEEN '20191201' AND '20241231' AND-- FILTRAMOS PARA VER LAS TARJETAS ACTIVAS DESDE LA FECHA ACTUAL
  u.credit_score > 700
GROUP BY
  u.id
HAVING
SUM (c.num_cards_issued) > 5
)
SELECT
COUNT (1) AS nro_clientes
FROM T1
nro_clientes
147

4.7.6 ¿Cuál es la diferencia en la deuda total entre clientes masculinos y femeninos con tarjeta de crédito?

Code 

WITH T1 AS
(
SELECT
  SUM (CASE WHEN u.gender = 'male' THEN u.total_debt ELSE 0 END) AS deuda_male,
  SUM (CASE WHEN u.gender = 'female' THEN u.total_debt ELSE 0 END) AS deuda_female
FROM
  users_data u
INNER JOIN
  cards_data c
ON
  u.id = c.client_id
WHERE
  c.card_type = 'credit'
)
SELECT
  *,
  deuda_male - deuda_female AS diferencia
FROM T1
deuda_maledeuda_femalediferencia
61.297.40959.207.8402.089.569

EL GENERO MASCULINO TIENE 2.089.569 $ MAS DE DEUDA TOTAL QUE EL GENERO FEMENINO

4.7.7 ¿Cuántos clientes de género masculino realizaron más de 5 transacciones en comercios con MCC 5732 (electrónica) durante 2017?

Code 

WITH T1 AS
(
SELECT
  u.id,
  COUNT (DISTINCT t.id) AS nro_transacciones
FROM
  users_data u
INNER JOIN
  transacciones t
ON
  u.id = t.client_id
WHERE
  u.gender = 'male' AND
  t.mcc = 5732 AND
  YEAR (t.date) = 2017
GROUP BY
  u.id
HAVING
  COUNT (DISTINCT t.id) > 5
)
SELECT
  COUNT (1) AS nro_clientes
FROM T1
nro_clientes
4

4.7.8 ¿Cuántos clientes masculinos han realizado más de 100 transacciones en comercios con el código MCC 5411 (supermercados)?

Code 

WITH T1 AS
(
SELECT
  u.id,
  COUNT (DISTINCT t.id) AS nro_transacciones
FROM
  users_data u
INNER JOIN
  transacciones t
ON
  u.id = t.client_id
WHERE
  u.gender = 'male' AND
  t.mcc = 5411 
GROUP BY
  u.id
HAVING
  COUNT (DISTINCT t.id) > 100
)
SELECT
  COUNT (1) AS nro_clientes
FROM T1
nro_clientes
594