JonasoPuffer Operators : delegators (2)
    Updated 2024-10-30
    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