amelia-leeeth
Updated 2022-12-05
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
›
⌄
SELECT
date_trunc ('day',block_timestamp) as DATE,
avg (price) as token,
count(distinct TX_HASH) as txn,
count(distinct FROM_ADDRESS) as users,
sum(TX_FEE * price) as USD,
txn / users as txn_per_user,
USD/txn as fees_per_txn,
sum (case when STATUS = 'SUCCESS' then 1 else null end) as success,
txn - success as failed,
success * 100 / txn as success_rate,
success_rate - 100 as Failed_rate
FROM ethereum.core.fact_transactions
left join (select date_trunc ('day',HOUR) as TIME, avg (PRICE) as price from ethereum.core.fact_hourly_token_prices WHERE SYMBOL = 'WETH' GROUP by 1) on block_timestamp::DATE = TIME
WHERE block_timestamp >= CURRENT_DATE -90
GROUP BY 1
Run a query to Download Data