greyswan2023-10-18 08:16 PM
    Updated 2023-10-25
    select
    signers [0] as borrower_address,
    sum(amount) as amount_borrow,
    count(*) as number_of_loans
    from
    solana.core.fact_events
    inner join solana.core.fact_transactions using(tx_id, block_timestamp, succeeded)
    inner join lateral flatten (input => fact_transactions.log_messages) f_logs
    inner join solana.core.fact_transfers using(tx_id, block_timestamp)
    where
    succeeded
    and fact_events.program_id = '4tdmkuY6EStxbS6Y8s5ueznL3VPMSugrvQuDeAHGZhSt'
    and fact_transactions.log_messages [f_logs.index -1] = 'Program 4tdmkuY6EStxbS6Y8s5ueznL3VPMSugrvQuDeAHGZhSt invoke [1]'
    and regexp_replace(f_logs.value, '^Program log: Instruction: ') in (
    'BorrowPerpetual',
    'BorrowCnftPerpetual',
    'BorrowStakedBanxPerpetual',
    'BorroweRefinance',
    'RefinancePerpetualLoan',
    'InstantRefinancePerpetualLoan'
    )
    and fact_transfers.mint = 'So11111111111111111111111111111111111111112'
    and case
    when not regexp_replace(f_logs.value, '^Program log: Instruction: ') in (
    'RefinancePerpetualLoan',
    'InstantRefinancePerpetualLoan'
    ) then not fact_transfers.tx_from = signers [0]
    else TRUE
    end
    and block_timestamp :: date > current_date() - interval '{{days}} days'
    group by
    signers [0]
    order by sum(amount) desc
    Run a query to Download Data