NakedCollector2024-06-01 12:51 PM
Updated 2024-06-01
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
›
⌄
WITH fresh_wallet_traders AS (
SELECT from_address, min(block_timestamp) AS first_transaction
FROM ethereum.core.fact_transactions
WHERE from_address IN (
SELECT trader
FROM crosschain.defi.ez_dex_swaps
WHERE block_timestamp > CURRENT_TIMESTAMP() - interval '1 day'
)
GROUP BY from_address
HAVING MIN(block_timestamp) > CURRENT_TIMESTAMP() - interval '1 day'
),
fresh_wallet_buys AS (
SELECT
SUM(amount_out_usd) AS total,
token_out
FROM crosschain.defi.ez_dex_swaps
WHERE trader IN (SELECT from_address FROM fresh_wallet_traders)
AND amount_out_usd IS NOT NULL
AND blockchain = 'ethereum'
AND block_timestamp > CURRENT_TIMESTAMP() - interval '1 day'
GROUP BY token_out
HAVING SUM(amount_out_usd) > 1000
ORDER BY total DESC
),
top_tokens AS (
SELECT DISTINCT
to_varchar(fb.total, '$999,999,999') AS total_bought,
dc.symbol AS symbol,
fb.token_out AS token_address,
fb.total AS bought
FROM fresh_wallet_buys fb
INNER JOIN crosschain.core.dim_contracts dc
ON dc.address = fb.token_out
WHERE dc.symbol NOT LIKE '%USD%'
QueryRunArchived: QueryRun has been archived