with buys as (select date_trunc('week',BLOCK_TIMESTAMP) as week, tx_hash, (utils.udf_hex_to_int(substr(data,3,66))::int)/1e18 as amount

from berachain.testnet.fact_event_logs

where contract_address=lower('0xB5A27c33bA2ADEcee8CdBE94cEF5576E2F364A8f')

and origin_to_address=lower('0xB5A27c33bA2ADEcee8CdBE94cEF5576E2F364A8f')

and ORIGIN_FUNCTION_SIGNATURE='0xba002b70'

and TOPICS[0]='0x3590f0a355392f9c1de13cd72ea564d10019bb3605905ea543b424a360b9a88e'

),

BERO_transfers as (select * from berachain.testnet.fact_event_logs

where topics[0]='0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'

and contract_address=lower('0xB5A27c33bA2ADEcee8CdBE94cEF5576E2F364A8f'))

select week, avg(price_ob) as obero_p from (

select week, (honey_amount/bero_amount)-1.0 as price_ob from (

select week, tx_hash, honey_amount, sum(bero_amount) as bero_amount from (

select week, b.tx_hash, amount as honey_amount, (utils.udf_hex_to_int(substr(bero.data,3,66))::int)/1e18 as bero_amount from buys b

join BERO_transfers bero on b.tx_hash=bero.tx_hash

)

group by 1,2,3

)

)

group by 1

),

bera_price as (

with bera_sent as (select block_timestamp, tx_hash,(utils.udf_hex_to_int(substr(substr(data, 0,66),3,66))::int)/1e18 as bera_amount from berachain.testnet.fact_event_logs

where topics[0]='0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'

and contract_address=lower('0x7507c1dc16935B82698e4C63f2746A2fCf994dF8')

and concat('0x',substr(TOPICS[2], 27,64))=lower('0xab827b1cc3535a9e549ee387a6e9c3f02f481b49')

and ORIGIN_FUNCTION_SIGNATURE='0x0b2f6f3f'),

honey_received as (select block_timestamp, tx_hash,(utils.udf_hex_to_int(substr(substr(data, 0,66),3,66))::int)/1e18 as honey_amount from berachain.testnet.fact_event_logs

where topics[0]='0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'

and contract_address=lower('0x0E4aaF1351de4c0264C5c7056Ef3777b41BD8e03')

and concat('0x',substr(TOPICS[1], 27,64))=lower('0xab827b1cc3535a9e549ee387a6e9c3f02f481b49')

and ORIGIN_FUNCTION_SIGNATURE='0x0b2f6f3f')