aba21. flow daily with price
Updated 2022-12-12
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 flow_price as (
select date(timestamp) as day,
avg (price_usd) as price
from flow.core.fact_prices
where source = 'coinmarketcap' and symbol = 'FLOW'
group by 1
)
, flow_token_price as (
select date(timestamp) as day,
token_contract,
symbol,
avg (price_usd) as USDPrice
from flow.core.fact_prices
group by 1,2,3
)
, flow_daily_data as (
select date_trunc (day,block_timestamp) as date,
count (distinct tx_id) as number_of_transactions,
number_of_transactions/1440 as tpm,
number_of_transactions/86400 as tps,
sum(number_of_transactions) over (order by date) as cumulative_tx_count,
sum(tpm) over (order by date) as cumulative_tpm,
case when date >= '2022-01-01' then '2022'
else 'before 2022'
end period
from flow.core.fact_transactions
where block_timestamp >= '2021-01-01' and block_timestamp::date != CURRENT_DATE
group by 1
order by 1
)
select date_trunc('day',block_timestamp) as date,
period,
sum(amount*t2.usdprice) as volume,
avg(amount*t2.usdprice) as avg_volume,
max(t3.price) as price,
max(t4.TPM) as TPM
Run a query to Download Data