adriaparcerisasList of Wallets in a Sybil Cluster
Updated 2024-05-29
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
›
⌄
WITH Transactions AS (
SELECT
SENDER_WALLET,
SOURCE_CHAIN,
DESTINATION_CHAIN,
COUNT(*) AS tx_count,
SUM(STARGATE_SWAP_USD) AS total_volume_usd,
MIN(SOURCE_TIMESTAMP_UTC) AS first_tx,
MAX(SOURCE_TIMESTAMP_UTC) AS last_tx,
COUNT(DISTINCT SOURCE_CONTRACT) AS distinct_source_contracts
FROM
external.layerzero.fact_transactions_snapshot
GROUP BY
SENDER_WALLET,SOURCE_CHAIN,DESTINATION_CHAIN
),
criteria as (
SELECT
*
FROM
Transactions
WHERE
tx_count <5 -- minimo de 5 transacciones
AND total_volume_usd >1000 -- menso de 100 USD de volumen
AND datediff(hour,first_tx,last_tx) < 24 -- Transacciones en menos de un día
AND distinct_source_contracts < 3 -- Pocos contratos de origen
ORDER BY
tx_count DESC
),
-- Refinamiento con correlación temporal y patrones de uso de contratos
RefinedSuspectWallets AS (
SELECT
SENDER_WALLET,
SOURCE_CHAIN,
DESTINATION_CHAIN,
COUNT(*) AS tx_count,
QueryRunArchived: QueryRun has been archived