SELECT

date_trunc('d', block_timestamp) as date,

sum(value) as output_value

FROM bitcoin.core.fact_outputs

WHERE block_timestamp::date >= current_date - 30

GROUP BY 1

),

value_in as (

SELECT

date_trunc('d', block_timestamp) as date,

sum(value) as input_value

FROM bitcoin.core.fact_inputs

WHERE block_timestamp::date >= current_date - 30

GROUP BY 1

,

btc_price as (

SELECT

date_trunc('d', hour) as date,

median(price) as price

FROM ethereum.price.ez_hourly_token_prices

WHERE symbol = 'WBTC'

AND hour::date >= current_date - 30

GROUP BY date

SELECT

a.date,

(output_value + input_value) as volume_of_transfer_btc,

volume_of_transfer_btc * price as volume_of_transfer_usd

FROM value_out a

LEFT JOIN value_in b using (date)

LEFT JOIN btc_price c ON a.date = c.date

WHERE a.date < current_date

ORDER BY date desc