SELECT
avg(Volume * price) as "Avg TXs Volume (USD)"
from
(
select
date_trunc('day', block_timestamp) as date,
SUM(output_value) AS Volume
FROM
bitcoin.core.fact_transactions
WHERE
block_timestamp >= current_date - 30
AND output_value is not null
group by
1
) a
left join (
select
date_trunc('day', hour) as date,
avg(price) as price
from
bitcoin.price.ez_prices_hourly
where
hour >= current_date - 30
group by
1
) b on a.date = b.date