JonasoPuffer Operators : delegators (2)
Updated 2024-10-30Copy Reference Fork
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
›
⌄
with
L as(
select to_varchar(value:AA) as cate, to_varchar(value:CC) as name, to_varchar(value:DD) as token, to_varchar(value:CT) as CONTRACT
from (select live.udf_api('https://flipsidecrypto.xyz/api/v1/queries/868a10dd-8c47-4df6-a5d5-d246600e5411/data/latest') as db ) , LATERAL FLATTEN (input => db:data)
where to_varchar(value:AA) in ('Operator','Strategy') ),
A as(
select block_timestamp as time, event_name as event, tx_hash, b.name as operator, c.name as asset, c.token as token,
case when event_name like '%Increased%' then 1 else -1 end * decoded_log['shares']/1e18 as amount,
decoded_log['staker'] as staker,
decoded_log
from ethereum.core.fact_decoded_event_logs as a
join L as b on a.decoded_log['operator'] = b.contract and b.cate = 'Operator'
join L as c on a.decoded_log['strategy'] = c.contract and c.cate = 'Strategy'
and decoded_log['shares'] > 0 ),
B as(
select date_trunc('week',time) as time, case when token in ('EIGEN','ETH') then token else 'LSTs' end as token,
count(distinct staker) as staker
from A
group by 1,2 )
select time, 'EIGEN new delegators' as EIGENs, 'ETH new delegators' as ETHs, 'LSTs new delegators' as LSTs,
max(case when token = 'EIGEN' then staker end) as EIGEN,
max(case when token = 'ETH' then staker end) as ETH,
max(case when token = 'LSTs' then staker end) as LST
from B
group by 1,2,3,4
order by 1 desc
QueryRunArchived: QueryRun has been archived