MLDZMN2024-02-16 03:47 AM
    Updated 2024-02-23
    with t1 as (select
    BLOCK_TIMESTAMP,
    symbol,
    CURRENT_BAL
    from ethereum.core.ez_balance_deltas
    where USER_ADDRESS = lower('0xc977CBadD359aE06b236D9581e37fd5A03E54b84')
    and symbol in ('PDT','PRIME','stETH','USDC')
    ),

    final as (SELECT
    t1.BLOCK_TIMESTAMP::date as day,
    t1.symbol,
    t1.CURRENT_BAL

    FROM t1
    INNER JOIN
    (
    SELECT DATE(BLOCK_TIMESTAMP) AS trade_date,t1.symbol, MAX(BLOCK_TIMESTAMP) AS max_BLOCK_TIMESTAMP
    FROM t1
    GROUP BY DATE(BLOCK_TIMESTAMP),t1.symbol
    ) t2
    ON t2.trade_date = DATE(t1.BLOCK_TIMESTAMP) AND
    t2.max_BLOCK_TIMESTAMP = t1.BLOCK_TIMESTAMP
    and t1.symbol=t2.symbol
    ORDER BY
    t1.BLOCK_TIMESTAMP),

    final2 as (select
    date(HOUR)as day,
    p.symbol,
    avg(price) as token_price,
    CURRENT_BAL,
    CURRENT_BAL*token_price as balance_usd



    QueryRunArchived: QueryRun has been archived