cloudr3nDEXBoard Top DEX by Volume & User Count (30D)
    Updated 2023-09-16
    -- top dex % vol, top vol, top dex % user, user count
    with dexSwap as (
    SELECT
    date(block_timestamp) as day,
    case when amount_in_usd is null then amount_out_usd
    when amount_out_usd is null then amount_in_usd
    when amount_in_usd>=amount_out_usd then amount_in_usd
    when amount_out_usd>amount_in_usd then amount_out_usd end as amount_usd,
    platform, origin_from_address
    FROM
    avalanche.core.ez_dex_swaps
    WHERE
    current_date() - date(block_timestamp)<=30
    )

    SELECT
    sum(amount_usd) as platformVol,
    platform,
    sum(platformVol) over (order by platformVol asc) as totalVol,
    count(distinct origin_from_address) as userCount,
    sum(userCount) over (order by platformVol asc) as totalUser,
    100*platformVol/totalVol as pVol,
    100*userCount/totalUser as pUser
    from dexSwap

    group by platform
    order by platformVol desc
    limit 1
    Run a query to Download Data