WITH
aus AS (SELECT DISTINCT ON (pid) pid, fid, name,tick
FROM fleets f join fleet_scans fs using (fid)
WITH
aus AS (SELECT DISTINCT ON (pid) pid, fid, name,tick
FROM fleets f join fleet_scans fs using (fid)
ORDER BY pid,tick DESC)
,ships AS (SELECT pid, tick, ship, amount, id AS ship_id
FROM fleet_ships fs JOIN aus USING(fid) JOIN ship_stats USING(ship))
ORDER BY pid,tick DESC)
,ships AS (SELECT pid, tick, ship, amount, id AS ship_id
FROM fleet_ships fs JOIN aus USING(fid) JOIN ship_stats USING(ship))