Eman-RazTotal Swap Volume By Token Pairs
    Updated 2024-07-12
    with table1 as (with tab1 as (select date_trunc('hour',block_timestamp) as "Date", sum(token_in_amount) as "Swap Volume ($FLOW)",
    split_part(token_in_contract,'.',3) || '➡' || split_part(token_out_contract,'.',3) as "Token Pair"
    from flow.defi.ez_swaps
    where block_timestamp::date between '{{Start_Date}}' and '{{End_Date}}'
    and token_in_contract='A.1654653399040a61.FlowToken'
    group by 1,3),

    tab2 as (select hour, avg(price) as price
    from flow.price.ez_prices_hourly
    where symbol='FLOW' and hour::date between '{{Start_Date}}' and '{{End_Date}}'
    group by 1)

    select "Token Pair", sum("Swap Volume ($FLOW)") as "Swap Volume ($FLOW)",
    sum("Swap Volume ($FLOW)"*price) as "Swap Volume ($USD)"
    from tab1 left join tab2 on tab1."Date"=tab2.hour
    group by 1
    order by 2 desc),

    -----------------------------------------------------------------------------
    -----------------------------------------------------------------------------

    table2 as (with tab1 as (select date_trunc('hour',block_timestamp) as "Date", sum(token_out_amount) as "Swap Volume ($FLOW)",
    split_part(token_in_contract,'.',3) || '➡' || split_part(token_out_contract,'.',3) as "Token Pair"
    from flow.defi.ez_swaps
    where block_timestamp::date between '{{Start_Date}}' and '{{End_Date}}'
    and token_out_contract='A.1654653399040a61.FlowToken'
    group by 1,3),

    tab2 as (select hour, avg(price) as price
    from flow.price.ez_prices_hourly
    where symbol='FLOW' and hour::date between '{{Start_Date}}' and '{{End_Date}}'
    group by 1)

    select "Token Pair", sum("Swap Volume ($FLOW)") as "Swap Volume ($FLOW)",
    sum("Swap Volume ($FLOW)"*price) as "Swap Volume ($USD)"
    from tab1 left join tab2 on tab1."Date"=tab2.hour
    QueryRunArchived: QueryRun has been archived