title: Postgres - simplify repeat of select subqueries based on dates
body: <p>Newbie with Postgres (9.6).</p>
  
  <p>I have 2 tables</p>
  
  <p>sales_account (list of customers)</p>
  
  <p>journal (transactions line invoices payments)</p>
  
  <p>I want to create another table called balances which has a summary of outstanding transactions based on periods (like 7 days, 14 days, 30 days, 60 days or older).</p>
  
  <p>I have written the query in long hand as sub-queries (it takes far too long to generate - there are over 15m journal records) but am looking for any hints as to how I can simplify the query.</p>
  
  <p>Thank you.</p>
  
  <pre><code>INSERT INTO balances (sales_account, 
      balance,
      period_1,
      period_2,
      period_3,
      period_4,
      period_5)       
  
  SELECT  sales_account.id,
  
  (SELECT coalesce(sum(journal.outstanding),0)
  FROM journal
  WHERE journal.sales_account = sales_account.id AND
  journal.status='Active'), -- overall balance
  
  (SELECT coalesce(sum(journal.outstanding),0)
  FROM journal
  WHERE journal.sales_account = sales_account.id AND
  journal.status='Active' AND
  journal.reversed_by is null AND
  journal.original_id is null AND
  journal.transaction_date &lt;= CURRENT_DATE - interval '7 days'), -- current
  
  (SELECT coalesce(sum(journal.outstanding),0)
  FROM journal 
  WHERE journal.sales_account = sales_account.id AND
  journal.status='Active' AND
  journal.reversed_by is null AND
  journal.original_id is null AND
  journal.transaction_date &lt;= CURRENT_DATE  - interval '14 days' AND
  journal.transaction_date &gt;= CURRENT_DATE - interval '8 days'), -- 8-14 days
  
  (SELECT coalesce(sum(journal.outstanding),0)
  FROM journal
  WHERE journal.sales_account = sales_account.id AND
  journal.status='Active' AND
  journal.transaction_date &lt;=CURRENT_DATE  - interval '30 days' AND
  journal.transaction_date &gt;= CURRENT_DATE - interval '15 days'), -- 22-30 days
  
  (SELECT coalesce(sum(journal.outstanding),0)
  FROM journal
  WHERE journal.sales_account = sales_account.id AND
  journal.status='Active' AND
  journal.transaction_date &gt;= CURRENT_DATE - interval '31 days' AND
  journal.transaction_date &lt;=CURRENT_DATE  - interval '60 days'), -- 31-60 days
  
  (SELECT coalesce(sum(journal.outstanding),0) 
  FROM journal
  WHERE journal.sales_account = sales_account.id AND
  journal.status='Active' AND
  journal.transaction_date &gt;= CURRENT_DATE - interval '61 days') -- 61+ days
  
  FROM sales_account;
  </code></pre>