SELECT

date_trunc('month', block_timestamp) AS date,

COUNT(DISTINCT PUBKEY_SCRIPT_ADDRESS) AS "No. of active addresses"

FROM bitcoin.core.fact_inputs

GROUP BY date

),

Transactions AS (

SELECT date_trunc('month', block_timestamp) AS date,

COUNT(*) AS "Number of txns"

FROM bitcoin.core.fact_transactions

GROUP BY date

),

Volume AS (

SELECT date_trunc('month', block_timestamp) AS date,

SUM(output_value) AS "Bitcoin volume"

FROM bitcoin.core.fact_transactions

WHERE output_value is not NULL

GROUP BY date

),

Fees AS (

SELECT date_trunc('month', block_timestamp) AS date,

AVG(fee) AS "Avg txn fee"

FROM bitcoin.core.fact_transactions

GROUP BY date

SELECT a. date,

"No. of active addresses",

"Number of txns",

"Bitcoin volume",

"Avg txn fee"

FROM Addresses a JOIN Transactions b ON a.date = b.date

JOIN Volume c ON a.date = c.date

JOIN Fees d ON a.date = d.date

;