purifShare of reward vaults
Updated 2024-09-06
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
33
34
35
36
›
⌄
with pools(pool, token0, t0_name,t0_decimals, token1, t1_name, t1_decimals) as (
select pool, token0, t0_name,t0_decimals, token1, t1_name, t1_decimals
from (
VALUES
('0x8a960A6e5f224D0a88BaD10463bDAD161b68C144', '0x0e4aaf1351de4c0264c5c7056ef3777b41bd8e03','HONEY',1e18,'0x7507c1dc16935b82698e4c63f2746a2fcf994df8','WBERA',1e18),
('0x4302b57ba521cf2137e8af85990ab1ca2e241126', '0x4302b57ba521cf2137e8af85990ab1ca2e241126','IBGT',1e18,'0x7507c1dc16935b82698e4c63f2746a2fcf994df8','WBERA',1e18)
) as X(pool, token0, t0_name,t0_decimals, token1, t1_name, t1_decimals)
),
iBGT as (
select (ibgt_p.amount1/ibgt_p.amount0) * (bera_p.amount0/bera_p.amount1) as ibgt_price from (
select ABS(decoded_log ['amount0'] / 1e18) as amount0,
ABS(decoded_log ['amount1'] / 1e18) as amount1
from berachain.testnet.fact_decoded_event_logs l
join pools p on lower(p.pool)=lower(l.contract_address)
where (decoded_log ['recipient']=lower('0x496e305c03909ae382974caca4c580e1bf32afbe') AND decoded_log ['sender']=lower('0x496e305c03909ae382974caca4c580e1bf32afbe'))
and event_name='Swap'
and (lower(token0)=lower('0x4302b57ba521cf2137e8af85990ab1ca2e241126') and lower(token1)=lower('0x7507c1dc16935b82698e4c63f2746a2fcf994df8'))
order by l.block_timestamp DESC
limit 1
) ibgt_p,
(
select ABS(decoded_log ['amount0'] / 1e18) as amount0,
ABS(decoded_log ['amount1'] / 1e18) as amount1
from berachain.testnet.fact_decoded_event_logs l
join pools p on lower(p.pool)=lower(l.contract_address)
where (decoded_log ['recipient']=lower('0x496e305c03909ae382974caca4c580e1bf32afbe') AND decoded_log ['sender']=lower('0x496e305c03909ae382974caca4c580e1bf32afbe'))
and event_name='Swap'
and (lower(token0)=lower('0x0e4aaf1351de4c0264c5c7056ef3777b41bd8e03') and lower(token1)=lower('0x7507c1dc16935b82698e4c63f2746a2fcf994df8'))
order by l.block_timestamp DESC
limit 1
) bera_p
),
vaults as (select concat('0x',substr(TOPICS[1], 27,64)) as staking_token, concat('0x',substr(TOPICS[2], 27,64)) as vault_address from berachain.testnet.fact_event_logs
where topics[0]='0x5d9c31ffa0fecffd7cf379989a3c7af252f0335e0d2a1320b55245912c781f53'
and contract_address=lower('0x2b6e40f65d82a0cb98795bc7587a71bfa49fbb2b')),
total_staked as (
QueryRunArchived: QueryRun has been archived