with base as (select block_timestamp, dim_block_id,

owner,

round(collateralization_ratio/10000, 2) as CR,

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

collateral_asset,

target_asset as borrow_asset

from thorchain.defi.fact_loan_open_events),

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

collateral_asset,

from_amount,

from_amount_USD,

borrow_asset,

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,

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

from thorchain.defi.fact_swaps_events

where memo like 'loan%')

and from_asset in ('BTC.BTC', 'ETH.ETH')),

non_rune_borrows as (select a.block_timestamp, owner, cr, LTV,

collateral_asset,

a.from_amount,