with base as (select block_timestamp, dim_block_id,

owner,

round(collateralization_ratio/10000, 2) as CR,

round((1/CR) * 100, 2) as LTV,

split(collateral_asset, '.')[1] as collateral,

split(split(target_asset, '.')[1], '-')[0] as borrow,

collateral_asset,

from thorchain.defi.fact_loan_open_events

where block_timestamp >= '2023-08-01'),

base2 as (select a.block_timestamp, a.dim_block_id, owner, cr, LTV,

collateral_asset,

collateral,

from_amount,

from_amount_USD,

target_asset,

borrow,

case

when affiliate_address = 'thor160yye65pf9rzwrgqmtgav69n6zlsyfpgm9a7xk' then 'THORSwap'

when affiliate_address = 'wr' then 'THORWallet'

when affiliate_address = 'tl' then 'TS Ledger'

when affiliate_address = 'cb' then 'Team CoinBot'

when affiliate_address is null then 'No Affiliate'

else affiliate_address

end as affiliates,

tx_id,

max(b.streaming_quantity) as collateral_SS_count

from base a

left join thorchain.defi.fact_swaps b

on a.block_timestamp = b.block_timestamp and a.owner = b.from_address

where tx_id in (select tx_id