MLDZMNswp3
    Updated 2023-04-03
    with tb5 as (
    SELECT
    trunc(timestamp,'day') as day,
    TOKEN_CONTRACT,
    avg(price_usd) as price_token
    from near.core.fact_prices
    group by 1,2
    ),

    t1 as (select
    *,
    AMOUNT_IN*a.price_token as volume_usd_in,
    AMOUNT_OUT*b.price_token as volume_usd_out

    from near.core.ez_dex_swaps s
    left join tb5 a on s.TOKEN_IN_CONTRACT=a.TOKEN_CONTRACT and s.BLOCK_TIMESTAMP::date=a.day
    left join tb5 b on s.TOKEN_OUT_CONTRACT=b.TOKEN_CONTRACT and s.BLOCK_TIMESTAMP::date=b.day
    where BLOCK_TIMESTAMP>='2023-01-01'
    and volume_usd_out >=20000
    and BLOCK_TIMESTAMP>='2023-01-01'
    and volume_usd_out<1e6
    )

    select
    BLOCK_TIMESTAMP::date as date,
    count(distinct trader) as no_whales,
    count(distinct tx_hash) as no_swaps,
    sum(AMOUNT_OUT*b.price_token) as total_swap_volume,
    avg(AMOUNT_OUT*b.price_token) as avg_swap_volume,
    sum(total_swap_volume) over (order by date) as total_volume
    from near.core.ez_dex_swaps s
    left join tb5 a on s.TOKEN_IN_CONTRACT=a.TOKEN_CONTRACT and s.BLOCK_TIMESTAMP::date=a.day
    left join tb5 b on s.TOKEN_OUT_CONTRACT=b.TOKEN_CONTRACT and s.BLOCK_TIMESTAMP::date=b.day
    where BLOCK_TIMESTAMP>='2023-01-01'
    and tx_hash in (select tx_hash from t1)
    Run a query to Download Data