purifbero
    Updated 2024-12-14
    with buys as (select date_trunc('day',block_timestamp) as day, (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'),
    BERO_transfers as (select * from berachain.testnet.fact_event_logs
    where topics[0]='0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
    and contract_address=lower('0xB5A27c33bA2ADEcee8CdBE94cEF5576E2F364A8f')),
    bero_buys as (
    select day, sum(honey_amount) as honey_b, sum(bero_amount) as bero_b, avg(honey_amount/bero_amount) as price_b from (
    select day, tx_hash, honey_amount, sum(bero_amount) as bero_amount from (
    select b.day, 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
    ),

    sells as (
    select date_trunc('day',block_timestamp) as day,(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_sells as (
    select day, sum(bero_amount) as bero_s, sum(honey_amount) as honey_s, avg(honey_amount/bero_amount) as price_s from (
    select day,tx_hash, bero_amount,sum(honey_amount) as honey_amount from
    (select s.day,s.tx_hash, 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)
    group by 1,2,3)
    QueryRunArchived: QueryRun has been archived