1 CREATE OR REPLACE VIEW alliance_resources AS
2 WITH planet_estimates AS (
3 SELECT ps.tick, alliance, hidden,size,score,(metal+crystal+eonium) AS resources
4 ,score + (metal+crystal+eonium)/300 + hidden/100 AS nscore2
5 ,score + (metal+crystal+eonium)/300 + hidden/100 + (endtick()-tick())*(
6 250*size + COALESCE(metal_ref + crystal_ref + eonium_ref,7)* 1000
7 + CASE extraction WHEN 0 THEN 3000 WHEN 1 THEN 11500 ELSE COALESCE(extraction,3)*3000*3 END
8 )*(1.35+0.005*COALESCE(fincents,20))/100 AS nscore3
9 FROM current_planet_stats p
10 JOIN current_planet_scans ps USING (pid)
11 LEFT OUTER JOIN current_development_scans ds USING (pid)
13 SELECT *, RANK() OVER(PARTITION BY alliance ORDER BY score DESC) AS rank FROM planet_estimates
15 SELECT alliance, sum(resources) AS resources, sum(hidden) AS hidden
16 ,sum(nscore2)::bigint AS nscore2, sum(nscore3)::bigint AS nscore3
17 ,count(*) AS planets, sum(score) AS score, sum(size) AS size
18 ,avg(tick)::int AS avgtick
19 FROM planet_ranks WHERE rank <= 60
22 SELECT aid,alliance,a.relationship,s.members,r.planets
23 ,s.score, r.score AS topscore, s.size, r.size AS topsize
25 ,(s.score + (resources / 300) + (hidden / 100))::bigint AS nscore
26 ,nscore2, nscore3, avgtick
28 JOIN top_planets r USING (alliance)
29 LEFT OUTER JOIN (SELECT aid,score,size,members FROM alliance_stats
30 WHERE tick = (SELECT max(tick) FROM alliance_stats)) s USING (aid)