with wbtc_price as (

select base_token,quote_token, avg(price) as price, avg(hourly_change) as hourly_change_past_24h from (

select hour, base_token,quote_token, price, previous_price, ((price-previous_price)/price) as hourly_change from (

select hour, base_token, quote_token, price, coalesce(lag(price) over (order by hour), price) as previous_price

from (

select date_trunc('hour',block_timestamp) as hour, base_token, quote_token,

avg(qty/amount) as price

from (

select block_timestamp, concat('0x',substr(TOPICS[1], 27,64)) as base_token, concat('0x',substr(TOPICS[2], 27,64)) as quote_token,

(utils.udf_hex_to_int(substr(data,67,64))::int) as isBuy,

(utils.udf_hex_to_int(substr(data,131,64))::int) as inBaseQty,

(utils.udf_hex_to_int(substr(data,195,64))::int)/1e18 as qty,amount,

from berachain.testnet.fact_event_logs l

join (

select tx_hash, (utils.udf_hex_to_int(substr(data,3,64))::int)/1e8 as amount

from berachain.testnet.fact_event_logs

where contract_address=lower('0x2577D24a26f8FA19c1058a8b0106E2c7303454a4')

and topics[0]='0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'

and concat('0x',substr(TOPICS[2], 27,64)) NOT IN (lower('0x21e2c0afd058a89fcf7caf3aea3cb84ae977b73d'), lower('0xAB827b1Cc3535A9e549EE387A6E9C3F02F481B49'))

and ( concat('0x',substr(TOPICS[1], 27,64)) = ORIGIN_FROM_ADDRESS OR concat('0x',substr(TOPICS[2], 27,64)) = ORIGIN_FROM_ADDRESS )

) t on t.tx_hash=l.tx_hash

where CONTRACT_ADDRESS=lower('0xAB827b1Cc3535A9e549EE387A6E9C3F02F481B49')

and topics[0]='0x5d7a6c346454f5c536b7f52655e780f6db27b15b489f80f2dbb288c9e4f366bd'

and base_token=lower('0x0e4aaf1351de4c0264c5c7056ef3777b41bd8e03') and quote_token=lower('0x2577d24a26f8fa19c1058a8b0106e2c7303454a4')

and isBuy=1 and inBaseQty=1 and qty>0 and amount>0

and block_timestamp >= CURRENT_TIMESTAMP() - interval '24 hours'

order by block_timestamp desc

)

group by 1,2,3

)

)

)

group by 1,2

),

bera_price as (