purifDapp trifecta farmers
Updated 2024-11-26
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 plugins as (select concat('0x',substr(TOPICS[1], 27,64)) as plugin, concat('0x',substr(TOPICS[2], 27,64)) as gauge from berachain.testnet.fact_event_logs
where ORIGIN_TO_ADDRESS=lower('0x580ABF764405aA82dC96788b356435474c5956A7')
and ORIGIN_FUNCTION_SIGNATURE='0xd8867fc8'
and topics[0]='0xb920b936f556d443772609e0cc06a72c3bb67fc606de10dcdd51323d1ddbc7fc'
and concat('0x',substr(TOPICS[1], 27,64)) in (lower('0x80D7759Fa55f6a1F661D5FCBB3bC5164Dc63eb4D'),lower('0x62c310059A7d84805c675d2458234d3D137D9a1c'))
),
deposits as (
select concat('0x',substr(TOPICS[1], 27,64)) as address, count(distinct tx_hash) as txs from berachain.testnet.fact_event_logs
where ORIGIN_TO_ADDRESS in (select plugin from plugins)
and ORIGIN_FUNCTION_SIGNATURE='0x2f4f21e2'
--and block_timestamp <= timestamp '2024-09-25 16:20'
group by 1
),
withdraws as (
select concat('0x',substr(TOPICS[1], 27,64)) as address, count(distinct tx_hash) as txs from berachain.testnet.fact_event_logs
where ORIGIN_TO_ADDRESS in (select plugin from plugins)
and ORIGIN_FUNCTION_SIGNATURE='0x205c2878'
--and block_timestamp <= timestamp '2024-09-25 16:20'
group by 1
),
farm_reward_claim as (
select concat('0x',substr(TOPICS[1], 27,64)) as address,count(distinct tx_hash) as txs from berachain.testnet.fact_event_logs
where ORIGIN_TO_ADDRESS in (select gauge from plugins)
and ORIGIN_FUNCTION_SIGNATURE='0xc00007b0'
--and block_timestamp <= timestamp '2024-09-25 16:20'
group by 1
),
users as (select address, sum(txs) as txs from (
select address, txs from deposits
union all
select address, txs from withdraws
union all
select address, txs from farm_reward_claim
)
where address not in (select plugin from plugins union select gauge from plugins)
and lower(address) not in (lower('0xac94700e79a510b885014407918805fa0fcaa6e8'),lower('0xce67e15cbcb3486b29ad44486c5b5d32f361fddc'),lower('0x34D023ACa5A227789B45A62D377b5B18A680BE01'),lower('0x4e51f68940fac327b2f018b6b79109031031fe8b'))
QueryRunArchived: QueryRun has been archived