Schemaverse: Attack Heatmap SQL

attack_heatmap.sql

with max_xy as (
    select greatest(
        max( abs( location_x ) ),
        max( abs( location_y ) )
    ) v
    from planets
),
scale as (
    select m * ceil( v / m ) scale from (
        select 10 ^ floor( log( v ) ) m, v
        from max_xy
    )a
)
select count(1),
width_bucket( location[0], -scale, scale, 100 ) xb,
width_bucket( location[1], -scale, scale, 100 ) yb
from my_events
cross join scale
where public
and action = 'ATTACK'
and tic = ( select last_value-1 from tic_seq )
group by xb, yb
order by xb, yb
;

-- Schemaverse Attack Heatmap SQL