cryptall2024-07-08 04:45 PM copy
    Updated 2024-07-21
    -- forked from 2024-07-08 04:45 PM @ https://flipsidecrypto.xyz/edit/queries/ffd51664-b719-4578-98f1-23b115f6f052

    WITH T1 AS (
    SELECT
    DATE_TRUNC('DAY', BLOCK_TIMESTAMP) AS Date,
    SUM(CASE WHEN ACTIONS IN ('deposit', 'deposit_to_reserve') THEN AMOUNT_USD ELSE 0 END) AS Lend,
    SUM(CASE WHEN ACTIONS IN ('borrow') THEN AMOUNT_USD ELSE 0 END) AS Borrow,
    SUM(CASE WHEN ACTIONS IN ('repay') THEN AMOUNT_USD ELSE 0 END) AS Repay,
    SUM(CASE WHEN ACTIONS IN ('withdraw_succeeded') THEN AMOUNT_USD ELSE 0 END) AS Withraw,
    COUNT(DISTINCT SENDER_ID) AS Active_user
    FROM
    near.defi.ez_lending
    WHERE
    AMOUNT_USD < 1E6
    AND BLOCK_TIMESTAMP >= (current_date - INTERVAL '7 days')
    GROUP BY 1
    ),
    T2 AS (
    SELECT
    *,
    SUM(Lend) OVER (ORDER BY Date) AS Total_lend,
    SUM(Borrow) OVER (ORDER BY Date) AS Total_Borrow,
    SUM(Repay) OVER (ORDER BY Date) AS Total_Repay,
    SUM(Withraw) OVER (ORDER BY Date) AS Total_Withraw
    FROM T1
    )
    SELECT
    Date,
    Lend,
    Borrow,
    Repay,
    Withraw,
    Active_user,
    Total_lend - Total_Withraw AS TVL
    FROM T2
    ORDER BY Date DESC;