cristinatintobitcoin2 2.2
Updated 2023-07-21
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
›
⌄
-- Calculate changes in mining difficulty before and after each halving
WITH halvings AS (
SELECT
BLOCK_NUMBER,
block_timestamp,
case when (block_number between 180000 and 210000 or block_number between 390000 and 420000 or block_number between 600000 and 630000 or block_number between 810000 and 840000) then 'Days before halving'
when (block_number between 210001 and 240000 or block_number between 420001 and 450000 or block_number between 630001 and 660000 or block_number between 840001 and 870000) then 'Days after halving'
else 'Other days' end as period
FROM bitcoin.core.fact_blocks
),
mining_difficulty AS (
SELECT
BLOCK_NUMBER,
DIFFICULTY
FROM bitcoin.core.fact_blocks
)
SELECT
date_trunc('month',block_timestamp) as date,
period,
AVG(mining_difficulty.DIFFICULTY) AS average_mining_difficulty
FROM halvings
JOIN mining_difficulty
ON halvings.BLOCK_NUMBER = mining_difficulty.BLOCK_NUMBER
GROUP BY 1,2
ORDER BY 1 asc
Run a query to Download Data