MoeMaple - 2022 - 0
Updated 2023-09-19Copy Reference Fork
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
›
⌄
-- Thanks to H4wk
with base as (select
s.BLOCK_TIMESTAMP ,
split(logs.value,':')[2] as action,
tt.amount ,
tt.mint ,
s.tx_id ,
signers[0] as user ,
(FEE/1e9) as fee_paid
from solana.core.fact_transactions s
join (select distinct block_timestamp, tx_id
from solana.core.fact_events where succeeded = True
and program_id = '5D9yi4BKrxF8h65NkVE1raCCWFKUs5ngub2ECxhvfaZe'
-- and ARRAY_CONTAINS('4cKB5xKtDpv4xo6ZxyiEvtyX3HgXzyJUS1Y8hAfoNkMT'::VARIANT, instruction:accounts) --BLUECHIP MPG
) events
using(tx_id, block_timestamp)
join lateral flatten (input => log_messages) logs
join solana.core.fact_transfers tt using (tx_id)
where BLOCK_TIMESTAMP between '2022-04-15' and '2022-12-15'
and logs.value ilike 'Program log: Instruction%'
)
select
date_trunc(month,block_timestamp)::date as date ,
case when action like '%Drawdown%' then 'Borrow'
when action like '%Fund%' then 'Lend'
when (action like '%Repay%' or action like '%Payment') then 'Repay' end as action_type,
case when action like '%OpenTerm%' then 'OpenTerm'
else 'FixedTerm' end as loan_type,
case when action = 'OpenTermLoanDrawdown' then 'OpenTerm_Borrow'
when action = 'OpenTermLoanFund' then 'OpenTerm_Lend'
when action = 'OpenTermLoanRepay' then 'OpenTerm_Repay'
Run a query to Download Data