flyingfishPink_Drainer_ethereum_scam_stats
Updated 2023-11-09
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
›
⌄
-- TODO
-- There is no price for Blur Pool token and all those txs have a null amount
-- contract_address = '0x0000000000a39bb272e79075ade125fd351887ac'
WITH raw_data AS (
select
b.tx_hash -- b.evt_tx_hash,
, b.contract_address AS token -- b.contract_address as token,
, b.raw_amount AS total_amount -- b.value as total_amount,
-- , b.decimals
, iff(token = '0xbd2949f67dcdc549c6ebe98696449fa79d988a9f', 18, b.decimals) as decimals
, b.symbol
, b.amount
-- , b.amount_usd
, iff(token = '0xbd2949f67dcdc549c6ebe98696449fa79d988a9f', b.amount_usd / pow(10, 18), b.amount_usd) as amount_usd
, b.from_address AS victim -- b."from" as victim,
, b.block_timestamp -- b.evt_block_time,
FROM ethereum.core.ez_token_transfers b
WHERE
-- exclude swaps
b.origin_to_address not in (
-- ethereum
'0x13f4ea83d0bd40e75c8222255bc855a974568dd4',
'0x1a1ec25dc08e98e5e93f1104b5e5cdd298707d31',
'0x4c60051384bd2d3c01bfc845cf5f4b44bcbe9de5',
'0x82e0b8cdd80af5930c4452c684e71c861148ec8a',
'0x881d40237659c251811cec9c364ef91dc08d300c',
'0xef1c6e67703c7bd7107eed8303fbe6ec2554bf6b',
'0x6131b5fae19ea4f9d964eac0408e4408b66337b5'
)
and (
b.origin_from_address in (lower('0x63605E53D422C4F1ac0e01390AC59aAf84C44A51'),'0x00000f312c54d0dd25888ee9cdc3dee988700000')
or b.to_address in (lower('0x63605E53D422C4F1ac0e01390AC59aAf84C44A51'),'0x00000f312c54d0dd25888ee9cdc3dee988700000')
)
AND b.block_timestamp >= '{{startDate}}'
-- exluding ShibDoge due to wrong price
-- problem can be resolved by getting prices from crosschain.core.fact_hourly_prices
Run a query to Download Data