SELECT

date_trunc('day', hour) as day,

avg(price) as btc_price

FROM

bitcoin.price.ez_prices_hourly

group by

1

),

new_users as (

SELECT

date_trunc('day', first_tx) as days,

count(DISTINCT PUBKEY_SCRIPT_ADDRESS) as new_users,

sum(new_users) over(

ORDER BY

days

) as cumulative_users

FROM

(

SELECT

PUBKEY_SCRIPT_ADDRESS,

min(block_timestamp) as first_tx

FROM

bitcoin.core.fact_outputs

GROUP BY

1

)

GROUP BY

1

date_trunc(day, BLOCK_TIMESTAMP) as date,

count(*) as transactions,

btc_price,

new_users,

cumulative_users,