with aggr_fact_filtered as ( select random3_Hour.* from iaahd.random3_Hour JOIN iaahd.TEST_SIMPLE_RANDOM_2_d2 ON TEST_SIMPLE_RANDOM_2_d2.d2 = random3_Hour.d2 JOIN iaahd.TEST_SIMPLE_RANDOM_2_ATTEMPTS_long_2 ON TEST_SIMPLE_RANDOM_2_ATTEMPTS_long_2.ATTEMPTS_long_2 = random3_Hour.ATTEMPTS_long_2 where start_time > cast('2020-03-19 13:00:00.0' as timestamp) - interval 4*1 hours and start_time <= '2020-03-30 14:00:00.0' AND cast(TEST_SIMPLE_RANDOM_2_d2.d2 as varchar) IN ('D2_01','D2_02') ), time_dim as ( select date_key from iaahd.D_SYS_TIME where date_key > cast('2020-03-19 13:00:00.0' as timestamp) - interval 4*1 hours and date_key <= '2020-03-30 14:00:00.0' and date_key = trunc(date_key,'HH') ), all_dims as ( select distinct 'dummy' dummy_row ,random3_Hour.d2 from aggr_fact_filtered random3_Hour ), densificator as ( select date_key ,random3_Hour.d2 from time_dim join all_dims random3_Hour on 1 = 1 ), fact_densificated as ( select nvl(random3_Hour.start_time,densificator.date_key) start_time,nvl(random3_Hour.d2, densificator.d2) d2,ATTEMPTS_long_2,count_attempts_long,count_success_long,d1,sum_attempts_long,sum_success_long from densificator full join aggr_fact_filtered random3_Hour on densificator.date_key = random3_Hour.start_time and densificator.d2 = random3_Hour.d2 ) select max(pp_lag) prev_kpi,random3_Hour.d2 from (select * from (select LAG(SUM(random3_Hour.count_attempts_long),1) over (partition by random3_Hour.d2 order by start_time) "pp_lag",random3_Hour.d2 random3_Hour_d2,start_time from fact_densificated random3_Hour JOIN iaahd.TEST_SIMPLE_RANDOM_2_d2 ON TEST_SIMPLE_RANDOM_2_d2.d2 = random3_Hour.d2      group by start_time,random3_Hour.d2     ) random3_Hour where start_time >= '2020-03-19 13:00:00.0' AND start_time < '2020-03-30 14:00:00.0' ORDER BY pp_lag NULLS LAST LIMIT 100000) group by random3_Hour.d2 order by prev_kpi desc NULLS LAST LIMIT 5