crh cell hit moving average #ss #crh

moving_avg_crh_hit.sql

select t1.user_id,t1.journey_id,t1.journey_via_point_id,t1.location_id,t1.start_dt,t1.ts_jvp_id,t1.journey_via_point_type_id,t1.cell_id,
sum((case when t2.cell_id is  null then 0 else 1 end) + (case when t3.cell_id is  null then  0 else 1 end)
     + (case when t4.cell_id is  null then  0 else 1 end) + (case when t5.cell_id is  null then  0 else 1 end) +  (case when t6.cell_id is  null then  0 else 1 end))/5
 from (
select v.user_id,
v.journey_id,
v.journey_via_point_id,
v.location_id,
v.start_dt,
v.ts_jvp_id,
v.journey_via_point_type_id,
lag(v.cell_id,1) over(partition by user_id order by v.user_id, v.start_dt)  as bcell_id1,
lag(v.cell_id,2) over(partition by user_id order by v.user_id, v.start_dt)  as bcell_id2,
v.cell_id,
lead(v.cell_id,1) over(partition by user_id order by v.user_id, v.start_dt) as acell_id1,
lead(v.cell_id,2) over(partition by user_id order by v.user_id, v.start_dt) as acell_id2
from  dm_taozt.jvp_sorted_all v) t1
left join hit_crh04_30 t2 on (t1.bcell_id1=t2.cell_id)
left join hit_crh04_30 t3 on (t1.bcell_id2=t3.cell_id)
left join hit_crh04_30 t4 on (t1.cell_id=t4.cell_id)
left join hit_crh04_30 t5 on (t1.acell_id1=t5.cell_id)
left join hit_crh04_30 t6 on (t1.acell_id2=t6.cell_id) 
group by t1.user_id,t1.journey_id,t1.journey_via_point_id,t1.location_id,t1.start_dt,t1.ts_jvp_id,t1.journey_via_point_type_id,t1.cell_id;