anomonetest date_diff
    Updated 2022-03-06
    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