SELECT

input_value,

block_timestamp,

DATE_TRUNC('hour', block_timestamp) AS rounded_hour

FROM

bitcoin.core.fact_transactions

WHERE

block_timestamp BETWEEN '2022-01-01' AND '2024-12-31 23:59:59'

),

price_data AS (

SELECT

symbol,

price,

hour

FROM

bitcoin.price.ez_prices_hourly

WHERE

symbol = 'BTC'

AND hour BETWEEN '2022-01-01' AND '2024-12-31 23:59:59'

),

transactions_with_prices AS (

SELECT

t.input_value,

t.block_timestamp,

t.rounded_hour,

p.price,

t.input_value * p.price AS transaction_value

FROM

transaction_data t

LEFT JOIN

price_data p

ON

t.rounded_hour = p.hour

SELECT