database/views/alliances_resources.sql

   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)
  12 ), planet_ranks AS (
  13         SELECT *, RANK() OVER(PARTITION BY alliance ORDER BY score DESC) AS rank FROM planet_estimates
  14 ), top_planets AS (
  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
  20         GROUP BY alliance
  21 )
  22 SELECT aid,alliance,a.relationship,s.members,r.planets
  23         ,s.score, r.score AS topscore, s.size, r.size AS topsize
  24         ,r.resources,r.hidden
  25         ,(s.score + (resources / 300) + (hidden / 100))::bigint AS nscore
  26         ,nscore2, nscore3, avgtick
  27 FROM alliances a
  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)
  31 ;
  32