bergDaily Swap Number of Ethereum
Updated 2022-11-24Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
›
⌄
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