BlockTrackerDEX Projects on Avalanche Stats. in the Last 30 Days
    Updated 2023-07-04
    -- forked from SocioCrypto / DeFi Projects on Avalanche Stats. in the Last 30 Days @ https://flipsidecrypto.xyz/SocioCrypto/q/0OsgUtioXnmg/defi-projects-on-avalanche-stats.-in-the-last-30-days

    with t1 as (
    SELECT
    platform,
    count(distinct origin_from_address) as n_users,
    count(DISTINCT tx_hash) as n_swaps,
    rank()over (order by n_users desc) as rank_n_users,
    rank()over (order by n_swaps desc) as rank_n_swaps
    FROM avalanche.core.ez_dex_swaps
    WHERE block_timestamp::date >= current_date - 30
    GROUP BY 1
    ),
    t0 as (
    SELECT
    platform,
    count(distinct origin_from_address) as n_users,
    count(DISTINCT tx_hash) as n_swaps,
    rank()over (order by n_users desc) as rank_n_users,
    rank()over (order by n_swaps desc) as rank_n_swaps
    FROM avalanche.core.ez_dex_swaps
    WHERE block_timestamp::date BETWEEN current_date - 60 AND current_date - 31
    GROUP BY 1
    )

    SELECT
    a.platform,
    a.n_users, a.n_swaps,
    concat(CASE when a.rank_n_users<b.rank_n_users then '🟢'
    when a.rank_n_users>b.rank_n_users then '🔻'
    when a.rank_n_users=b.rank_n_users then '⚪️'
    end,a.rank_n_users) as "rank by users",
    concat(CASE when a.rank_n_swaps<b.rank_n_swaps then '🟢'
    when a.rank_n_swaps>b.rank_n_swaps then '🔻'
    when a.rank_n_swaps=b.rank_n_swaps then '⚪️'
    end, a.rank_n_swaps) as "rank by swaps"
    Run a query to Download Data