DAY | OUTSTANDING_USD | |
|---|---|---|
1 | 2025-04-01 00:00:00.000 | 0 |
2 | 2025-03-01 00:00:00.000 | 0 |
3 | 2025-02-01 00:00:00.000 | 0 |
4 | 2025-01-01 00:00:00.000 | 0 |
5 | 2024-12-01 00:00:00.000 | 0 |
6 | 2024-11-01 00:00:00.000 | 0 |
7 | 2024-10-01 00:00:00.000 | 0 |
8 | 2024-09-01 00:00:00.000 | 0 |
9 | 2024-08-01 00:00:00.000 | 0 |
10 | 2024-07-01 00:00:00.000 | 0 |
11 | 2024-06-01 00:00:00.000 | 0 |
12 | 2024-05-01 00:00:00.000 | 0 |
13 | 2024-04-01 00:00:00.000 | 0 |
14 | 2024-03-01 00:00:00.000 | 0 |
15 | 2024-02-01 00:00:00.000 | 0 |
16 | 2024-01-01 00:00:00.000 | 0 |
17 | 2023-12-01 00:00:00.000 | 0 |
18 | 2023-11-01 00:00:00.000 | 0 |
19 | 2023-10-01 00:00:00.000 | 0 |
20 | 2023-09-01 00:00:00.000 | 0 |
smartymetricsoutstanding
Updated 2025-04-28
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
›
⌄
-- 1. Generate all months in the data range for the events
WITH months AS (
SELECT DISTINCT DATE_TRUNC('month', block_timestamp) AS month
FROM solana.core.fact_decoded_instructions
WHERE program_id = 'MFv2hWf31Z9kbCa1snEPYctwafyhdvnV7FZnsebVacA' -- program id of marginfi
AND event_type IN ('lendingAccountBorrow', 'lendingAccountRepay') -- select borrow or repay events
),
-- 2. Calculate monthly borrows and repays
txns AS (
SELECT
DATE_TRUNC('month', tr.block_timestamp) AS month,
SUM(CASE WHEN di.event_type = 'lendingAccountBorrow'
THEN tr.amount / POWER(10, COALESCE(p.decimals, 0)) * COALESCE(p.price, 0)
ELSE 0 END) AS borrowed_usd,
SUM(CASE WHEN di.event_type = 'lendingAccountRepay'
THEN tr.amount / POWER(10, COALESCE(p.decimals, 0)) * COALESCE(p.price, 0)
ELSE 0 END) AS repaid_usd
FROM solana.core.fact_transfers tr
JOIN solana.core.fact_decoded_instructions di
ON tr.tx_id = di.tx_id
AND di.program_id = 'MFv2hWf31Z9kbCa1snEPYctwafyhdvnV7FZnsebVacA'
AND di.event_type IN ('lendingAccountBorrow', 'lendingAccountRepay')
JOIN solana.price.ez_prices_hourly p
ON tr.mint = p.token_address
AND DATE_TRUNC('hour', tr.block_timestamp) = p.hour
AND p.name IS NOT NULL
GROUP BY month
)
-- 3. Calculate outstanding balance as cumulative borrows minus cumulative repays.
SELECT
months.month AS day,
COALESCE(SUM(txns.borrowed_usd) OVER (ORDER BY months.month), 0) -
COALESCE(SUM(txns.repaid_usd) OVER (ORDER BY months.month), 0) AS outstanding_usd
FROM months
LEFT JOIN txns
ON months.month = txns.month
Last run: about 1 month ago
27
811B
2s