txns.block_timestamp::date as date,

count(1) as n_txns,

count(distinct sender) as n_active,

sum(value) as tx_volume

from aptos.core.fact_transactions txns

join (

select

x.block_timestamp,

date_trunc('hour', x.block_timestamp) as hour_,

amount/pow(10,8) as amount_raw,

pr.price,

amount_raw * price as value

from aptos.core.fact_transfers x inner join

aptos.price.ez_hourly_token_prices pr on pr.hour = hour_

where x.block_timestamp > current_date - {{daysago}}

and pr.hour > current_date - {{daysago}}

and x.token_address = '0x1::aptos_coin::AptosCoin'

and pr.token_address = '0x1::aptos_coin::aptoscoin'

) val

on val.block_timestamp = txns.block_timestamp

where txns.block_timestamp > current_date - {{daysago}}

group by 1,2;