test2

    -- ------------------------ 
    --  ---like+repo+comment = interact
    --  ------------------------
    -- drop table if exists interact_sum;
    -- create table interact_sum as
    -- select
    --     uid,
    --     count(action_type) as interact
    -- from tianchi_weibo.weibo_action_data_train
    -- group by uid;

    -----------------
    ---level point
    -----------------
    drop table if exists interact_levelpoint;
    create table interact_levelpoint as 
     select 
        uid,
        case 
            when interact_sum.interact>100 then interact_sum.interact*200
            when interact_sum.interact<=100 and interact_sum.interact>50 then interact_sum.interact*100
            when interact_sum.interact<=50 and interact_sum.interact>11 then interact_sum.interact*50
            when interact_sum.interact<=11 and interact_sum.interact>6 then interact_sum.interact*10
            when interact_sum.interact<=5 and interact_sum.interact>=0 then interact_sum.interact
            end as level
        from interact_sum;
    

    --------------------
    ----max level-------------
    ---------------------
    drop table if exists interact_levelpointmax;
    create table interact_levelpointmax as 
    select 
            uid,
            max(level) as max_level
        from interact_levelpoint
    group by uid;
        
        
    drop table if exists interact_levelmax;
    create table interact_levelmax as 
    select 
            uid,
            case 
                when max_level>=20000 then max_level/200
                when max_level>=5000 and max_level<20000 then max_level/100
                when max_level>=550 and max_level<5000 then max_level/50
                when max_level>=60 and max_level<550 then max_level/10
                when max_level>=0 and max_level<60 then max_level
                end as level_pointmax
        from interact_levelpointmax;

    ---------------------
    ---join dummy and predict-maxlevel ; clear NULL
    ---------------------
    drop table if exists uid_max;
    create table uid_max as 
        ------clear 0 from havenull
    select 
        havenull.uid,
        havenull.mid,
        case 
            when havenull.action_sum IS NULL then 0 
            when havenull.action_sum IS NOT NULL then cast(havenull.action_sum as bigint)
        end as action_sum
    from
            ------dummy0 inner join bsum 
        (
            select
                    x.uid,
                    x.mid,
                    y.action_sum as action_sum
            from
                    x
                    left outer join
                    (
                        select
                            interact_levelmax.uid,
                            interact_levelmax.levelpoint as action_sum
                        from blevelnum
                    )y
            on x.uid=y.uid
        )havenull;