oscarqblast_spam_tokens
Updated 2024-10-23
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
›
⌄
-- forked from avalanche_spam_tokens @ https://flipsidecrypto.xyz/studio/queries/eb6d9797-db74-482f-b2e8-bc2b0d39d1a7
-- forked from optimism_base_spam_tokens copy @ https://flipsidecrypto.xyz/studio/queries/69d5abe1-c895-48f3-919e-45f76420d92a
-- forked from base_spam_tokens @ https://flipsidecrypto.xyz/studio/queries/d1a94515-adfa-4ef2-855d-78fdc226e154
-- forked from arbitrum_spam_tokens @ https://flipsidecrypto.xyz/studio/queries/599fe9ba-ec5e-4ad8-9585-3ddefa527192
-- forked from bsc_spam_tokens @ https://flipsidecrypto.xyz/studio/queries/2de95684-6afe-41b4-b825-206d475efc55
-- forked from ethereum_spam_tokens @ https://flipsidecrypto.xyz/studio/queries/2186037f-b46a-484c-abc9-c5b28986532c
SELECT
pc.address,
pc.symbol,
pc.name,
pc.creator_address,
pc.created_block_timestamp,
count(*) AS number_transfers,
count(distinct tr.to_address) AS number_recipients,
FROM
blast.core.dim_contracts pc
LEFT JOIN blast.defi.dim_dex_liquidity_pools lp ON pc.address = lp.pool_address
LEFT JOIN blast.core.ez_token_transfers tr ON pc.address = tr.contract_address
WHERE
len(pc.symbol) >= 3
AND lp.pool_address IS NULL
AND pc.created_block_timestamp BETWEEN CAST('{{initial_date}}' AS timestamp) AND CAST('{{final_date}}' AS timestamp)
AND tr.block_timestamp BETWEEN CAST('{{initial_date}}' AS timestamp) AND CAST('{{final_date}}' AS timestamp)
GROUP BY
1,2,3,4,5
HAVING
count(*) > 500
AND count(distinct to_address) > 500
ORDER BY
CREATED_BLOCK_TIMESTAMP DESC
QueryRunArchived: QueryRun has been archived