-- forked from Volume, total loans and loan average (30D) copy @ https://flipsidecrypto.xyz/edit/queries/f1e35ec5-04da-4305-a077-3bff0d5318ce -- forked from Alkine / Volume, total loans and loan average (30D) @ https://flipsidecrypto.xyz/Alkine/q/volume-total-loans-and-loan-average-nx7eAw -- 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 ) ) AND succeeded = True ), reborrows_citrus as ( SELECT BLOCK_TIMESTAMP, inner_instructions[0]:instructions[1]:parsed:info:lamports / 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: Reborrow'), log_messages ) AND succeeded = True ), reborrows_sharky AS ( SELECT BLOCK_TIMESTAMP, max(AMOUNT) as amount, 'SHARKY' AS PLATFORM FROM solana.core.fact_transfers WHERE BLOCK_TIMESTAMP >= (CURRENT_DATE - interval '{{interval}}') AND BLOCK_TIMESTAMP < CURRENT_DATE AND TX_TO IN ( SELECT inner_instructions[0]:instructions[0]:parsed:info:wallet FROM solana.core.fact_transactions WHERE BLOCK_TIMESTAMP >= ( CURRENT_DATE - interval '{{interval}}' - interval '3 days' ) 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 ) AND succeeded = True ) GROUP BY tx_to, BLOCK_TIMESTAMP ), 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_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 ), 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 SELECT * FROM reborrows_sharky UNION SELECT * FROM reborrows_citrus UNION SELECT * FROM mortgages UNION SELECT * FROM frakt_pools_transfers UNION SELECT * FROM frakt_bonds UNION SELECT * FROM rainfi ) SELECT date_trunc('day', BLOCK_TIMESTAMP) as blocktime, sum(amount) as total_volume, count(*) as total_loans, platform FROM total GROUP BY platform, blocktime

3 day | All platform | (refreshing)