select

token_address,

symbol,

decimals,

median(price) as price

from ethereum_core.fact_hourly_token_prices

where hour::date >= current_date - 1

and decimals is not null

group by token_address, symbol, decimals

),

kashi_token_address_list as (

select

event_inputs:cloneAddress::string as kashi_address_pair, '0x' || substring(event_inputs:data::string, 27, 40) as collateral, '0x'||substring(event_inputs:data::string, 91, 40) as asset

from ethereum_core.fact_event_logs

where contract_address = '0xf5bce5077908a1b7370b9ae04adc565ebd643966'

and event_name = 'LogDeploy'

),

generated_borrow as (

select

contract_address as kashi_address_pair,

(event_inputs:to::string) as user_address,

(event_inputs:amount) as amount,

event_name

from ethereum_core.fact_event_logs

where contract_address in (select kashi_address_pair from kashi_token_address_list)

and event_name in ('LogBorrow', 'LogRepay')

),

borrow_data_info as (

select

kashi_address_pair,

user_address,

sum(amount) as total_borrow

from generated_borrow

group by kashi_address_pair, user_address

),