purifYeet testnet v2 - Staking apr
Updated 2025-01-08
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
›
⌄
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