ChiefYawww
Updated 2023-02-16
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
›
⌄
with
fff as (
SELECT
inner_instruction,
date_trunc('day', e.block_timestamp) as date,
program_id,
tx_from as lender,
tx_to borrower,
mint,
amount as borrow_amount,
parse_json(inner_instruction):instructions[0]:parsed:type as init_loan,
CASE WHEN init_loan = 'createAccount' THEN init_loan END as new_loan,
parse_json(inner_instruction):instructions[5]:parsed:info:mint as nft
FROM
solana.core.fact_events as e
JOIN solana.core.fact_transfers as t ON e.tx_id = t.tx_id
WHERE
succeeded = True
AND program_id = 'JCFRaPv7852ESRwJJGRy2mysUMydXZgVVhrMLmExvmVp'
-- AND e.tx_id = '5tAnRjtbwvUqVMoK2bHJ7T1Bit7uutz85EAjaP1Xa2GFSRCwnd3jWSqEXV3wcoWgW75hrpXD4c5i2bskjicBLezq'
AND e.index = 1
AND new_loan = 'createAccount' -- without this you are counting all money movement (new loans, ending loans, listings maybe)
),
SELECT
parse_json(inner_instruction):instructions[3]:parsed:type as end_loan,
CASE WHEN end_loan = 'thawAccount' THEN end_loan END as end_loan,
end_loan as (
)
SELECT
date,
count(distinct lender) as lenders,
count(distinct borrower) borrowers,
count(nft) NFTs,
sum(borrow_amount) amount_borrowed
Run a query to Download Data