Estoy tratando de escribir una consulta que defina y luego describa la 'actividad de suscripción' de una tabla de transacciones de usuario. Digamos que la tabla de transacciones desde la que estamos trabajando tiene customer_id, created_at (fecha de la transacción), comerciante_name, transaction_amount.

La tabla de salida final debe tener customer_Id como la primera columna, activity_month como la segunda columna, comerciante_name_x como la tercera columna y _merchant_name_y como la cuarta columna (puede tener infinitas columnas a partir de entonces para varios comerciantes). Y aquí está la parte complicada: la tabla debe llenarse con "1" si esa celda representa una cadena de al menos 3 meses consecutivos de ese usuario realizando transacciones al menos una vez con ese comerciante y "0" en caso contrario. .

Aquí hay un código básico que me dio lo que se vería como la tabla de salida con '1' para actividad y '0' para ninguna actividad. Debe enmendarse para que '1' aparezca solo si es parte de una cadena ininterrumpida de> = 3 meses de actividad consecutiva para el comerciante dado y '0' en caso contrario.

SELECT customer_id
    , LAST_DAY(created_at::DATE) AS month
    , MAX(CASE WHEN merchant_name = 'Amazon Prime' THEN 1 ELSE 0 END) AS amazon
    , MAX(CASE WHEN merchant_name = 'Netflix.com' THEN 1 ELSE 0 END) AS netflix
FROM TABLE
GROUP BY 1,2

El resultado se vería así: '1' denota que el usuario realizó transacciones con ese comerciante al menos una vez (no importa si una, dos o 300 veces ese mes) y que es parte de> = 3 meses consecutivos de ese usuario que realiza transacciones con ese comerciante

CUSTOMER_ID    MONTH       AMAZON   NETFLIX
54321          2019-04-30       1         0
54321          2019-03-31       1         0
54321          2019-02-29       1         1
54321          2019-01-31       1         1
54321          2018-12-31       0         1
54321          2018-11-30       0         0  

Trabajando desde una TABLA que enumera y describe transacciones:

ColumnsData                  Type
TRANSACTION_ID               NUMBER(38,0)
CREATED_AT                   TIMESTAMP_NTZ(9)
AMOUNT_DOLLARS   NUMBER(38,0)
CUSTOMER_ID                  NUMBER(38,0)
MERCHANT_NAME                VARCHAR(16777216)

(una muestra de la tabla de datos):

TRANSACTION_ID     CREATED_AT     AMOUNT_DOLLARS   CUSTOMER_ID   MERCHANT_NAME
1234567            2018-08-23     57.31            306797979     Amazon Prime
7654321            2020-09-21     10.99            309221214     Stp & Shop
9999971            2020-01-07     11.59            509227711     Lyft Com
6549875            2019-05-10     88.23            311188226     Lttle Caesar
3121541            2020-07-31     72.01            307746845     Redbox
1279875            2020-04-05     15.20            315151515     Family Dollar

(marca de tiempo omitida de CREATED_AT para compacidad) La consulta debe escalar entre miles de clientes, cada uno de los cuales realiza muchas transacciones. Muchas gracias.

0
Paul Anthony 25 oct. 2020 a las 22:11

1 respuesta

La mejor respuesta

Creo que si entiendo correctamente, necesitará usar una función de ventana como la siguiente:

ACTUALIZACIÓN: actualizado para reflejar los datos de muestra. Tomé los datos que proporcionó y agregué 3 registros adicionales para Customer_ID = 306797979 y Merchant_name = Amazon. Lo que verá en los resultados es que los registros de 2018-08 y 09 son 0 porque no hay 3 meses consecutivos de una suscripción de Amazon en ese momento. Cuando llegamos a 2018-10 y 11, ambos ahora tienen 3 períodos posteriores de transacciones.

create temporary table temp (
TRANSACTION_ID NUMBER(38,0),
CREATED_AT TIMESTAMP_NTZ(9),
AMOUNT_DOLLARS NUMBER(38,0),
CUSTOMER_ID NUMBER(38,0),
MERCHANT_NAME VARCHAR(16777216)
);

insert into temp 
    values 
    (1234567, '2018-08-23 00:00:00', 57.31, 306797979, 'Amazon Prime'),
    (7654321, '2020-09-21 00:00:00', 10.99, 309221214, 'Stp & Shop'),
    (9999971, '2020-01-07 00:00:00', 11.59, 509227711, 'Lyft Com'),
    (6549875, '2019-05-10 00:00:00', 88.23, 311188226, 'Lttle Caesar'),
    (3121541, '2020-07-31 00:00:00', 72.01, 307746845, 'Redbox'),
    (1279875, '2020-04-05 00:00:00', 15.20, 315151515, 'Family Dollar'),
    (1234567, '2018-11-23 00:00:00', 57.31, 306797979, 'Amazon Prime'),
    (1234236, '2018-09-23 00:00:00', 57.31, 306797979, 'Amazon Prime'),
    (3972831, '2018-10-23 00:00:00', 57.31, 306797979, 'Amazon Prime');

with _filler_dates as (
    select
      date_trunc('MONTH',dateadd(
        month,
        '-' || row_number() over (order by null),
        dateadd(day, 1 , current_date()))) as filler_date
    from table (generator(rowcount => 30))
),
_data as(
select customer_id 
    , merchant_name
    , amount_dollars
    , date_trunc('MONTH', CREATED_AT) as cur_month
    , COALESCE(LEAD(cur_month,1,NULL) OVER (
        PARTITION BY customer_id
        ORDER BY cur_month)
        , date_trunc('MONTH', current_date())) as next_month   
from temp)
,_merged as (
select d.customer_id
    , d.merchant_name
    , CASE WHEN fd.filler_date <> cur_month then NULL else d.amount_dollars end as amount_dollars
    , fd.filler_date  
from _data d
join _filler_dates fd
 on fd.filler_date between  d.cur_month and dateadd(MONTH, -1, d.next_month)
)

select *
    , CASE WHEN sum(CASE WHEN amount_dollars > 0 THEN 1 ELSE 0 END)
                 OVER (PARTITION BY customer_id, merchant_name
                 ORDER BY filler_date ASC
                 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) >= 3
           THEN 1 ELSE 0 END as amazon    
from _merged
order by 1,2,4;

//drop table temp;
0
Daniel Zagales 2 nov. 2020 a las 10:59