example_report.sql

SELECT *,
      (t2.historic_comparison_sum - t1.sum) as delta_sum,
      (t2.historic_comparison_m - t1.m) as delta_m
    FROM
      (
    SELECT
      regexp_replace(lower(support_fact_finder.variable), ‘[^A-Za-z0-9]’, ‘_’, ‘g’) as variable,
      regexp_replace(lower(support_fact_finder.profile), ‘[^A-Za-z0-9]’, ‘_’, ‘g’) as profile,
      regexp_replace(lower(support_fact_finder.category), ‘[^A-Za-z0-9]’, ‘_’, ‘g’) as category,
      sum,
      support_fact_finder.base,
      base_sum,
      m
    FROM
      ( 
        SELECT * FROM (
          
    SELECT
      SUM(e),
      SQRT(
        SUM(
          POWER(m, 2)
        )
      ) AS m,
      variable
    FROM
      support_fact_finder
    WHERE geoid IN (‘4056100’,‘4053900’,‘4053100’,‘4066701’,‘4061301’,‘3027900’,‘3117202’,‘3030700’,‘3031701’,‘3012902’,‘3029500’)
      AND year = ‘Y2011-2015’
    GROUP BY variable
        ) aggregated
        INNER JOIN support_fact_finder_meta_update
        ON support_fact_finder_meta_update.variablename = aggregated.variable
      ) support_fact_finder
    INNER JOIN support_fact_finder_meta_update
    ON support_fact_finder_meta_update.variablename = support_fact_finder.variable
    LEFT OUTER JOIN (
      SELECT * FROM (
        SELECT sum(e) as base_sum, variable 
        FROM (
          SELECT *
          FROM support_fact_finder
          INNER JOIN support_fact_finder_meta_update
          ON support_fact_finder_meta_update.variablename = support_fact_finder.variable
          WHERE geoid IN (‘4056100’,‘4053900’,‘4053100’,‘4066701’,‘4061301’,‘3027900’,‘3117202’,‘3030700’,‘3031701’,‘3012902’,‘3029500’)
            AND year = ‘Y2011-2015’
        ) window_sum
        WHERE base = variable
        GROUP BY variable
      ) percentage
      INNER JOIN support_fact_finder_meta_update
      ON support_fact_finder_meta_update.variablename = percentage.variable
    ) enriched_percentage
    ON support_fact_finder.base = enriched_percentage.base
  ) t1
    INNER JOIN
      ( SELECT sum AS historic_comparison_sum, m AS historic_comparison_m, variable as t2_var
        FROM (
    SELECT
      regexp_replace(lower(support_fact_finder.variable), ‘[^A-Za-z0-9]’, ‘_’, ‘g’) as variable,
      regexp_replace(lower(support_fact_finder.profile), ‘[^A-Za-z0-9]’, ‘_’, ‘g’) as profile,
      regexp_replace(lower(support_fact_finder.category), ‘[^A-Za-z0-9]’, ‘_’, ‘g’) as category,
      sum,
      support_fact_finder.base,
      base_sum,
      m
    FROM
      ( 
        SELECT * FROM (
          
    SELECT
      SUM(e),
      SQRT(
        SUM(
          POWER(m, 2)
        )
      ) AS m,
      variable
    FROM
      support_fact_finder
    WHERE geoid IN (‘4056100’,‘4053900’,‘4053100’,‘4066701’,‘4061301’,‘3027900’,‘3117202’,‘3030700’,‘3031701’,‘3012902’,‘3029500’)
      AND year = ‘Y2006-2010’
    GROUP BY variable
        ) aggregated
        INNER JOIN support_fact_finder_meta_update
        ON support_fact_finder_meta_update.variablename = aggregated.variable
      ) support_fact_finder
    INNER JOIN support_fact_finder_meta_update
    ON support_fact_finder_meta_update.variablename = support_fact_finder.variable
    LEFT OUTER JOIN (
      SELECT * FROM (
        SELECT sum(e) as base_sum, variable 
        FROM (
          SELECT *
          FROM support_fact_finder
          INNER JOIN support_fact_finder_meta_update
          ON support_fact_finder_meta_update.variablename = support_fact_finder.variable
          WHERE geoid IN (‘4056100’,‘4053900’,‘4053100’,‘4066701’,‘4061301’,‘3027900’,‘3117202’,‘3030700’,‘3031701’,‘3012902’,‘3029500’)
            AND year = ‘Y2011-2015’
        ) window_sum
        WHERE base = variable
        GROUP BY variable
      ) percentage
      INNER JOIN support_fact_finder_meta_update
      ON support_fact_finder_meta_update.variablename = percentage.variable
    ) enriched_percentage
    ON support_fact_finder.base = enriched_percentage.base
  ) t ) t2
    ON t2.t2_var = t1.variable

This is a way to sum the non-base numbers, but methodologically might not work if we need to use the publish totals for denoms

SELECT *, 

sum(sum) filter (WHERE base != variable) OVER ( Partition by "year", variable ) as denom

FROM 
	(SELECT   Sum(e) filter (WHERE geoid IN ('4056100', 
                                          '4053900', 
                                          '4053100', 
                                          '4066701', 
                                          '4061301', 
                                          '3027900') ) AS sum, 
           variable, 
           sqrt( sum( power(m, 2) ) filter (WHERE geoid IN ('4056100', 
                                                            '4053900', 
                                                            '4053100', 
                                                            '4066701', 
                                                            '4061301', 
                                                            '3027900') ) )AS m, 
           sum(e) filter (WHERE geoid IN ('0'))                           AS comparison_sum, 
           year 
           /* Sqrt(Sum(Power(m, 2))) AS m, */ 
  FROM     support_fact_finder 

  GROUP BY variable, 
           year 
  ORDER BY variable DESC) aggregated
inner join support_fact_finder_meta_update
on support_fact_finder_meta_update.variablename = aggregated.variable