drop table sospdm.tmp_yinfei_yuanzuan_redbag;
create table sospdm.tmp_yinfei_yuanzuan_redbag stored as rcfile as
select member_no,red_amount,process_time as min_date,date_add(process_time,diffdate-1) as max_date,diffdate from 
(
    select member_no,red_amount,process_time,diffdate,row_number() over(partition by member_no order by diffdate desc ) as rk 
    from 
    (
        select
             member_no,red_amount,process_time,diffdate
            ,row_number() over(partition by member_no,red_amount,process_time order by diffdate) as rn
        from 
        (
            select 
                 t1.member_no,t1.red_amount,t1.process_time,datediff(t2.process_time,t1.process_time)+1 as diffdate
            from 
            (
                select member_no,sum(red_amount) as red_amount,to_date(process_time) as process_time
                from sosp_ssa.xxx
                where to_date(process_time)>='2018-12-20' and to_date(process_time)<='2019-01-20' and activity_id = '1212draw'
                group by member_no,to_date(process_time)
            ) t1 
            inner join
            (
                select member_no,sum(red_amount) as red_amount,to_date(process_time) as process_time
                from sosp_ssa.xxx
                where to_date(process_time)>='2018-12-20' and to_date(process_time)<='2019-01-20' and activity_id = '1212draw'
                group by member_no,to_date(process_time)
            ) t2
            on t1.member_no=t2.member_no
            where datediff(t2.process_time,t1.process_time) >= 0
        ) t3
    ) t4 where diffdate = rn and rn >= 7
) t5 where rk = 1;