Moefffllllwww8
    Updated 2023-01-11
    with prices as (select date(RECORDED_HOUR) date ,
    avg(CLOSE) as price
    from
    crosschain.core.fact_hourly_prices
    where
    ID ilike 'flow'
    and
    RECORDED_HOUR between '2022-12-01' and CURRENT_DATE -1
    group by 1

    )

    select
    'Flow out' as type ,
    date_trunc('day', BLOCK_TIMESTAMP)::date AS DAY,
    monthname(BLOCK_TIMESTAMP) AS Month,
    -1*sum(TOKEN_OUT_AMOUNT*price) AS Volume,
    -1*avg(TOKEN_OUT_AMOUNT*price) AS avg_Volume,
    -1*count(distinct TRADER) as TRADERs
    from flow.core.ez_swaps s , prices p
    where TOKEN_OUT_CONTRACT ilike '%Flow%'
    and block_timestamp::date = date
    group by 2,3

    union all

    select
    'Flow in' as type ,
    date_trunc('day', BLOCK_TIMESTAMP)::date AS DAY,
    monthname(BLOCK_TIMESTAMP) AS Month,
    sum(TOKEN_IN_AMOUNT*price) AS Volume,
    avg(TOKEN_IN_AMOUNT*price) AS avg_Volume,
    count(distinct TRADER) as TRADERs
    from flow.core.ez_swaps s , prices p
    where TOKEN_IN_CONTRACT ilike '%Flow%'
    Run a query to Download Data