hessTotal Swappers
    Updated 2024-11-27
    with base as ( select trunc(block_timestamp,'hour') as hourly,
    tx_hash,
    origin_from_address,
    pool_name,
    amount_in_usd,
    amount_out_usd,
    AMOUNT_IN,
    AMOUNT_Out,
    TOKEN_IN,
    TOKEN_out,
    symbol_in,
    symbol_out
    from avalanche.defi.ez_dex_swaps
    where platform in ('trader-joe-v2','trader-joe-v1')
    )
    ,
    min as ( select min(hourly) as min,
    origin_from_address
    from base
    group by 2)
    ,
    final as (select trunc(min,'week') as weekly,
    count(DISTINCT origin_from_address) as "New Swappers",
    sum("New Swappers") over (order by weekly asc) as "Cumulative New Swappers"
    from min
    where min::date >= '2024-01-01'
    group by 1 )

    select sum("New Swappers") as new_total,
    avg("New Swappers") as avg
    from final


    QueryRunArchived: QueryRun has been archived