Query editor
-- BTC chain metrics validation, last 7 days-- Columns ordered to match Blockchain.com's Section 4.1 metric list, with-- non-requested metrics pushed to the end.---- Confirmation time (Section 4.1 #2) is omitted: Dune has no mempool data.-- Mining pool / hashshare (Section 4.1 #3) lives in a separate query: 7414875.WITH-- Cumulative BTC supply at window start minus 1 day (sum from genesis)baseline AS (SELECT sum(mint_reward) AS supply_at_window_start_minus_1FROM bitcoin.blocksWHERE date < current_date - interval '7' day),-- Per-block intervals for hildobby's hash rate methodblock_intervals AS (SELECTcast(time AS date) AS date,height,difficulty,time,date_diff('second', LAG(time, 1) OVER (ORDER BY height), time) AS interval_secondsFROM bitcoin.blocksWHERE date >= current_date - interval '8' day -- one extra day so LAG has a predecessor),-- Hildobby's per-day hash rate, his exact formula-- (https://dune.com/queries/1836983)-- AVG(difficulty * interval/600) / 10^11-- Units are non-standard (his original query divides by 10^11 with no labelled unit).hildobby_per_day AS (SELECTdate,AVG(CAST(difficulty AS double) * (interval_seconds / 600)) / POWER(10, 11) AS hashrate_hildobby_unitsFROM block_intervalsWHERE interval_seconds IS NOT NULLAND date >= current_date - interval '7' dayGROUP BY 1),-- Per-day standard aggregatesdaily AS (SELECTdate,count(*) AS blocks_mined,-- Hash rate (standard physics-derived formula): D * 2^32 / 600s target-- Result in EH/s by dividing by 10^18avg(difficulty) * power(2, 32) / 600 / power(10, 18) AS hashrate_eh_per_sec_standard,-- Supplysum(mint_reward) AS daily_mint_btc,-- Difficultyavg(difficulty) AS avg_difficulty,-- Block size: three valid definitions, plus mediancast(avg(size) AS integer) AS avg_block_size_bytes,cast(approx_percentile(size, 0.5) AS integer) AS median_block_size_bytes,