suchanad2024-06-06 08:03 PM
Updated 2024-06-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
32
33
34
35
36
›
⌄
⌄
/* This SQL query identifies Bitcoin addresses that are
involved in recurring transactions on a specific day.
It groups these repeated transactions, assigns them a unique
pseudo wallet ID, and lists all the associated addresses,
helping to detect patterns that suggest the same entity
might control those addresses. */
/* 2. Transaction Amounts & Timing: */
/* COMPLETED */
/* Recurring Transfers: If there's a pattern of repeated transfers between */
/* specific addresses with similar amounts and timing, it suggests they might */
/* be controlled by the same entity for managing funds */
WITH repeattrans AS (
SELECT i.PUBKEY_SCRIPT_ADDRESS AS INPUT_ADDRESS,
o.PUBKEY_SCRIPT_ADDRESS AS OUTPUT_ADDRESS,
date_trunc('day', i.BLOCK_TIMESTAMP) as in_day,
date_trunc('day', o.BLOCK_TIMESTAMP) as out_day,
i.value as in_value,
o.value as out_value
FROM Bitcoin.CORE.fact_inputs AS i
JOIN Bitcoin.CORE.fact_outputs AS o ON i.tx_id = o.tx_id
WHERE date_trunc('day', i.BLOCK_TIMESTAMP) >= '2024-06-05'
AND date_trunc('day', i.BLOCK_TIMESTAMP) <= '2024-06-05'
AND date_trunc('day', o.BLOCK_TIMESTAMP) >= '2024-06-05'
AND date_trunc('day', o.BLOCK_TIMESTAMP) <= '2024-06-05'
),
repeaters as
(select a.input_address,
a.output_address,
a.in_day,
a.out_day,
a.in_value,
a.out_value,
count(*) as repeat_count
from repeattrans a
QueryRunArchived: QueryRun has been archived