superflyFinal List of Sybil Wallets previous to filter by Initial L0/Nansen/Chaos List copy
Updated 2024-10-05
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 adriaparcerisas / Final List of Sybil Wallets previous to filter by Initial L0/Nansen/Chaos List @ https://flipsidecrypto.xyz/adriaparcerisas/q/PByNZ1JS74kP/final-list-of-sybil-wallets-previous-to-filter-by-initial-l0-nansen-chaos-list
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 <100 -- 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,
QueryRunArchived: QueryRun has been archived