purifYeet testnet v2 - Staking apr
    Updated 2025-01-08
    with staking as (
    select sum(amount) as staked from (
    select block_timestamp, (utils.udf_hex_to_int(substr(data,3,66))::int)/1e18 as amount
    from berachain.testnet.fact_event_logs
    where ORIGIN_TO_ADDRESS=LOWER('0xd98c56EBbb46aA3a85Cc488c6b24E0d486624D9F')
    and ORIGIN_FUNCTION_SIGNATURE='0xa694fc3a'
    and topics[0]='0xebedb8b3c678666e7f36970bc8f57abf6d8fa2e828c0da91ea5b75bf68ed101a')
    ),
    unstaking as (
    select sum(amount) as unstaked from (
    select block_timestamp, (utils.udf_hex_to_int(substr(data,3,64))::int)/1e18 as amount
    from berachain.testnet.fact_event_logs
    where ORIGIN_TO_ADDRESS=LOWER('0xd98c56EBbb46aA3a85Cc488c6b24E0d486624D9F')
    and ORIGIN_FUNCTION_SIGNATURE='0x2e17de78'
    and topics[0]='0xf960dbf9e5d0682f7a298ed974e33a28b4464914b7a2bfac12ae419a9afeb280'
    )
    ),
    staked_total as (
    select staked-unstaked as staked from staking, unstaking
    ),
    claim_data as (
    select sum(amount) as claimed from (
    select (utils.udf_hex_to_int(substr(data,0,66))::int)/1e18 as amount
    from berachain.testnet.fact_event_logs
    where ORIGIN_TO_ADDRESS=LOWER('0xd98c56EBbb46aA3a85Cc488c6b24E0d486624D9F')
    and topics[0]='0xd8138f8a3f377c5259ca548e70e4c2de94f129f5a11036a15b69513cba2b426a'
    and ORIGIN_FUNCTION_SIGNATURE='0x4e71d92d')
    )

    select roi as net_return, (365/duration)*roi as annualized_apr from (
    select (claimed_usd_value/staked_usd_value)*100 as roi, datediff('day',date('2024-06-12'), GETDATE()) as duration from (
    select claimed*37 as claimed_usd_value, staked*0.48 as staked_usd_value from claim_data, staked_total))
    QueryRunArchived: QueryRun has been archived