(select * from
        (
                        select hdid
                        ,rtime
                        ,event.event_info['capture_time'] capture_time
                        ,event.event_info['app'] app_list
                        ,event.event_info['is_system'] is_system_list
                        ,event.event_info['name'] name_list
                        ,event.event_info['flags'] flags_list
                        ,row_number() over(partition by hdid order by rtime desc) as rank
                        from vlog.like_user_event_hour_orc
                        where day = '${day}'
                                and event_id = '0104099'
                        group by hdid
                        ,rtime
                        ,event.event_info['capture_time']
                        ,event.event_info['app']
                        ,event.event_info['is_system']
                        ,event.event_info['name']
                        ,event.event_info['flags']
                )a
                where rank=1
        )b
        lateral view posexplode(split(regexp_replace(app_list, '\\[|\\]', ''), ',')) t1 as pos1,app
        lateral view posexplode(split(regexp_replace(is_system_list, '\\[|\\]', ''), ',')) t2 as pos2,is_system
        lateral view posexplode(split(regexp_replace(name_list, '\\[|\\]', ''), ',')) t3 as pos3,name
        lateral view posexplode(split(regexp_replace(flags_list, '\\[|\\]', ''), ',')) t4 as pos4,flags
        where pos1=pos2
                and pos1=pos3
                and pos1=pos4