mlhtotal swap
    Updated 2022-11-23
    with price as (select timestamp::Date as day,
    token,
    token_contract,
    avg (price_usd) as USDPrice
    from flow.core.fact_prices
    where token != 'Blocto'
    group by 1, 2, 3
    )

    select substr(token_in_contract,20) || ' - ' || substr(token_out_contract,20) as pair,
    count (distinct tx_id) as swaps,
    count (distinct trader) as swappers,
    sum (case when token_in_contract = token_contract then token_in_amount
    when token_out_contract = token_contract then token_out_amount end) as Volume,
    sum (case when token_in_contract = token_contract then token_in_amount*usdprice
    when token_out_contract = token_contract then token_out_amount*usdprice end) as USD_Volume
    from flow.core.ez_swaps a join price b on (a.token_in_contract = b.token_contract or a.token_out_contract = b.token_contract)
    and a.block_timestamp::Date = b.day
    where token in ('USDC', 'Flow', 'Starly', 'Sportium', 'REVV', 'FUSD', 'Blocto Token', 'USDT')
    group by 1
    Run a query to Download Data