MLDZMNET.3
    Updated 2022-09-17
    WITH price as (
    SELECT Date_trunc('day',HOUR) AS Date, TOKEN_ADDRESS, SYMBOL AS SYM, AVG(PRICE) AS Price
    FROM ethereum.core.fact_hourly_token_prices
    GROUP BY 1,2,3
    ),

    t1 AS(
    SELECT BALANCE_DATE,USER_ADDRESS,IFF(CONTRACT_ADDRESS='ETH','0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2',CONTRACT_ADDRESS) AS CONTRACT_ADDRESS,SYMBOL,BALANCE
    FROM ethereum.erc20_balances
    WHERE (USER_ADDRESS = LOWER('0x5934807cC0654d46755eBd2848840b616256C6Ef')
    OR USER_ADDRESS = LOWER('0x64187ae08781B09368e6253F9E94951243A493D5'))
    --AND BALANCE_DATE = (SELECT MAX(BALANCE_DATE) FROM ethereum.erc20_balances)
    )


    SELECT date_trunc('month',BALANCE_DATE) as month,
    SYMBOL as token,
    SUM(PRICE*BALANCE) AS TVL,
    sum(TVL) over (partition by token order by month) as cumulative_TVL
    FROM t1
    LEFT JOIN PRICE ON t1.CONTRACT_ADDRESS = price.TOKEN_ADDRESS AND t1.BALANCE_DATE = price.date
    group by 1,2 having TVL>100

    Run a query to Download Data