anomonetest date_diff
Updated 2022-03-06
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
›
⌄
WITH members as (
SELECT * FROM (
SELECT BALANCE_DATE as M_BALANCE_DATE,
USER_ADDRESS as Member_address,
BALANCE as Member_balance,
ROW_NUMBER() OVER ( PARTITION BY USER_ADDRESS ORDER BY BALANCE_DATE desc) rn
FROM ethereum.erc20_balances
WHERE CONTRACT_ADDRESS = LOWER('0x2d94aa3e47d9d5024503ca8491fce9a2fb4da198')
AND BALANCE >= 35000 and BALANCE <= 150000
--AND USER_ADDRESS = lower('0x9e301190d0bdf64e287d6205d86c488fbd37d4f8')
AND BALANCE_DATE >= to_date('2021-05-04') and BALANCE_DATE < to_date(getdate())
) WHERE rn = 1
),
whales as (
SELECT * FROM
(SELECT BALANCE_DATE ,--as W_BALANCE_DATE,
USER_ADDRESS ,--as Whale_Address,
BALANCE, --as Whale_Balance
ROW_NUMBER() OVER ( PARTITION BY USER_ADDRESS ORDER BY BALANCE_DATE asc) rn2
FROM ethereum.erc20_balances
WHERE CONTRACT_ADDRESS = LOWER('0x2d94aa3e47d9d5024503ca8491fce9a2fb4da198')
AND BALANCE > 150000
AND USER_ADDRESS = lower('0x9e301190d0bdf64e287d6205d86c488fbd37d4f8')
AND BALANCE_DATE >= to_date('2021-05-04')
) WHERE rn2 = 1
)
SELECT m.* , w.BALANCE_DATE as W_BALANCE_DATE , w.BALANCE as Whale_Balance
FROM members m
INNER JOIN whales w
WHERE m.Member_address = w.USER_ADDRESS
--AND datediff(day, M_BALANCE_DATE , W_BALANCE_DATE) = 1
Run a query to Download Data