with

troveupdated as (

select

block_timestamp,

tx_hash,

topics,

event_index,

concat('0x',substr(topics[1],27,40)) as borrower,

regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}') as logs,

utils.udf_hex_to_int(logs[0])*pow(10,-18) as debt ,

utils.udf_hex_to_int(logs[1])*pow(10,-18) as coll ,

utils.udf_hex_to_int(logs[2]) as stake,

utils.udf_hex_to_int(logs[3]) as operation

from

ethereum.core.fact_event_logs

where

contract_address=lower('0xA39739EF8b0231DbFA0DcdA07d7e29faAbCf4bb2') -- Trove Manager

and topics[0]='0xc3770d654ed33aeea6bf11ac8ef05d02a6a04ed4686dd2f624d853bbec43cc8b' -- TroveUpdated

),

price_tb as (

select

date(hour) as day,

avg(price) as avg_price

from

ethereum.price.ez_prices_hourly

where

token_address='0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

group by

day

),

borrower_ls as (

select distinct borrower

from