smritiBorrow(outstanding)
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
›
⌄
WITH marginfi_events AS (
SELECT
tx_id,
block_timestamp,
event_type
FROM solana.core.fact_decoded_instructions
WHERE
program_id = 'MFv2hWf31Z9kbCa1snEPYctwafyhdvnV7FZnsebVacA'
AND event_type IN ('lendingAccountBorrow', 'lendingAccountRepay')
),
transfers_with_event AS (
SELECT
ft.mint AS token_address,
ft.amount,
e.event_type
FROM solana.core.fact_transfers ft
INNER JOIN marginfi_events e
ON ft.tx_id = e.tx_id
WHERE ft.amount > 0 -- filters out noise
)
SELECT
a.token_address,
COALESCE(mp.symbol, 'Unknown') AS token_symbol,
COALESCE(mp.name, 'Unknown') AS token_name,
SUM(CASE WHEN a.event_type = 'lendingAccountBorrow' THEN amount ELSE 0 END) AS total_borrowed,
SUM(CASE WHEN a.event_type = 'lendingAccountRepay' THEN amount ELSE 0 END) AS total_repaid,
SUM(CASE WHEN a.event_type = 'lendingAccountBorrow' THEN amount ELSE 0 END) -
SUM(CASE WHEN a.event_type = 'lendingAccountRepay' THEN amount ELSE 0 END) AS outstanding_borrows
FROM transfers_with_event a
LEFT JOIN solana.price.ez_prices_hourly mp ON a.token_address = mp.token_address
GROUP BY 1, 2, 3
ORDER BY outstanding_borrows DESC
Run a query to Download Data