近6个月每月都有还款行为用户分析

-- 近6个月每月都有还款行为用户中（都没用、第一月用过后没再用、每月都用）线下转账的用户
-- 近6月的数据存放该表
create table open.t_ttt_0401(
    uid bigint
) WITH (appendonly=true) DISTRIBUTED BY (uid);

-- 这是这一批每个月都有信贷还款的用户
insert into open.t_ttt_0401
select
    uid
from (
    select
        to_char(f_business_time, 'YYYY-MM') as m,
        f_trans_buyer_user_id as uid
    from wallet.t_wallet_info
    where f_business_time>='2020-04-01'
    and f_business_time<'2020-10-01'
    and F_trans_seller_user_id in (select f_sp_user_id from fn.t_scenario_sp_map where F_sp_scenario in (40000,40001,40002))
    and F_business_type in (5,6,7)
    and F_trans_type<>17
    and F_recvable_bank_user_id != 202
    group by m,uid
) t1 group by uid having count(*)=6;

-- 始终没有用线下转账的人
select
    uid
from (
    select
        uid,
        max(type) as type
    from (
        select
            uid,
            type
        from (
            select
                f_trans_buyer_user_id as uid,
                case when F_recvable_bank_user_id=8999 or F_recvable_bank_user_id=8998 then 'DaE'
                    else 'qiTa' end type
            from wallet.t_wallet_info t1
            left join open.t_ttt_0401 t2 on t1.f_trans_buyer_user_id=t2.uid
            where f_business_time>='2020-04-01'
            and f_business_time<'2020-10-01'
            and F_trans_seller_user_id in (select f_sp_user_id from fn.t_scenario_sp_map where F_sp_scenario in (40000,40001,40002))
            and F_business_type in (5,6,7)
            and F_trans_type<>17
            and F_recvable_bank_user_id != 202
            and t2.uid is not null
        ) t3 group by uid,type
    ) t4 group by uid having count(*)=1
) t5 where type='qiTa';

-- 近6个月中仅第一个月用过一次的用户
select
    f_trans_buyer_user_id
from wallet.t_wallet_info t5
left join (
    select
        uid
    from (
        select
            f_trans_buyer_user_id as uid
        from wallet.t_wallet_info t1
        left join open.t_ttt_0401 t2 on t1.f_trans_buyer_user_id=t2.uid
        where f_business_time>='2020-04-01'
        and f_business_time<'2020-10-01'
        and F_trans_seller_user_id in (select f_sp_user_id from fn.t_scenario_sp_map where F_sp_scenario in (40000,40001,40002))
        and F_business_type in (5,6,7)
        and F_trans_type<>17
        and (F_recvable_bank_user_id=8999 or F_recvable_bank_user_id=8998)
        and t2.uid is not null
    ) t3 group by uid having count(*)=1
) t4 on t5.f_trans_buyer_user_id=t4.uid
where f_business_time>='2020-04-01' and f_business_time<'2020-05-01'
and F_trans_seller_user_id in (select f_sp_user_id from fn.t_scenario_sp_map where F_sp_scenario in (40000,40001,40002))
and F_business_type in (5,6,7)
and F_trans_type<>17
and (F_recvable_bank_user_id=8999 or F_recvable_bank_user_id=8998)
and t4.uid is not null;

-- 每次都用了线下大额转账的人
select
    uid
from (
    select
        uid,
        max(type) as type
    from (
        select
            uid,
            type
        from (
            select
                f_trans_buyer_user_id as uid,
                case when F_recvable_bank_user_id=8999 or F_recvable_bank_user_id=8998 then 'DaE'
                    else 'qiTa' end type
            from wallet.t_wallet_info t1
            left join open.t_ttt_0401 t2 on t1.f_trans_buyer_user_id=t2.uid
            where f_business_time>='2020-04-01'
            and f_business_time<'2020-10-01'
            and F_trans_seller_user_id in (select f_sp_user_id from fn.t_scenario_sp_map where F_sp_scenario in (40000,40001,40002))
            and F_business_type in (5,6,7)
            and F_trans_type<>17
            and F_recvable_bank_user_id != 202
            and t2.uid is not null
        ) t3 group by uid,type
    ) t4 group by uid having count(*)=1
) t5 where type='DaE';