SELECT

DATE_TRUNC('WEEK', 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

Date,

Lend,

Borrow,

Repay,

Withraw,

Active_user,

Total_lend - Total_Withraw AS TVL

FROM T2

ORDER BY Date DESC;