Schemaverse: Ships Heatmap SQL

ships_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 ship_flight_recorder
cross join scale
where 1=1
and tic = ( select last_value-1 from tic_seq )
and location[0] between -scale and scale
and location[1] between -scale and scale
group by xb, yb
order by xb, yb
;

-- Schemaverse Ships Heatmap SQL