gscheibe2024-01-12 10:44 AM
Updated 2024-01-19
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
›
⌄
⌄
/* ============================================================
3.2 - COUNT: DATE RANGE - UNIQUE ADDRESS RELATIONS ON PER DAY
ON A DATE RANGE WHERE THE TRANSACTION AMOUNT MOVED IS
EQUAL OR HIGHER THAN A BTC_MIN IN BTC
-- REMOVE PARALLEL EDGES
=============================================================== */
SELECT T1.DAY, COUNT(DISTINCT T2.pubkey_script_address, T3.pubkey_script_address) AS Total
FROM (
SELECT BT.tx_id, date_trunc('day', (BT.block_timestamp)) AS DAY
FROM bitcoin.core.fact_transactions AS BT
WHERE date_trunc('day', (BT.block_timestamp)) >= '{{DAY}}' AND date_trunc('day', (BT.block_timestamp)) < '{{DAY2}}'
AND BT.input_value >= {{BTC_MIN}} -- Define the MIN Amount of BTC
AND BT.is_coinbase = FALSE -- Exclude COINBASE transactions (no source address)
) AS T1
LEFT JOIN (
SELECT TI.pubkey_script_address, TI.tx_id
FROM bitcoin.core.fact_inputs AS TI
WHERE date_trunc('day', (TI.block_timestamp)) >= '{{DAY}}' AND date_trunc('day', (TI.block_timestamp)) < '{{DAY2}}'
) AS T2 ON T1.tx_id = T2.tx_id
LEFT JOIN (
SELECT TX.pubkey_script_address, TX.tx_id
FROM bitcoin.core.fact_outputs AS TX
WHERE date_trunc('day', (TX.block_timestamp)) >= '{{DAY}}' AND date_trunc('day', (TX.block_timestamp)) < '{{DAY2}}'
) AS T3 ON T1.tx_id = T3.tx_id
WHERE T2.pubkey_script_address <> T3.pubkey_script_address -- Exclude self loops / Change
GROUP BY T1.DAY
ORDER BY T1.DAY
QueryRunArchived: QueryRun has been archived