translationmissingWallets-with-sniped-Tokens_new
Updated 2024-09-15
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
-- Identifiziere CEX-Wallets
cex_wallets AS (
SELECT DISTINCT address
FROM ETHEREUM.core.dim_labels
WHERE label_type = 'cex' AND label_subtype = 'hot_wallet'
),
-- Identifiziere Contract-Adressen
contract_addresses AS (
SELECT DISTINCT address
FROM ETHEREUM.core.dim_contracts
),
-- 1. Stufe: Wallets, die den Token im angegebenen Block erhalten haben
initial_wallets AS (
SELECT DISTINCT tx_to AS wallet_address
FROM ETHEREUM.defi.ez_dex_swaps
WHERE
block_number = {{block}}
AND token_out = lower('{{token}}')
AND tx_to NOT IN (
SELECT pool_address
FROM ETHEREUM.defi.dim_dex_liquidity_pools
WHERE tokens :token0 :: STRING = lower('{{token}}')
OR tokens :token1 :: STRING = lower('{{token}}')
)
AND tx_to NOT IN (SELECT address FROM cex_wallets)
AND tx_to NOT IN (SELECT address FROM contract_addresses)
),
-- 2. Stufe: Wallets, die von den Wallets der ersten Stufe Token erhalten haben
second_level_wallets AS (
SELECT DISTINCT ez.to_address AS wallet_address
FROM ETHEREUM.core.ez_token_transfers ez
INNER JOIN initial_wallets iw ON ez.from_address = iw.wallet_address
QueryRunArchived: QueryRun has been archived