database/functions/hostile_alliances.sql

   1 DROP FUNCTION IF EXISTS hostile_alliances(INT,INT);
   2 CREATE OR REPLACE FUNCTION hostile_alliances (first INT, last INT)
   3 RETURNS table(aid INT, alliance TEXT, hostile_count BIGINT, targeted BIGINT, targeted_raids BIGINT)
   4 AS $SQL$
   5 WITH hostile_alliances AS (
   6         SELECT COALESCE(aid,-1) AS aid, count(*) AS hostile_count
   7         FROM calls c
   8                 JOIN incomings i USING (call)
   9                 JOIN current_planet_stats s USING (pid)
  10         WHERE c.landing_tick BETWEEN $1 + i.eta AND $2 + i.eta
  11         GROUP BY aid
  12 ), alliance_targets_1 AS (
  13         SELECT COALESCE(aid,-1) AS aid, exists(
  14                         SELECT pid FROM raid_targets rt
  15                         JOIN raids r ON rt.raid = r.id
  16                         WHERE rt.pid = p.pid AND lc.landing_tick BETWEEN r.tick AND r.tick + r.waves - 1
  17                 ) AS raid_target
  18         FROM launch_confirmations lc
  19                 JOIN current_planet_stats p USING (pid)
  20                 JOIN fleets f USING (fid)
  21         WHERE f.mission = 'Attack'
  22                 AND lc.landing_tick BETWEEN $1 + lc.eta AND $2 + lc.eta
  23 ), alliance_targets AS (
  24         SELECT aid, count(*) AS targeted, count(NULLIF(raid_target,false)) AS targeted_raids
  25         FROM alliance_targets_1
  26         GROUP BY aid
  27 )
  28 SELECT aid, alliance, hostile_count, targeted, targeted_raids
  29 FROM hostile_alliances
  30         LEFT JOIN alliance_targets USING (aid)
  31         LEFT JOIN alliances USING (aid)
  32 $SQL$ LANGUAGE SQL STABLE;
  33