flipside

    [LFJ] Daily LP Rewards Claiming (Before & After Avalanche 9000 Etna Upgrade)
    Ali3N[LFJ] Daily LP Rewards Claiming (Before & After Avalanche 9000 Etna Upgrade)
    Updated 2025-01-31
    with top_lp as (
    select
    sum(case when amount_in_usd is null then 0 else amount_in_usd end) as total,
    sum(case when token_in='0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' then amount_in
    when token_out='0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' then amount_out else 0
    end
    ) as total_eth,
    contract_address as pool_address,
    pool_name
    from avalanche.defi.ez_dex_swaps
    where
    1=1--date(block_timestamp)>= current_date() - 365
    and contract_address in (
    select pool_address from avalanche.defi.dim_dex_liquidity_pools where platform like '%trader%'
    --and date(creation_time)>='2023-01-01'
    )
    and (pool_name not like ('0x%') or pool_name not like ('%-0x%'))

    group by contract_address, pool_name
    order by total desc, total_eth desc
    limit 1000
    ),

    lps as (
    select
    pool_name, pool_address, tokens:token0 as token0, tokens:token1 as token1
    from
    avalanche.defi.dim_dex_liquidity_pools
    where
    1=1
    and pool_address in (select pool_address from top_lp where pool_name not like ('0x%'))
    ),

    TRANSFER AS (
    SELECT
    BLOCK_TIMESTAMP,
    Last run: 3 months ago
    DATE
    TIMESPAN
    TRANSACTIONS
    USERS
    VOLUME
    AVERAGE_VOLUME
    1
    		2025-01-31 00:00:00.000After Upgrade14158125981.406038333260.292161236
    2
    		2025-01-30 00:00:00.000After Upgrade15472168861.632830844479.720547815
    3
    		2025-01-29 00:00:00.000After Upgrade16660192643.953679607294.562620305
    4
    		2025-01-28 00:00:00.000After Upgrade19679750423.932119111991.312988268
    5
    		2025-01-27 00:00:00.000After Upgrade23485296890.501240593437.89159475
    6
    		2025-01-26 00:00:00.000After Upgrade14877944285.4081088122035.097862303
    7
    		2025-01-25 00:00:00.000After Upgrade17381191634.572085974429.673928444
    8
    		2025-01-24 00:00:00.000After Upgrade12761234307.802237476587.237599593
    9
    		2025-01-23 00:00:00.000After Upgrade19876404516.898274612855.215429756
    10
    		2025-01-22 00:00:00.000After Upgrade15466135084.064659623321.62872538
    11
    		2025-01-21 00:00:00.000After Upgrade22283314079.914486611594.848322891
    12
    		2025-01-20 00:00:00.000After Upgrade23196384750.580699332700.820729871
    13
    		2025-01-19 00:00:00.000After Upgrade22175741548.720599828784.707640846
    14
    		2025-01-18 00:00:00.000After Upgrade20892296510.159411559.453130964
    15
    		2025-01-17 00:00:00.000After Upgrade16468162901.045100422342.949568632
    16
    		2025-01-16 00:00:00.000After Upgrade17075225865.427611146424.559074457
    17
    		2025-01-15 00:00:00.000After Upgrade21495159009.223549471222.079921158
    18
    		2025-01-14 00:00:00.000After Upgrade1357245823.714879548114.559287199
    19
    		2025-01-13 00:00:00.000After Upgrade21281351916.976477805666.509425147
    20
    		2025-01-12 00:00:00.000After Upgrade11441100014.212545707277.817257071
    78
    6KB
    12s