dayvidjoshbitcoin2 4
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- Calculate changes in estimated network hashrate before and after each halving
WITH halvings AS (
SELECT
BLOCK_NUMBER,
COUNT(*) OVER (PARTITION BY NULL ORDER BY BLOCK_NUMBER) AS halving_number
FROM bitcoin.core.fact_blocks
WHERE BLOCK_NUMBER IN (840000, 630000, 420000, 210000) -- Replace with actual halving block numbers
),
mining_difficulty AS (
SELECT
BLOCK_NUMBER,
DIFFICULTY
FROM bitcoin.core.fact_blocks
),
block_times AS (
SELECT
BLOCK_NUMBER,
BLOCK_TIMESTAMP,
LAG(BLOCK_TIMESTAMP) OVER (ORDER BY BLOCK_TIMESTAMP) AS prev_timestamp
FROM bitcoin.core.fact_blocks
)
SELECT
halvings.halving_number,
AVG(mining_difficulty.DIFFICULTY) AS average_difficulty,
AVG(1.0 / (DATEDIFF(SECOND, block_times.prev_timestamp, block_times.BLOCK_TIMESTAMP))) AS estimated_network_hashrate
FROM halvings
JOIN mining_difficulty
ON halvings.BLOCK_NUMBER = mining_difficulty.BLOCK_NUMBER
JOIN block_times
ON halvings.BLOCK_NUMBER = block_times.BLOCK_NUMBER
GROUP BY halvings.halving_number
ORDER BY halvings.halving_number
Run a query to Download Data