bergDaily Swap Number of Ethereum
    Updated 2022-11-24
    with avg_price as (
    select
    hour::date as day, symbol, avg(price) price_avg
    from ethereum.core.fact_hourly_token_prices
    where symbol = '{{ symbol }}'
    and hour::date >= current_date - interval '3 weeks'
    group by symbol, day
    )

    select trunc(block_timestamp::date, 'day') "Date",
    count (distinct (tx_hash)) "Total Swaps Count",
    sum(iff(symbol_in = '{{ symbol }}', amount_in, null)) "Outflow Volume ({{ symbol }})",
    sum(iff(symbol_out = '{{ symbol }}', amount_out, null)) "Inflow Volume ({{ symbol }})",
    sum(iff(symbol_in = '{{ symbol }}', amount_in * price_avg, null)) "Outflow Volume ({{ symbol }}) (USD)",
    sum(iff(symbol_out = '{{ symbol }}', amount_out * price_avg, null)) "Inflow Volume ({{ symbol }}) (USD)",
    count(distinct(iff(symbol_in = '{{ symbol }}', tx_hash, null))) "Swaps from count",
    count(distinct(iff(symbol_out = '{{ symbol }}', tx_hash, null))) "Swaps to count",
    count(distinct(iff(symbol_in = '{{ symbol }}', origin_from_address, null))) "Swaps from users count",
    count(distinct(iff(symbol_out = '{{ symbol }}', origin_from_address, null))) "Swaps to users count",
    sum("Total Swaps Count") over (order by "Date" asc) as "Total Swaps Count (CUM)",
    sum("Outflow Volume ({{ symbol}})") over (order by "Date" asc) as "{{ symbol}} Outflow Volume (CUM)",
    sum("Inflow Volume ({{ symbol}})") over (order by "Date" asc) as "{{ symbol}} Inflow Volume (CUM)",
    sum("Swaps from count") over (order by "Date" asc) as "Swaps from count (CUM)",
    sum("Swaps to count") over (order by "Date" asc) as "Swaps to count (CUM)"
    from ethereum.core.ez_dex_swaps a
    join avg_price
    on block_timestamp::date = day
    where block_timestamp >= '2022-11-01'
    and '{{ symbol }}' in (symbol_in, symbol_out)
    group by 1
    order by 1 asc
    Run a query to Download Data