Query editor
-- This query used incompatible data types from Dune SQL alpha and may need to be updated.-- More details can be found on https://dune.com/docs/query/dunesql-changes/with base as (SELECTdate_trunc('{{granularity}}', time) time/*rewards*/, sum(total_fees) as total_fees, sum(mint_reward) as total_mint, max(mint_reward) as mint_reward, avg(total_fees) as average_fees_per_block/*size (bytes), transactions, and weights*/, avg(transaction_count) as transaction_per_block, avg(stripped_size) as stripped_bytes_per_block, avg(case when height >= 481824 then weight else 0 end)as block_weight -- stripped_size*3 + size, block_limit, avg(total_fees/stripped_size)*1e8 as cost_per_byte/*difficulty and pace*/, 10 as target_pace, avg(difficulty) as difficulty, avg(difficulty* pow(2,32) / 600) as hash_rate --hash rate (hash/s) = difficulty * 2^32 / 600, avg(EXTRACT(MINUTE FROM block_pace)) as block_paceFROM (SELECT*, time - LAG(time) OVER (ORDER BY height asc) AS block_pace, case when height < 481824 then 1e6 else 4*1e6 end as block_limitFROM bitcoin.blocks)group by 1, block_limitorder by time asc)SELECT*--getting totals for supply and fee revenues, sum(total_mint) over (order by time asc) as total_supply, sum(total_fees) over (order by time asc) as lifetime_fees, (sum(total_mint) over (order by time asc) + sum(total_fees) over (order by time asc))*p.price as lifetime_revenue_usd, 21*1e6 as maximum_supply--adding prices data for BTC, p.price as btc_priceFROM baseLEFT JOIN prices.usd p ON base.time = p.minute AND p.symbol = 'BTC' and p.blockchain is nullorder by time asc