js699monthly bitcoin summary metrics
Updated 2025-04-01
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
›
⌄
with
prices as (
select
DATE_TRUNC('month', hour) AS data,
avg(price) as btc_price
from bitcoin.price.ez_prices_hourly where symbol ='BTC'
group by 1
)
SELECT
DATE_TRUNC('month', BLOCK_TIMESTAMP_HOUR) AS date,
btc_price,
SUM(BLOCK_COUNT) AS monthly_block_count,
SUM(TRANSACTION_COUNT) AS monthly_transaction_count,
MAX(UNIQUE_ADDRESS_COUNT) AS monthly_unique_addresses,
SUM(TOTAL_FEES_NATIVE) AS monthly_total_fees_native,
SUM(TOTAL_FEES_USD) AS monthly_total_fees_usd,
AVG(TOTAL_FEES_NATIVE / TRANSACTION_COUNT) AS avg_fee_per_transaction_native,
AVG(TOTAL_FEES_USD / TRANSACTION_COUNT) AS avg_fee_per_transaction_usd
FROM bitcoin.stats.ez_core_metrics_hourly x
join prices y on DATE_TRUNC('month', x.BLOCK_TIMESTAMP_HOUR)=data
GROUP BY date, btc_price
ORDER BY date DESC
QueryRunArchived: QueryRun has been archived