permarywilful-crimson
Updated 2024-10-12
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
›
⌄
-- Step 1: Calculate the balance for each address as of 4 years ago (before 2021)
WITH historical_balance AS (
SELECT
fo.pubkey_script_address,
(SUM(fo.value_sats) - COALESCE(SUM(fi.value_sats), 0)) / 100000000 AS balance -- Sum of outputs minus inputs
FROM
bitcoin.core.fact_outputs fo -- outputs (credits)
LEFT JOIN
bitcoin.core.fact_inputs fi -- inputs (debits)
ON
fo.pubkey_script_address = fi.pubkey_script_address
AND fi.block_timestamp <= '2020-12-31' -- Only consider inputs before 2021
WHERE
fo.block_timestamp <= '2020-12-31' -- Only consider outputs before 2021
GROUP BY
fo.pubkey_script_address
HAVING
(SUM(fo.value_sats) - COALESCE(SUM(fi.value_sats), 0)) / 100000000 = 1 -- Address had exactly 1 BTC before 2021
)
-- Step 2: Return addresses with a balance of 1 BTC by the end of 2020
SELECT
hb.pubkey_script_address
FROM
historical_balance hb;
QueryRunArchived: QueryRun has been archived