database/def_leeches.sql

   1 CREATE OR REPLACE VIEW def_leeches AS
   2 WITH f AS (
   3         SELECT uid,fid,lc.pid,f.pid AS fpid,landing_tick,eta,back, SUM(fs.amount*(s.metal + s.crystal + s.eonium)/100.0) AS value
   4         FROM launch_confirmations lc
   5                 JOIN fleets f USING (fid)
   6                 JOIN fleet_ships fs USING (fid)
   7                 JOIN ship_stats s ON fs.ship = s.name
   8         WHERE mission = 'Defend'
   9         GROUP BY uid,fid,lc.pid,f.pid,landing_tick,eta,back
  10 ), f2 AS (
  11         SELECT f.uid
  12                 ,SUM(f.value / COALESCE(p.value, (SELECT value FROM planet_stats WHERE pid = f.fpid AND tick = landing_tick - eta ORDER BY tick DESC LIMIT 1))) AS sent_value
  13         FROM calls c
  14                 JOIN users u USING (uid)
  15                 JOIN f USING (pid,landing_tick)
  16                 LEFT JOIN (SELECT pid AS fpid,value,tick AS landing_tick FROM planet_stats) AS p USING (fpid, landing_tick)
  17         GROUP BY f.uid
  18 )
  19 SELECT uid,username,defense_points,count(call) AS calls
  20         , SUM(fleets) AS fleets, SUM(recalled) AS recalled
  21         ,count(NULLIF(fleets,0)) AS defended_calls
  22         ,SUM(value)::NUMERIC(4,2) AS value
  23         ,sent_value::NUMERIC(4,2)
  24 FROM (SELECT u.uid,username,defense_points,call,count(f.back) AS fleets
  25                 , count(NULLIF(f.landing_tick + f.eta -1 = f.back,TRUE)) AS recalled
  26                 ,SUM(f.value / COALESCE(p.value, (SELECT value FROM planet_stats WHERE pid = f.pid AND tick = landing_tick - eta ORDER BY tick DESC LIMIT 1))) AS value
  27         FROM users u
  28                 JOIN calls c USING (uid)
  29                 LEFT JOIN f USING (pid,landing_tick)
  30                 LEFT JOIN (SELECT pid,value,tick AS landing_tick FROM planet_stats) AS p USING (pid, landing_tick)
  31         GROUP BY u.uid,username,defense_points,call
  32 ) d
  33         LEFT JOIN f2 USING (uid)
  34 GROUP BY uid,username,defense_points, sent_value