denze-e7j2NU2023-12-21 08:24 AM
    Updated 2023-12-20

    -- top 10 more popular erc20 on EVM: mainnet, polygon, base, optimism, arbitrum, gnosis chain
    -- popular = largest # holders with value > $1

    with

    erc20_in as (
    select
    blockchain
    , token_in as contract_address
    , symbol_in as symbol
    , trader as address
    , sum(amount_in) AS value
    , sum(amount_in_usd) as value_usd
    from crosschain.defi.ez_dex_swaps
    where true
    -- and block_timestamp > current_timestamp - interval '1 day'
    and blockchain in (
    'ethereum'
    , 'polygon'
    , 'base'
    , 'optimism'
    , 'arbitrum'
    , 'gnosis'
    )
    and amount_in is not null
    group by 1,2,3,4
    )

    , erc20_out as (
    select
    blockchain
    , token_out as contract_address
    , symbol_out as symbol
    , trader as address
    , -1 * sum(amount_out) AS value
    QueryRunArchived: QueryRun has been archived