purifBullas bribes, revenue and obero emissions
Updated 2024-12-29
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
›
⌄
-- bribe contract 0x313a6eD90492c801E68c34bace8809D1B8AecD40, 0x0c7fC9Cb0C9735108609DE70a7cF8F97249778B5
-- gauge contract 0xF3Ef842E1572E13d8A59Aa51cC7b7c51F37e1456, 0xd39c967FCA12af8bE3E5eD803c8751f8c0508be0
-- treasury old : 0x19858F6c29eA886853dc97D1a68ABf8d4Cb07712 new : 0x90bd6df5E84b0a97387Edf5345d1784988084d89
-- queue plugin 0x5aD441790c3114e0AB27816abdB0c9693cd96399, 0xd2fe6dc3fae1d60a20AeB4E8509FDF4740393150
with
obero_price as (
with buys as (select date_trunc('day',block_timestamp) as day, tx_hash, (utils.udf_hex_to_int(substr(data,3,66))::int)/1e18 as amount
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'
order by block_timestamp desc
),
BERO_transfers as (select * from berachain.testnet.fact_event_logs
where topics[0]='0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
and contract_address=lower('0xB5A27c33bA2ADEcee8CdBE94cEF5576E2F364A8f'))
select day,avg(honey_amount/bero_amount) as price_b, avg(honey_amount/bero_amount)-1.0 as price_ob 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
order by day desc limit 1
),
bera_price as (
with bera_sent as (select block_timestamp, tx_hash,(utils.udf_hex_to_int(substr(substr(data, 0,66),3,66))::int)/1e18 as bera_amount from berachain.testnet.fact_event_logs
where topics[0]='0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
and contract_address=lower('0x7507c1dc16935B82698e4C63f2746A2fCf994dF8')
and concat('0x',substr(TOPICS[2], 27,64))=lower('0xab827b1cc3535a9e549ee387a6e9c3f02f481b49')
and ORIGIN_FUNCTION_SIGNATURE='0x0b2f6f3f'),
QueryRunArchived: QueryRun has been archived