SocioAnalyticaover time swap on Dexes [Ethereum] copyv
Updated 2024-03-04
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
32
33
34
35
36
›
⌄
with swaps as (
select
tx_hash,
block_timestamp
from ethereum.defi.ez_dex_swaps
where block_timestamp::date >= dateadd('day', -{{past_days}}, current_date)
)
,
fee_eth as (
select
date_trunc('day', block_timestamp) as date,
count(DISTINCT tx_hash) as n_swaps,
sum(tx_fee) as total_fee_eth,
avg(tx_fee) as avg_fee_eth,
median(tx_fee) as median_fee_eth
from ethereum.core.fact_transactions a
join swaps b using(tx_hash, block_timestamp)
where a.block_timestamp::date >= dateadd('day', -{{past_days}}, current_date)
group by 1
)
,
eth_price as (
SELECT 'Ethereum' as network,
TO_TIMESTAMP(value[0]::string) as date,
value[1] as price
FROM (
SELECT livequery.live.udf_api(
'https://api.coingecko.com/api/v3/coins/ethereum/market_chart?vs_currency=usd&days=30') as response
),LATERAL FLATTEN (input => response:data:prices)
)
, avg_price as (
select
date_trunc('day', date) as day,
avg(price) as usd_price
from eth_price
group by 1
QueryRunArchived: QueryRun has been archived