Prueba Tecnica

Author

Jorge Valente Hernandez Castelan

SQL TEST

Preguntas

Dadas las siguientes tablas:

SELECT * FROM users;

Y

SELECT * FROM loan_details;

Escribe una consulta que de la lista de usuarios que pidieron un préstamo más de una vez en el mismo día, agrupado por usuario y tipo de préstamo. Ordenado de mayor a menor por fecha de creación del préstamo.

Respuesta

Para este ejercicio me tomé la libertad de crear una BD de muestra con las tablas correspondientes:

create table users (
    user_id int primary key,
    username varchar(50)
);

-- Insertar datos en users
insert into users (user_id, username) values
(1, 'John Doe'),
(2, 'Jane Don'),
(3, 'Alice Jones'),
(4, 'Lisa Romero');

## Crear tabla loan_details con una clave foránea a users
create table loan_details (
    user_loan_id int primary key,
    user_id int,
    loan_type int,
    loan_date date,
    foreign key (user_id) references users(user_id)
);

## Insertar datos en loan_details
insert into loan_details (user_loan_id, user_id, loan_type, loan_date) values
(1, 1, 1, '2023-08-02'),
(2, 2, 1, '2023-08-03'),
(3, 3, 2, '2023-08-02'),
(4, 4, 2, '2023-08-04'),
(5, 2, 2, '2023-08-03'),
(6, 1, 1, '2023-08-02'),
(7, 3, 2, '2023-08-04'),
(8, 4, 3, '2023-08-03'),
(9, 1, 4, '2023-08-03'),
(10, 3, 1, '2023-08-02'),
(11, 4, 2, '2023-08-04'),
(12, 3, 2, '2023-08-02'),
(13, 1, 1, '2023-08-02'),
(14, 4, 3, '2023-08-03');

Resultando en:

## Consulta
select users.username, ld.loan_type tipo_prestamo, ld.loan_date fecha_prestamo,
       count(*) Cantidad_prestamos
from loan_details ld 
left join users on ld.user_id = users.user_id
group by users.username, ld.loan_type, ld.loan_date
having count(*) > 1
order by ld.loan_date desc
username tipo_prestamo fecha_prestamo Cantidad_prestamos
Lisa Romero 2 2023-08-04 2
Lisa Romero 3 2023-08-03 2
John Doe 1 2023-08-02 3
Alice Jones 2 2023-08-02 2

Dada la siguiente tabla:

SELECT * FROM transactions;

Crea una consulta que genere el monto promedio y total mensual de transacciones SETTLED. Por categoría junto con el monto acumulado mes a mes.

Respuesta

Al igual que en el caso anterior, armé la tabla en una BD de prueba:

create table transactions (
    transaction_date date,
    state varchar(50),
    category varchar(50),
    amount decimal(10, 2)
);

insert into transactions (transaction_date, state, category, amount) values
('2023-08-07', 'SETTLED', 'CREDIT_CARD', -1044.33),
('2023-08-21', 'SETTLED', 'CREDIT_CARD', -1178.55),
('2023-09-04', 'CANCELED', 'CHECKING', -20.00),
('2023-09-04', 'SETTLED', 'CHECKING', -1449.75),
('2023-10-02', 'SETTLED', 'CHECKING', -12530.39),
('2023-10-09', 'SETTLED', 'CHECKING', -1815.89),
('2023-10-16', 'SETTLED', 'CHECKING', -1863.37),
('2023-10-23', 'SETTLED', 'CHECKING', -9275.68),
('2023-10-30', 'CANCELED', 'CHECKING', -40.00),
('2023-10-30', 'SETTLED', 'CHECKING', -6223.13),
('2023-11-06', 'CANCELED', 'CHECKING', -658.09),
('2023-11-06', 'SETTLED', 'CHECKING', -12602.32),
('2023-11-13', 'CANCELED', 'CHECKING', -84.45),
('2023-11-13', 'SETTLED', 'CHECKING', -8090.13),
('2023-11-20', 'CANCELED', 'CHECKING', -20.00),
('2023-11-20', 'SETTLED', 'CHECKING', -3187.59),
('2023-11-20', 'SETTLED', 'CREDIT_CARD', -2563.17),
('2023-11-27', 'CANCELED', 'CHECKING', -194.81),
('2023-11-27', 'SETTLED', 'CHECKING', -10376.22),
('2023-11-27', 'SETTLED', 'CREDIT_CARD', -4117.82),
('2023-12-04', 'SETTLED', 'CHECKING', -3579.06),
('2023-12-04', 'SETTLED', 'CREDIT_CARD', -6917.86),
('2023-12-11', 'CANCELED', 'CHECKING', -239.85),
('2023-12-11', 'SETTLED', 'CHECKING', 0.00),
('2023-12-11', 'SETTLED', 'CREDIT_CARD', -6223.47),
('2023-12-18', 'CANCELED', 'CHECKING', -199.96),
('2023-12-18', 'CANCELED', 'CREDIT_CARD', -371.89),
('2023-12-18', 'SETTLED', 'CHECKING', -3988.03),
('2023-12-18', 'SETTLED', 'CREDIT_CARD', -7955.47),
('2023-12-25', 'AUTHORIZED', 'CREDIT_CARD', -20.00),
('2023-12-25', 'CANCELED', 'CHECKING', -20.00),
('2023-12-25', 'CANCELED', 'CREDIT_CARD', -320.24),
('2023-12-25', 'SETTLED', 'CHECKING', -5773.02),
('2023-12-25', 'SETTLED', 'CREDIT_CARD', -5404.81),
('2024-01-01', 'CANCELED', 'CREDIT_CARD', -157.46),
('2024-01-01', 'SETTLED', 'CHECKING', -8633.34),
('2024-01-08', 'CANCELED', 'CREDIT_CARD', -959.71),
('2024-01-08', 'SETTLED', 'CHECKING', -2676.16),
('2024-01-15', 'SETTLED', 'CHECKING', -1662.51),
('2024-01-15', 'SETTLED', 'CREDIT_CARD', -1095.59),
('2024-01-22', 'SETTLED', 'CREDIT_CARD', -784.72);

Y la consulta:

with transacciones_mensuales as (
    select date(date_format(transaction_date, '%Y-%m-01')) fecha, 
       avg(amount) monto_promedio, 
       sum(amount) monto_total_mensual
from transactions
where state = "SETTLED"
group by date(date_format(transaction_date, '%Y-%m-01'))
)

select 
    fecha,
    monto_promedio,
    monto_total_mensual,
    sum(monto_total_mensual) over (order by fecha 
    rows between unbounded preceding and current row) acumulado_mensual
from transacciones_mensuales

Resultando en:

fecha monto_promedio monto_total_mensual acumulado_mensual
2023-08-01 -1111.44 -2222.88 -2222.88
2023-09-01 -1449.75 -1449.75 -3672.63
2023-10-01 -6341.692 -31708.46 -35381.09
2023-11-01 -6822.875 -40937.25 -76318.34
2023-12-01 -4980.215 -39841.72 -116160.06
2024-01-01 -2970.464 -14852.32 -131012.38

BI TEST: Grafico

Uno de los productos de Rabbit llamado producto R permite a los tenderos (dueños de una tienda) la posibilidad de recibir pago de servicios y realizar recargas de tiempo aire a los usuarios finales. Cada transacción genera un ingreso para Rabbit equivalente al 1% del monto total transaccionado o de la recarga de tiempo aire. Cada tendero que activa el uso del producto R implica una inversión para Rabbit que puede variar de acuerdo con el mes. La relación entre este costo de colocación individual y el mes de activación del producto se encuentra en el archivo cac.csv.

Para el objetivo de este ejercicio se requieren las siguientes definiciones:

  • Tendero: Dueño de una tienda

  • Revenue: Monto total que ha sido obtenido por los ingresos que genera el producto R para Rabbit acumulados hasta cierto mes.

  • Retorno de la inversión: es igual al revenue acumulado hasta cierto mes menos la inversión original y el resultado de esta resta dividido entre la inversión original. (revenue-inversión original)/inversión original

  • El ingreso se calcula como el monto de la transacción multiplicado por 0.01; es decir, cada transacción genera 1% de ingreso para Rabbit con respecto del monto total transaccionado.

  • Solo las transacciones con status: “SUCCESSFUL” son válidas para generar ingresos.

  • La inversión original se calcula como el número de clientes que fueron activados en un mes en particular multiplicado por el costo de colocación individual de dicho mes.

  • Cosecha: Periodos en los que se han realizado activaciones del producto R. Estos periodos se agrupan por mes y año en formato “YYYY-MM”

  • No todos los tenderos que se activan en el producto R terminan realizando transacciones a clientes finales.

Con base en la información descrita anteriormente responde los siguientes puntos mediante Python o R project y adjunta tu código o notebook en el cuerpo del correo que contiene este documento.

  1. Crear una gráfica que muestre el retorno de la inversión que ha generado cada una de las cosechas del producto R en cada uno de los meses subsecuentes a la originación de la cosecha. Es decir; que porcentaje de la inversión se ha recuperado para cada cosecha al mes 0, al mes 1 al mes 2, etc tras haber sido colocada. Mes cero es el mes de originación de la cosecha ya que los clientes regularmente empiezan a transaccionar el mismo día que activan el producto R.

  2. Identificar cuál sería el precio máximo permitido de costo de colocación individual para lograr recuperar la inversión original en menos de 3 meses. Para ello crear un promedio ponderado de comportamiento general de todas las cosechas.

Respuesta
library(dplyr, warn = FALSE)
library(tidyr)
library(jsonlite)
library(lubridate)
library(echarts4r)

# Carga de datos
activations <- vroom::vroom("activations.csv")
cac <- vroom::vroom("cac.csv")
transactions <- readxl::read_excel("transactions.xlsx")

# Arreglo en los datos de transacciones
transactions <-
  transactions |> 
  mutate(
    transactionHistory = gsub("\\'", "\"", transactionHistory),
    transactionHistory = lapply(transactionHistory, jsonlite::fromJSON)
  ) |>
  tidyr::unnest(transactionHistory) |> 
  mutate(
    transactionAmount = as.numeric(gsub("\\$", "", transactionAmount))
  ) 

cac <-
  cac |> 
  mutate(
    costPerActivation = as.numeric(gsub("\\$", "", costPerActivation)),
    cohort = as_date(zoo::as.yearmon(cohort, format = "%Y-%m"))
  )

activations <- 
  activations |> 
  mutate(
    activationAt = floor_date(activationAt, "month")
  )

Ahora, vamos a trabajar los datos:

transactions <-
transactions |> 
  select(!transactionId) |> 
  filter(status == "SUCCESSFUL") |> 
  mutate(
    revenue = transactionAmount*.01,
    date = floor_date(as_date(date), "month")) |> 
  left_join(
    activations,
    by = c("customerId" = "customerId")) |> 
  group_by(date, activationAt) |> 
  summarise(
    revenue = sum(revenue, na.rm = T),
    clientes = n_distinct(customerId)
  ) |> 
  group_by(activationAt) |> 
  arrange(date) |> 
  mutate(cumulative_revenue = cumsum(revenue)) |> 
  arrange(activationAt) |> 
  left_join(
    cac,
    by = c("activationAt" = "cohort") 
  ) |> 
  replace_na(list(costPerActivation = 0)) |> 
  mutate(costo_total = costPerActivation*clientes,
         retorno_de_inversion = (cumulative_revenue-
                                   costo_total)/costo_total,
         mes = as.character(row_number()-1))
`summarise()` has grouped output by 'date'. You can override using the
`.groups` argument.
GRAFICO
transactions |> 
  e_charts(mes) |> 
  e_line(retorno_de_inversion, symbol = "none") |> 
  e_tooltip(trigger = "axis", backgroundColor = "#fff") |> 
  e_theme("infographic") |> 
  e_title(
    "CAC recovery by enrollment Cohort",
    left = "center"
  ) |> 
  e_legend(right = 0, 
           orient = "vertical")

CALCULO DE VALOR MAXIMO

# Filtro final para obtener el promedio de retornos de inversion y 
# costo de activación individual
calculo <-
transactions |> 
  filter(
    mes <= 3
  ) |> 
  group_by(
    activationAt
  ) |> 
  summarise(
    ret_inv = mean(retorno_de_inversion, na.rm = T)
  ) |> 
  filter(!is.infinite(ret_inv)) |> 
  left_join(
    cac,
    by = c("activationAt" = "cohort")
  )

# Regresion lineal para calcular la correlacion entre el costo inicial
# y el retorno de inversion
model <-
summary(
  lm(calculo$ret_inv ~ calculo$costPerActivation)
)

# Ponderacion final
model$coefficients[[1]]/abs(model$coefficients[[2]])
[1] 31.44717