I built this long query that essentially self joins a bunch of tables but at different time frames to pull in balances at 3 month intervals and places each balance / time period combination in its own column.

The query is something like this:

```
select
   extract(year from a.mnth_end_dt) * 100 + extract(month from a.mnth_end_dt) as YM
  ,case when s.segmt_id = 'S4' then 'A'
        else 'R'
        end as Segment
  ,case when a.act_open_dt between add_months(a.mnth_end_dt + interval '1' day, -1) and a.mnth_end_dt then 'New'
        else 'Old'
        end as ACT_STAT  
  ,count(distinct a.act_id) as ACTs
  ,coalesce(sum(b0.b1),0) as INT0_Tot_BD_Assets
  ,coalesce(sum(bk0.bal),0) as INT0_Tot_BNK_Cash
  ,INT0_Tot_BD_Assets + INT0_Tot_BNK_Cash as INT0_Tot_Assets
  ,coalesce(sum(b0.b2),0) as INT0_BS
  ,coalesce(sum(b0.b3),0) as INT0_BDC 
  ,INT0_BS + INT0_BDC + INT0_Tot_BNK_Cash as INT0_Tot_Cash
  ,cast(cast(INT0_Tot_Cash as decimal(20,4)) / cast(INT0_Tot_Assets as decimal(20,4)) as decimal(20,4)) * 100 || '%' as INT0_Cash_Pct  
  ,coalesce(sum(b1.b1),0) as INT1_Tot_BD_Assets
  ,coalesce(sum(bk1.bal),0) as INT1_Tot_BNK_Cash
  ,INT1_Tot_BD_Assets + INT1_Tot_BNK_Cash as INT1_Tot_Assets
  ,coalesce(sum(b1.b2),0) as INT1_BS
  ,coalesce(sum(b1.b3),0) as INT1_BDC  
  ,INT1_BS + INT1_BDC + INT1_Tot_BNK_Cash as INT1_Tot_Cash
  ,case when INT1_Tot_Cash = 0 then 0 || '%' 
        else cast(cast(INT1_Tot_Cash as decimal(20,4)) / cast(INT1_Tot_Assets as decimal(20,4)) as decimal(20,4)) * 100 || '%' 
        end as INT1_Cash_Pct  
  ,coalesce(sum(b2.b1),0) as INT2_Tot_BD_Assets
  ,coalesce(sum(bk2.bal),0) as INT2_Tot_BNK_Cash
  ,INT2_Tot_BD_Assets + INT2_Tot_BNK_Cash as INT2_Tot_Assets
  ,coalesce(sum(b2.b2),0) as INT2_BS
  ,coalesce(sum(b2.b3),0) as INT2_BDC  
  ,INT2_BS + INT2_BDC + INT2_Tot_BNK_Cash as INT2_Tot_Cash
  ,case when INT2_Tot_Cash = 0 then 0 || '%' 
        else cast(cast(INT2_Tot_Cash as decimal(20,4)) / cast(INT2_Tot_Assets as decimal(20,4)) as decimal(20,4)) * 100 || '%' 
        end as INT2_Cash_Pct  

  from or.accts a

    inner join ir.segments s
      on a.clnt_cd = c.clnt_cd --segmt_lvl_nm# (6 highest level: AS/IS/Other Business)                
      and c.segmt_id in ('S4','S5')

    left join ir.acct_bal_mthly b0
      on a.acct_id = b0.acct_id      
      and b0.mnth_end_yyyymm = cast(extract(year from a.mnth_end_dt) * 100 + (extract(month from a.mnth_end_dt)) as integer)

    left join ir.acct_bal_mthly b1
      on a.acct_id = b1.acct_id      
      and b1.mnth_end_yyyymm = cast(extract(year from add_months(a.mnth_end_dt,3)) * 100 + (extract(month from add_months(a.mnth_end_dt,3))) as integer)

    left join ir.acct_bal_mthly_1 b2
      on a.acct_id = b2.acct_id      
      and b2.mnth_end_yyyymm = cast(extract(year from add_months(a.mnth_end_dt,6)) * 100 + (extract(month from add_months(a.mnth_end_dt,6))) as integer)    

    left join br.bank_bal_mnth bk0
      on a.s_acct_id = bk0.bk_acct_nbr      
      and bk0.busn_dt = a.mnth_end_dt

    left join br.bank_bal_mnth bk1
      on a.s_acct_id = bk1.bk_acct_nbr      
      and bk1.busn_dt = (add_months(a.mnth_end_dt + interval '1' day, 3) - interval '1' day)

    left join br.bank_bal_mnth bk2
      on a.s_acct_id = bk2.bk_acct_nbr      
      and bk2.busn_dt = (add_months(a.mnth_end_dt + interval '1' day, 6) - interval '1' day)

  where a.mnth_end_dt between '2014-01-31' and '2018-04-30'        
    and a.acct_clos_dt is null    

group by 1,2,3
order by 1,2,3

```

This query gets the job done, but I'm wondering if there is a better / more efficient way to do this? I feel that this query would unnecessarily tax the server (the actual query is much larger, I shortened it here for this question).

Would appreciate any ideas. Thanks!