select

hour

, token_address

, avg(price) as price

from

optimism.core.fact_hourly_token_prices p

where

hour >= '2023-01-01'::date

group by 1,2

)

SELECT

'Borrow' as action

, tr.origin_from_address as user

-- , ct.decimals

, ct.symbol as "Borrowed Token"

, tr.raw_amount/power(10,ct.decimals) as amount

, amount * p.price as amount_usd

, case

when tr.from_address = lower('0x8cd6b19a07d754bf36adeee79edf4f2134a8f571') then 'soOP'

when tr.from_address = lower('0x5Ff29E4470799b982408130EFAaBdeeAE7f66a10') then 'soUSDT'

when tr.from_address = lower('0xf7B5965f5C117Eb1B5450187c9DcFccc3C317e8E') then 'soWETH'

when tr.from_address = lower('0x5569b83de187375d43FBd747598bfe64fC8f6436') then 'soDAI'

when tr.from_address = lower('0xEC8FEa79026FfEd168cCf5C627c7f486D77b765F') then 'soUSDC'

when tr.from_address = lower('0x33865E09A572d4F1CC4d75Afc9ABcc5D3d4d867D') then 'soWBTC'

when tr.from_address = lower('0xd14451E0Fa44B18f08aeB1E4a4d092B823CaCa68') then 'soSUSD'

end as "SONEE Contract"

, tr.tx_hash

, tr.block_timestamp

from

optimism.core.fact_token_transfers TR

left outer join optimism.core.dim_contracts ct on tr.contract_address = ct.address

left outer join token_price p on tr.contract_address = p.token_address

WHERE