title: Postgres, query the result of a subquery?
body: <p>I have data which is employee level which I aggregate daily, and then join back to a roster to get the name and manager name, etc. Is there a way to then aggregate this data (with the manager name) and join it back to the original data?</p>
  
  <p>Up until this point works fine. I make one row per system_id per date. I join the system ID with a mapping table to get the Employee ID. I then join that to a roster table to get some Employee Information (including their supervisor name).</p>
  
  <pre><code>SELECT *
  FROM 
      (SELECT DISTINCT (system_id)
      FROM schema.fact_table
      WHERE report_date &gt;= '2017-09-01') AS t1
  CROSS JOIN 
      (SELECT report_date :: DATE
      FROM generate_series('2017-09-01' :: DATE , '2017-09-10' :: DATE , INTERVAL '1 day') report_date ) AS t2
  LEFT JOIN 
      (SELECT *
      FROM schema.dimension_table_1
      WHERE system_id IS NOT NULL
              AND expiration_date &gt; '2017-09-01') AS t3
      ON t1.system_id = t3.system_id
          AND t2.report_date &gt;= t3.effective_date
          AND t2.report_date &lt;= t3.expiration_date
  LEFT JOIN 
      (SELECT *
      FROM schema.dimension_table_2
      WHERE expiration_date &gt; '2017-09-01') AS t4
      ON t3.employee_id = t4.employee_id
          AND report_date &gt;= t4.effective_date
          AND report_date &lt;= t4.expiration_date
  LEFT JOIN 
      (SELECT report_date AS report_date,
           system_id AS system_id,
           sum(stuff_count) AS stuff_count,
  
      FROM schema.fact_table_1
      WHERE report_date &gt;= '2017-09-01'
      GROUP BY  report_date, avaya_id, source_database ) AS t5
      ON t2.report_date = t5.report_date
          AND t1.system_id = t5.system_id
  </code></pre>
  
  <p>I then want to add some columns which have the aggregated daily performance for the employee's team.</p>
  
  <pre><code>LEFT JOIN 
      (SELECT report_date,
           supervisor_employee_id,
           sum(stuff_count) AS supervisor_stuff_count,
  
      WHERE report_date = '2017-09-01'
      GROUP BY  report_date, supervisor_employee_id) AS t7
      ON supervisor_employee_id = supervisor_employee_id
          AND report_date = report_date
  </code></pre>
  
  <p>How can I achieve this? I need to somehow give an alias to the entire upper section (daily aggregated employee level data with the supervisor name), and then aggregate that alias on the date + supervisor_id_column instead. I just couldn't get it to work right.</p>