-- forked from Volume, total loans and loan average @ https://flipsidecrypto.xyz/edit/queries/6fcf26c8-a542-4928-b1fa-e877b3df4a83 with loans as ( SELECT BLOCK_TIMESTAMP, abs(post_balances[0] - pre_balances[0]) / 1e9 AS amount, CASE WHEN ARRAY_CONTAINS( PARSE_JSON( '{"pubkey":"SHARKobtfF1bHhxD2eqftjHBdVSCbKo9JtgK71FhELP","signer":false,"source":"transaction","writable":false}' ), ACCOUNT_KEYS ) THEN 'SHARKY' WHEN ARRAY_CONTAINS( PARSE_JSON( '{"pubkey":"JCFRaPv7852ESRwJJGRy2mysUMydXZgVVhrMLmExvmVp","signer":false,"source":"transaction","writable":false}' ), ACCOUNT_KEYS ) THEN 'CITRUS' END AS PLATFORM FROM solana.core.fact_transactions WHERE BLOCK_TIMESTAMP >= (CURRENT_DATE - interval '{{interval}}') -- AND BLOCK_TIMESTAMP < CURRENT_DATE AND ( ARRAY_CONTAINS( PARSE_JSON( '{"pubkey":"SHARKobtfF1bHhxD2eqftjHBdVSCbKo9JtgK71FhELP","signer":false,"source":"transaction","writable":false}' ), ACCOUNT_KEYS ) OR ARRAY_CONTAINS( PARSE_JSON( '{"pubkey":"JCFRaPv7852ESRwJJGRy2mysUMydXZgVVhrMLmExvmVp","signer":false,"source":"transaction","writable":false}' ), ACCOUNT_KEYS ) ) AND ( array_contains( to_variant('Program log: Instruction: TakeLoan'), log_messages ) OR array_contains( to_variant('Program log: Instruction: Borrow'), log_messages ) OR array_contains( to_variant('Program log: Instruction: MigrateV2Loan'), log_messages ) OR array_contains( to_variant('Program log: Instruction: TakeLoanV3'), log_messages ) ) AND succeeded = True ), reborrows_citrus_1 as ( SELECT block_timestamp as loan_date, inner_instructions[0]:instructions[1]:parsed:info:source as tx_to FROM solana.core.fact_transactions WHERE BLOCK_TIMESTAMP >= (CURRENT_DATE - interval '{{interval}}') -- AND BLOCK_TIMESTAMP < CURRENT_DATE AND ARRAY_CONTAINS( PARSE_JSON( '{"pubkey":"JCFRaPv7852ESRwJJGRy2mysUMydXZgVVhrMLmExvmVp","signer":false,"source":"transaction","writable":false}' ), ACCOUNT_KEYS ) AND array_contains( to_variant('Program log: Instruction: Reborrow'), log_messages ) AND succeeded = True ), reborrows_citrus_2 as ( SELECT loan_date, TX_TO, amount FROM solana.core.fact_transfers join reborrows_citrus_1 using (tx_to) WHERE BLOCK_TIMESTAMP >= ( CURRENT_DATE - interval '{{interval}}' - interval '30 days' ) -- AND BLOCK_TIMESTAMP < CURRENT_DATE AND mint = 'So11111111111111111111111111111111111111112' ), reborrows_citrus_3 as ( SELECT tx_to, max(amount) as amount FROM reborrows_citrus_2 group by tx_to ), reborrows_citrus_4 as ( select loan_date, amount, 'CITRUS' as platform from reborrows_citrus_2 join reborrows_citrus_3 using (tx_to, amount) ), reborrows_sharky_1 as ( SELECT BLOCK_TIMESTAMP as loan_date, inner_instructions[0]:instructions[0]:parsed:info:wallet as tx_to FROM solana.core.fact_transactions WHERE BLOCK_TIMESTAMP >= (CURRENT_DATE - interval '{{interval}}') -- AND BLOCK_TIMESTAMP < CURRENT_DATE AND ARRAY_CONTAINS( PARSE_JSON( '{"pubkey":"SHARKobtfF1bHhxD2eqftjHBdVSCbKo9JtgK71FhELP","signer":false,"source":"transaction","writable":false}' ), ACCOUNT_KEYS ) AND ( array_contains( to_variant('Program log: Instruction: ExtendLoan'), log_messages ) OR array_contains( to_variant('Program log: Instruction: ExtendLoanV2'), log_messages ) OR array_contains( to_variant('Program log: Instruction: ExtendLoanV3'), log_messages ) ) AND succeeded = True ), reborrows_sharky_2 as ( SELECT loan_date, TX_TO, amount FROM solana.core.fact_transfers join reborrows_sharky_1 using (tx_to) WHERE BLOCK_TIMESTAMP >= ( CURRENT_DATE - interval '{{interval}}' - interval '30 days' ) -- AND BLOCK_TIMESTAMP < CURRENT_DATE AND mint = 'So11111111111111111111111111111111111111112' ), reborrows_sharky_3 as ( SELECT tx_to, max(amount) as amount FROM reborrows_sharky_2 group by tx_to ), reborrows_sharky_4 as ( select loan_date, amount, 'SHARKY' as platform from reborrows_sharky_2 join reborrows_sharky_3 using (tx_to, amount) ), mortgages as ( SELECT BLOCK_TIMESTAMP, abs(post_balances[2] - pre_balances[2]) / 1e9 AS amount, 'CITRUS' AS platform FROM solana.core.fact_transactions WHERE BLOCK_TIMESTAMP >= (CURRENT_DATE - interval '{{interval}}') -- AND BLOCK_TIMESTAMP < CURRENT_DATE AND ARRAY_CONTAINS( PARSE_JSON( '{"pubkey":"JCFRaPv7852ESRwJJGRy2mysUMydXZgVVhrMLmExvmVp","signer":false,"source":"transaction","writable":false}' ), ACCOUNT_KEYS ) AND ( array_contains( to_variant('Program log: Instruction: Mortgage'), log_messages ) ) AND succeeded = True ), frakt_pools_transactions as ( SELECT tx_id, instructions[0]:accounts[1] as borrower FROM solana.core.fact_transactions WHERE BLOCK_TIMESTAMP >= (CURRENT_DATE - interval '{{interval}}') -- AND BLOCK_TIMESTAMP < CURRENT_DATE AND ARRAY_CONTAINS( PARSE_JSON( '{"pubkey":"A66HabVL3DzNzeJgcHYtRRNW1ZRMKwBfrdSR4kLsZ9DJ","signer":false,"source":"transaction","writable":false}' ), ACCOUNT_KEYS ) AND ( array_contains( to_variant('Program log: Instruction: ApproveLoanByAdmin'), log_messages ) ) AND succeeded = True ), frakt_pools_transfers as ( SELECT BLOCK_TIMESTAMP, amount, 'FRAKT' AS platform FROM solana.core.fact_transfers t1 JOIN frakt_pools_transactions t2 on t1.tx_id = t2.tx_id AND tx_to = borrower WHERE BLOCK_TIMESTAMP >= (CURRENT_DATE - interval '{{interval}}') -- AND BLOCK_TIMESTAMP < CURRENT_DATE AND mint = 'So11111111111111111111111111111111111111112' ), frakt_cnfts as ( SELECT BLOCK_TIMESTAMP, abs(post_balances[0] - pre_balances[0]) / 1e9 AS amount, 'FRAKT' as platform FROM solana.core.fact_transactions WHERE BLOCK_TIMESTAMP >= (CURRENT_DATE - interval '{{interval}}') -- AND BLOCK_TIMESTAMP < CURRENT_DATE AND ARRAY_CONTAINS( PARSE_JSON( '{"pubkey":"4tdmkuY6EStxbS6Y8s5ueznL3VPMSugrvQuDeAHGZhSt","signer":false,"source":"transaction","writable":false}' ), ACCOUNT_KEYS ) AND ( array_contains( to_variant( 'Program log: Instruction: CreateBondAndSellToOffersCnft' ), log_messages ) ) AND succeeded = True ), frakt_bonds as ( SELECT BLOCK_TIMESTAMP, abs(post_balances[0] - pre_balances[0]) / 1e9 AS amount, 'FRAKT' as platform FROM solana.core.fact_transactions WHERE BLOCK_TIMESTAMP >= (CURRENT_DATE - interval '{{interval}}') -- AND BLOCK_TIMESTAMP < CURRENT_DATE AND ARRAY_CONTAINS( PARSE_JSON( '{"pubkey":"4tdmkuY6EStxbS6Y8s5ueznL3VPMSugrvQuDeAHGZhSt","signer":false,"source":"transaction","writable":false}' ), ACCOUNT_KEYS ) AND ( array_contains( to_variant( 'Program log: Instruction: CreateBondAndSellToOffers' ), log_messages ) ) AND succeeded = True ), frakt_bonds_staked_banx as ( SELECT BLOCK_TIMESTAMP, abs(post_balances[0] - pre_balances[0]) / 1e9 AS amount, 'FRAKT' as platform FROM solana.core.fact_transactions WHERE BLOCK_TIMESTAMP >= (CURRENT_DATE - interval '{{interval}}') -- AND BLOCK_TIMESTAMP < CURRENT_DATE AND ARRAY_CONTAINS( PARSE_JSON( '{"pubkey":"4tdmkuY6EStxbS6Y8s5ueznL3VPMSugrvQuDeAHGZhSt","signer":false,"source":"transaction","writable":false}' ), ACCOUNT_KEYS ) AND ( array_contains( to_variant( 'Program log: Instruction: SellStakedBanxToOffers' ), log_messages ) ) AND succeeded = True ), frakt_bonds_refinance as ( SELECT BLOCK_TIMESTAMP, REGEXP_SUBSTR( log_messages[ARRAY_SIZE(log_messages) -4], '\\d+' -- This regular expression matches one or more digits ) / 1e9 AS amount, 'FRAKT' as platform FROM solana.core.fact_transactions WHERE BLOCK_TIMESTAMP >= (CURRENT_DATE - interval '{{interval}}') -- AND BLOCK_TIMESTAMP < CURRENT_DATE AND ARRAY_CONTAINS( PARSE_JSON( '{"pubkey":"4tdmkuY6EStxbS6Y8s5ueznL3VPMSugrvQuDeAHGZhSt","signer":false,"source":"transaction","writable":false}' ), ACCOUNT_KEYS ) AND ( array_contains( to_variant( 'Program log: Instruction: RefinanceToBondOffersV2' ), log_messages ) ) AND succeeded = True ), frakt_bonds_refinance_bis as ( SELECT BLOCK_TIMESTAMP, REGEXP_SUBSTR( log_messages[ARRAY_SIZE(log_messages) -4], '\\d+' -- This regular expression matches one or more digits ) / 1e9 AS amount, 'FRAKT' as platform FROM solana.core.fact_transactions WHERE BLOCK_TIMESTAMP >= (CURRENT_DATE - interval '{{interval}}') -- AND BLOCK_TIMESTAMP < CURRENT_DATE AND ARRAY_CONTAINS( PARSE_JSON( '{"pubkey":"4tdmkuY6EStxbS6Y8s5ueznL3VPMSugrvQuDeAHGZhSt","signer":false,"source":"transaction","writable":false}' ), ACCOUNT_KEYS ) AND ( array_contains( to_variant( 'Program log: Instruction: RefinanceFbondByLender' ), log_messages ) ) AND succeeded = True ), --rainfi as ( -- SELECT -- BLOCK_TIMESTAMP, -- abs(post_balances[0] - pre_balances[0]) / 1e9 AS amount, -- 'RAINFI' as platform -- FROM -- solana.core.fact_transactions -- WHERE -- BLOCK_TIMESTAMP >= (CURRENT_DATE - interval '{{interval}}') -- -- AND BLOCK_TIMESTAMP < CURRENT_DATE -- AND ARRAY_CONTAINS( -- PARSE_JSON( -- '{"pubkey":"RainEraPU5yDoJmTrHdYynK9739GkEfDsE4ffqce2BR","signer":false,"source":"transaction","writable":false}' -- ), -- ACCOUNT_KEYS -- ) -- AND ( -- array_contains( -- to_variant('Program log: Instruction: TakeLoan'), -- log_messages -- ) -- ) -- AND succeeded = True --), --rainfi as ( -- SELECT -- BLOCK_TIMESTAMP, -- abs(post_balances[0] - pre_balances[0]) / 1e9 AS amount, -- 'RAINFI' as platform -- FROM -- solana.core.fact_transactions -- WHERE -- BLOCK_TIMESTAMP >= (CURRENT_DATE - interval '{{interval}}') -- -- AND BLOCK_TIMESTAMP < CURRENT_DATE -- AND ARRAY_CONTAINS( -- PARSE_JSON( -- '{"pubkey":"RainEraPU5yDoJmTrHdYynK9739GkEfDsE4ffqce2BR","signer":false,"source":"transaction","writable":false}' -- ), -- ACCOUNT_KEYS -- ) -- AND ( -- array_contains( -- to_variant('Program log: Instruction: TakeLoan'), -- log_messages -- ) -- ) -- AND succeeded = True --), total AS ( SELECT * FROM loans UNION ALL SELECT * FROM reborrows_sharky_4 UNION ALL SELECT * FROM reborrows_citrus_4 UNION ALL SELECT * FROM mortgages UNION ALL SELECT * FROM frakt_pools_transfers UNION ALL SELECT * FROM frakt_bonds UNION ALL SELECT * FROM frakt_bonds_refinance_bis UNION ALL SELECT * FROM frakt_bonds_refinance UNION ALL SELECT * FROM frakt_bonds_staked_banx UNION ALL SELECT * FROM frakt_cnfts --UNION ALL --SELECT -- * --FROM -- rainfi ) SELECT date_trunc('day', BLOCK_TIMESTAMP) as blocktime, count(*) as total_loans, avg(amount) as loan_avg, sum(amount) as total_volume, platform FROM total GROUP BY platform, blocktime

P2P NFT LENDING