with recursive interesting as materialized (select block_state from block_states where (name like '%chest%' and name not like '%ender%') or name like '%shulker%'), chunk_nums as (select chunk_x, chunk_z, count(*) as cnt, avg(x) as avgx, avg(y) as avgy, avg(z) as avgz, (array_agg(x order by y desc))[1] as firstx, max(y) as firsty, (array_agg(z order by y desc))[1] as firstz from (select x>>4 as chunk_x, z>>4 as chunk_z, x, y, z from blocks where created_at > (extract(epoch from now())*1000.0)::bigint - $1::bigint*3600*1000 and block_state in (select * from interesting)) tmp group by chunk_x, chunk_z), lookup as (select (select id from dbscan where dbscan.dimension=0 and dbscan.server_id=1 and (dbscan.cluster_parent is not null or dbscan.is_core) and dbscan.x=chunk_nums.chunk_x and dbscan.z=chunk_nums.chunk_z) as id, cnt, avgx, avgy, avgz, firstx, firsty, firstz from chunk_nums), initial as (select id, id, false from lookup where id is not null), traversal(child, parent, root) as (select * from initial union all select child, case when cluster_parent is null then parent else cluster_parent end, cluster_parent is null from (with forced_cte as materialized (select traversal.child, traversal.parent, (select dbscan.cluster_parent from dbscan where dbscan.id=traversal.parent) as cluster_parent from traversal where not traversal.root) select * from forced_cte) tmp), leaf_to_root as materialized (select child, parent from traversal where traversal.root), aggr as (select leaf_to_root.parent, sum(lookup.cnt) as cnt, avg(avgx) as avgx, avg(avgy) as avgy, avg(avgz) as avgz, (array_agg(firstx order by firsty desc))[1] as firstx, max(firsty) as firsty, (array_agg(firstz order by firsty desc))[1] as firstz from lookup inner join leaf_to_root on leaf_to_root.child=lookup.id group by leaf_to_root.parent) select dbscan.x*16 as block_x, dbscan.z*16 as block_z, cnt, avgx::bigint, avgy::bigint, avgz::bigint, firstx, firsty, firstz from dbscan inner join aggr on aggr.parent=dbscan.id order by cnt desc;