Salehblast-all_count_daily
Updated 2024-11-11
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 eth_day as (
select
TO_TIMESTAMP(value[0]::string)::date as price_date
,'ETH' as token
, avg(value[1]) as avg_price_day
from (
select livequery.live.udf_api(
'https://api.coingecko.com/api/v3/coins/ethereum/market_chart?vs_currency=usd&days=365&interval=daily&precision=3') as resp
)
,LATERAL FLATTEN (input => resp:data:prices)
group by 1,2
)
,lst_new_users_raw as (
select
from_address
,min(block_timestamp)::date as min_date
from blast.core.fact_transactions
group by 1
)
,lst_new_users as(
select
min_date::date as wallet_date
,count(DISTINCT from_address) as "New Wallets"
from lst_new_users_raw
group by 1
)
select
block_timestamp::date as date
,avg_price_day as "ETH Price"
,"New Wallets"
,count(DISTINCT tx_hash) as TXn
,count(DISTINCT from_address) as Wallets
,sum(value) as "ETH volume"
,count(DISTINCT block_timestamp::date) as "Activity Days"
,TXn/"Activity Days"/24/60/60 as "TPS"
,sum(TX_FEE) as "TX FEE(ETH)"
QueryRunArchived: QueryRun has been archived