select * from
(select
card_no,
trx_time,
merchant_id,
month(trx_time) as fea_month,
dayofmonth(trx_time) as fea_day_of_month,
hour(trx_time) as fea_hour,
week(trx_time) as fea_week,
substr(card_no, 1, 6) as card_no_prefix,
max(trx_amt) over w30d as w30d_trx_max ,
min(trx_amt) over w30d as w30d_trx_min,
sum(trx_amt) over w30d,
avg(trx_amt) over w30d,
max(usd_amt) over w30d,
min(usd_amt) over w30d,
sum(usd_amt) over w30d,
avg(usd_amt) over w30d,
max(org_amt) over w30d,
min(org_amt) over w30d,
sum(org_amt) over w30d,
avg(org_amt) over w30d,
distinct_count(merchant_id) over w30d,
count(merchant_id) over w30d,
distinct_count(term_city) over w30d,
count(term_city) over w30d,
max(trx_amt) over w10d,
min(trx_amt) over w10d,
sum(trx_amt) over w10d,
avg(trx_amt) over w10d,
max(usd_amt) over w10d,
min(usd_amt) over w10d,
sum(usd_amt) over w10d,
avg(usd_amt) over w10d,
max(org_amt) over w10d,
min(org_amt) over w10d,
sum(org_amt) over w10d,
avg(org_amt) over w10d,
distinct_count(merchant_id) over w10d,
count(merchant_id) over w10d,
distinct_count(term_city) over w10d,
count(term_city) over w10d
from tran
window w30d as (PARTITION BY tran.card_no ORDER BY tran.trx_time ROWS_RANGE BETWEEN 30d PRECEDING AND CURRENT ROW),
w10d as (PARTITION BY tran.card_no ORDER BY tran.trx_time ROWS_RANGE BETWEEN 10d PRECEDING AND CURRENT ROW)) as trx_fe
last join card_info order by card_info.crd_lst_isu_dte on trx_fe.card_no = card_info.crd_nbr and trx_fe.trx_time >= card_info.crd_lst_isu_dte ; |