with prm as (
select
    b.inv_account_id, b.product_id
    , extract(year from b.balance_date) as yyyy
    , max(b.balance_date) max_balance_date
from avantrade.trx_ut_account_balances b
where b.balance_date between '01-JAN-2020' and '30-JAN-2020'
group by b.inv_account_id, b.product_id, extract(year from b.balance_date)
)
, sorted as (
    select
        p.*
        , b.current_unit as latest_unit
        , b.total_cost
        , row_number() over (partition by p.inv_account_id, p.product_id order by p.yyyy desc, p.max_balance_date desc) as seq
        , (case when b.current_unit > 0 then 'YES' else 'NO' end) as has_unit
    from
        prm p
        join trx_ut_account_balances b on
            p.inv_account_id = b.inv_account_id
            and p.product_id = b.product_id
            and p.max_balance_date = b.balance_date
)
, pick as (
    select s.*, min(s.seq) over (partition by s.inv_account_id, s.product_id order by s.yyyy desc, s.max_balance_date desc) as min_seq
    from sorted s
    where s.has_unit = 'YES'
)
select distinct c.cif, up.product_name, up.currency,p.yyyy as tahun_perolehan,
--p.max_balance_date,
p.latest_unit as jumlah_unit, p.total_cost as total_perolehan
from pick p
join mst_ut_products up on p.product_id = up.product_id
join mst_inv_holders h on p.inv_account_Id = h.inv_account_id
  --join MST_INVESTMENT_ACCOUNTS e on b.inv_account_id = e.inv_account_id
  join mst_customers c on h.customer_id = c.customer_id
where p.min_seq = p.seq
order by c.cif, up.product_name, up.currency;