m4ri4ncochoClustering with change / Binance
Updated 2024-04-12
999
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
›
⌄
-- forked from 2023-11-07 04:57 PM @ https://flipsidecrypto.xyz/edit/queries/26527432-643f-4dc9-adf5-3b540a22b160
-- CLUSTERING INPUT ADDRESSES WITH THEIR CHANGE ADDRESSES
-- IDENTIFYING TX WITH 1 INPUTS AND 2 OUTPUTS
with first_selection as (
SELECT
tx_hash,
input_count,
output_count
from bitcoin.core.fact_transactions
where input_count = 1
and output_count = 2
and date_trunc('day',block_timestamp) > '2016-12-31'
),
-- ADDING OUTPUTS WITH THEIR VALUES
outputs_first_selection as (
SELECT
a.tx_hash,
b.PUBKEY_SCRIPT_ADDRESS as output,
b.value
from first_selection a
left join bitcoin.core.fact_outputs b
on a.tx_hash = b.tx_id
),
-- RECEIVERS LIST. VALUE <= 4 DECIMALS
receivers as (
SELECT
tx_hash,
output as receiver,
value
QueryRunArchived: QueryRun has been archived