1 CREATE OR REPLACE VIEW def_leeches 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
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
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)
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
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
33 LEFT JOIN f2 USING (uid)
34 GROUP BY uid,username,defense_points, sent_value