noanuman-1x1vZppleasant-scarlet copy
Updated 2024-11-07
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 changes AS (
SELECT
b.USER_ADDRESS,
b.BALANCE,
p.PRICE,
(p.PRICE * (b.BALANCE / 1e18)) AS DOLLAR_AMT,
b.BLOCK_TIMESTAMP,
ROW_NUMBER() OVER (PARTITION BY b.USER_ADDRESS ORDER BY b.BLOCK_TIMESTAMP) AS rank
FROM ethereum.core.fact_token_balances b
JOIN ethereum.price.ez_prices_hourly p
ON p.HOUR = DATE_TRUNC('HOUR', b.BLOCK_TIMESTAMP)
AND LOWER(p.TOKEN_ADDRESS) = LOWER('0xaaeE1A9723aaDB7afA2810263653A34bA2C21C7a')
WHERE LOWER(b.CONTRACT_ADDRESS) = LOWER('0xaaeE1A9723aaDB7afA2810263653A34bA2C21C7a')
),
-- DOESN'T WORK BECAUSE IF SOMEONE DIDN'T CHANGE ANYTHING THEY WON'T BE RECOREDED BUT THEY'RE A HOLDER
over1k AS (
SELECT
USER_ADDRESS,
MIN(DOLLAR_AMT),
MIN(BLOCK_TIMESTAMP)
FROM changes
WHERE BLOCK_TIMESTAMP::DATE >= DATEADD(MONTH, -{{month}}, CURRENT_DATE - {{interval}})
GROUP BY USER_ADDRESS
HAVING MIN(DOLLAR_AMT)>= 1000
),
dex_sells AS (
SELECT
s.ORIGIN_FROM_ADDRESS,
s.BLOCK_TIMESTAMP
FROM ethereum.defi.ez_dex_swaps s
JOIN over1k o
ON LOWER(o.USER_ADDRESS) = LOWER(s.ORIGIN_FROM_ADDRESS)
WHERE s.BLOCK_TIMESTAMP::DATE >= DATEADD(MONTH, -{{month}}, CURRENT_DATE - {{interval}})
QueryRunArchived: QueryRun has been archived