select

timestamp::date as day,

token_contract,

symbol,

avg (price_usd) as USDPrice

from flow.core.fact_prices

group by 1,2,3

)

,

flow_tpm as

select

date_trunc (day,block_timestamp) as date,

count (distinct tx_id) as TX_Count,

TX_Count/1440 as TPM,

TX_Count/86400 as TPS

from flow.core.fact_transactions

where block_timestamp >= '2022-01-01' and block_timestamp::date != CURRENT_DATE

group by 1

,

flow_volume as

select

date_trunc('day',block_timestamp) as date,

sum(amount*usdprice) as Volume,

avg(amount*usdprice) as Average_Volume,

usdprice,

from flow.core.ez_token_transfers t1 join flow_price t2 on t1.block_Timestamp::Date = t2.day and t1.token_contract = t2.token_contract

where tx_succeeded = 'TRUE'

and block_Timestamp >= '2022-01-01'

and block_timestamp::date != CURRENT_DATE

group by 1