select

date_trunc('minute', block_timestamp) as mnt,

count(distinct tx_id) as tx_cnt,

avg(tx_cnt) over (order by mnt asc) as avg_tx_cnt

from

flow.core.fact_transactions

where

TX_SUCCEEDED and block_timestamp >= '2022-01-01'

group by 1

), prices as (

select

date(timestamp) as day,

token_contract,

symbol,

avg(price_usd) as priceusd

from

flow.core.fact_prices

group by

1,2,3

select dayy, daily_valume, daily_tpm, moving_average_daily_tpm, moving_average_daily_valume from

(select

date_trunc('day', block_timestamp) as dayy,

'Volume (USD)' as type,

sum(AMOUNT*priceusd) as daily_valume,

avg(daily_valume) over (order by dayy asc rows between 7 preceding and current row) as moving_average_daily_valume

from

flow.core.ez_token_transfers a join prices b on b.day=date_trunc('day', block_timestamp) and a.token_contract=b.token_contract

where

TX_SUCCEEDED and block_timestamp >= '2022-01-01'

group by 1

) a join

(select

date_trunc('day', mnt) as dayyy,

'TPM' as type,