SELECT

DATE_TRUNC('day', t.block_timestamp) as date,

COUNT(*) as number_of_transactions,

SUM(t.output_value) as daily_volume_btc,

AVG(t.fee) as average_fee_btc

FROM bitcoin.core.fact_transactions t

WHERE t.block_timestamp >= '2024-01-01'

AND t.block_timestamp < '2024-02-01'

AND t.is_coinbase = FALSE

GROUP BY 1

SELECT

t.date,

t.number_of_transactions,

t.daily_volume_btc,

t.daily_volume_btc * p.price as daily_volume_usd,

t.average_fee_btc,

t.average_fee_btc * p.price as average_fee_usd

FROM daily_tx t

LEFT JOIN bitcoin.price.ez_prices_hourly p

ON DATE_TRUNC('day', p.hour) = t.date

AND p.symbol = 'BTC'

ORDER BY t.date;