with pg_stat_all_tables_vw as  (
 select
 a.snap_id             ,
 sample_start_time         ,
 relid               ,
 schemaname          ,
 relname             ,
 seq_scan            ,
 seq_tup_read        ,
 idx_scan            ,
 idx_tup_fetch       ,
 n_tup_ins           ,
 n_tup_upd           ,
 n_tup_del           ,
 n_tup_hot_upd       ,
 n_live_tup          ,
 n_dead_tup          ,
 autovacuum_count    
 from pg_stat_all_tables_history a,  pg_awr_snapshots_cust b where a.snap_id = b.snap_id
 and a.snap_id between :begin_snap_id and :end_snap_id
 and schemaname not in ('pg_catalog')),
 get_delta_data as (select snap_id,relid,sample_start_time,schemaname,relname,
 case WHEN (seq_scan-lag(seq_scan::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) = seq_scan then null
 else (seq_scan-lag(seq_scan::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) END AS delta_seq_scan,
 case WHEN (seq_tup_read-lag(seq_tup_read::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) = seq_tup_read then null
 else (seq_tup_read-lag(seq_tup_read::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) END AS delta_seq_tup_read,
 case WHEN (idx_scan-lag(idx_scan::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) = idx_scan then null
 else (idx_scan-lag(idx_scan::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) END AS delta_idx_scan,
 case WHEN (idx_tup_fetch-lag(idx_tup_fetch::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) = idx_tup_fetch then null
 else (idx_tup_fetch-lag(idx_tup_fetch::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) END AS delta_idx_tup_fetch,
 case WHEN (n_tup_ins-lag(n_tup_ins::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) = n_tup_ins then null
 else (n_tup_ins-lag(n_tup_ins::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) END AS delta_n_tup_ins,
 case WHEN (n_tup_upd-lag(n_tup_upd::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) = n_tup_upd then null
 else (n_tup_upd-lag(n_tup_upd::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) END AS delta_n_tup_upd,
 case WHEN (n_tup_del-lag(n_tup_del::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) = n_tup_del then null
 else (n_tup_del-lag(n_tup_del::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) END AS delta_n_tup_del,
 case WHEN (n_tup_hot_upd-lag(n_tup_hot_upd::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) = n_tup_hot_upd then null
 else (n_tup_hot_upd-lag(n_tup_hot_upd::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) END AS delta_n_tup_hot_upd,
 case WHEN (n_live_tup-lag(n_live_tup::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) = n_live_tup then null
 else (n_live_tup-lag(n_live_tup::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) END AS delta_n_live_tup,
 case WHEN (n_dead_tup-lag(n_dead_tup::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) = n_dead_tup then null
 else (n_dead_tup-lag(n_dead_tup::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) END AS delta_n_dead_tup,
 case WHEN (autovacuum_count-lag(autovacuum_count::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) = autovacuum_count then null
 else (autovacuum_count-lag(autovacuum_count::bigint,1,0::bigint) OVER (partition by schemaname,relname ORDER BY snap_id) ) END AS delta_autovacuum_count
 from pg_stat_all_tables_vw)
 select relid,schemaname,relname,sum(delta_seq_scan) AS total_full_table_scan,
 round((sum(delta_seq_tup_read)/sum(delta_seq_scan))::numeric,2) AS avg_rows_per_fts,
 round((sum(delta_idx_tup_fetch)/sum(delta_seq_scan))::numeric,2) AS avg_rows_fetch_per_index,
 round((sum(delta_n_tup_ins)/sum(delta_seq_scan))::numeric,2) AS avg_rows_inserted,
 round((sum(delta_n_tup_upd)/sum(delta_seq_scan))::numeric,2) AS avg_rows_updated,
 round((sum(delta_n_tup_del)/sum(delta_seq_scan))::numeric,2) AS avg_rows_deleted,
 round((sum(delta_n_tup_hot_upd)/sum(delta_seq_scan))::numeric,2) AS avg_rows_hot_updated,
 max(delta_n_live_tup) AS max_live_rows,
 max(delta_n_dead_tup)AS max_dead_rows,
 sum(delta_autovacuum_count) AS total_autovaccum_initiated
 from get_delta_data
 where delta_seq_scan>0
 group by schemaname,relname,relid
 having (sum(delta_seq_scan) is not null )
 order by total_full_table_scan DESC
limit 20;