cryptall2024-07-08 04:45 PM copy
Updated 2024-07-21
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- 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;