(utils.udf_hex_to_int(substr(data,3,66))::int)/1e18 as amount, tx_hash

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'),

sells as (

select date_trunc('day',block_timestamp) as day, concat('0x',substr(TOPICS[1], 27,64)) as seller,

(utils.udf_hex_to_int(substr(data,3,66))::int)/1e18 as amount, tx_hash

from berachain.testnet.fact_event_logs

where contract_address=lower('0xB5A27c33bA2ADEcee8CdBE94cEF5576E2F364A8f')

and origin_to_address=lower('0xB5A27c33bA2ADEcee8CdBE94cEF5576E2F364A8f')

and ORIGIN_FUNCTION_SIGNATURE='0x26124288'

and TOPICS[0]='0xffc36e4a73526964b1bc25e2a44a90e979636de95ea6e59e01b6b83f2e20ae1e'

),

HONEY_transfers as (select * from berachain.testnet.fact_event_logs

where topics[0]='0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'

and contract_address=lower('0x0E4aaF1351de4c0264C5c7056Ef3777b41BD8e03')),

BERO_transfers as (select * from berachain.testnet.fact_event_logs

where topics[0]='0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'

and contract_address=lower('0xB5A27c33bA2ADEcee8CdBE94cEF5576E2F364A8f')),

daily_buys as (

select day, sum(bero_amount) as bero_b_vol, sum(honey_amount) as honey_b_vol, avg(honey_amount/bero_amount) as price_b from

(select day, honey_amount, sum(bero_amount) as bero_amount from

(select b.day, 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 and b.day=date_trunc('day',bero.block_timestamp))

group by 1,2)

group by 1

),

daily_sells as (

select day, sum(bero_amount) as bero_s_vol, sum(honey_amount) as honey_s_vol, avg(honey_amount/bero_amount) as price_s from

(select day, honey_amount, sum(bero_amount) as bero_amount from

(select s.day, amount as bero_amount, (utils.udf_hex_to_int(substr(h.data,3,66))::int)/1e18 as honey_amount from sells s

join HONEY_transfers h on h.tx_hash=s.tx_hash and s.day=date_trunc('day',h.block_timestamp))