-- forked from Lending/Borrow in Total 💲 @ https://flipsidecrypto.xyz/edit/queries/9e0df050-5003-4922-b727-548557d6d38c -- forked from Lending/Borrow by Assets @ https://flipsidecrypto.xyz/edit/queries/b3792438-b087-4e14-bd85-777dd1b8189a with priceTb as ( SELECT hour, symbol, median(price) as usd_price FROM aptos.price.ez_prices_hourly GROUP BY 1 , 2 ) , DepositTb as ( SELECT BLOCK_TIMESTAMP, TX_HASH, split((split(EVENT_TYPE, '<')[1]), '>')[0] as token_address, c.symbol, DECIMALS, split(EVENT_RESOURCE, '<')[1] as asset, EVENT_DATA:repay_only::Boolean as repay_only, EVENT_DATA:profile_name as profile_name, EVENT_DATA:receiver as receiver, EVENT_DATA:sender as sender, EVENT_DATA:deposit_amount/pow(10, DECIMALS) as deposit_amount, EVENT_DATA:repay_amount/pow(10, DECIMALS) as repay_amount, 0 as borrow_amount, 0 as withdraw_amount, (EVENT_DATA:deposit_amount/pow(10, DECIMALS))*usd_price as deposit_amount_usd, (EVENT_DATA:repay_amount/pow(10, DECIMALS))*usd_price as repay_amount_usd, 0 as borrow_amount_usd, 0 as withdraw_amount_usd SELECT TX_HASH FROM aptos.core.fact_events a --JOIN aptos.core.dim_tokens c on split((split(EVENT_TYPE, '<')[1]), '>')[0] = token_address --JOIN priceTb p on date_trunc('hour',BLOCK_TIMESTAMP) = hour and c.symbol = p.symbol -- JOIN aptos.core.fact_transactions b using(BLOCK_TIMESTAMP,TX_HASH) WHERE EVENT_ADDRESS = '0x9770fa9c725cbd97eb50b2be5f7416efdfd1f1554beb0750d4dae4c64e860da3' -- AND b.sender = '0x88d27fead5e6df32344cd0d015043429fd84ecf39c1836ff9d80e2a28d1b2866' AND EVENT_MODULE = 'controller' AND EVENT_RESOURCE ilike 'DepositEvent%' AND SUCCESS = 'true' AND TX_TYPE = 'user_transaction' LIMIT 10 GROUP by all -- UNION all -- SELECT -- BLOCK_TIMESTAMP, -- TX_HASH, -- split((split(EVENT_TYPE, '<')[1]), '>')[0] as token_address, -- symbol, -- DECIMALS, -- split(EVENT_RESOURCE, '<')[1] as asset, -- 'True' as repay_only, -- EVENT_DATA:receiver_profile_name as receiver_profile_name, -- EVENT_DATA:receiver as receiver, -- EVENT_DATA:receiver as sender, -- EVENT_DATA:deposit_amount/pow(10, DECIMALS) as deposit_amount, -- EVENT_DATA:repay_amount/pow(10, DECIMALS) as repay_amount, -- 0 as borrow_amount, -- 0 as withdraw_amount -- FROM aptos.core.fact_events a -- JOIN aptos.core.dim_tokens c on split((split(EVENT_TYPE, '<')[1]), '>')[0] = token_address -- -- JOIN aptos.core.fact_transactions b using(BLOCK_TIMESTAMP,TX_HASH) -- WHERE EVENT_ADDRESS = '0x9770fa9c725cbd97eb50b2be5f7416efdfd1f1554beb0750d4dae4c64e860da3' -- -- AND b.sender = '0x88d27fead5e6df32344cd0d015043429fd84ecf39c1836ff9d80e2a28d1b2866' -- AND EVENT_MODULE = 'controller' -- AND EVENT_RESOURCE ilike 'DepositRepayForEvent%' -- AND SUCCESS = 'true' -- AND TX_TYPE = 'user_transaction' -- GROUP by all UNION all -- ) -- ,EndFlashLoanTb as ( SELECT BLOCK_TIMESTAMP, TX_HASH, split((split(EVENT_TYPE, '<')[1]), '>')[0] as token_address, c.symbol, DECIMALS, split(EVENT_RESOURCE, '<')[1] as asset, 'True' as repay_only, EVENT_DATA:profile_name as profile_name, EVENT_DATA:user_addr as receiver, EVENT_DATA:user_addr as sender, EVENT_DATA:deposit_amount/pow(10, DECIMALS) as deposit_amount, EVENT_DATA:repay_amount/pow(10, DECIMALS) as repay_amount, 0 as borrow_amount, 0 as withdraw_amount, (EVENT_DATA:deposit_amount/pow(10, DECIMALS))*usd_price as deposit_amount_usd, (EVENT_DATA:repay_amount/pow(10, DECIMALS))*usd_price as repay_amount_usd, 0 as borrow_amount_usd, 0 as withdraw_amount_usd select tx_hash, symbol FROM aptos.core.fact_events a JOIN aptos.core.dim_tokens c on split((split(EVENT_TYPE, '<')[1]), '>')[0] = token_address --JOIN priceTb p on date_trunc('hour',BLOCK_TIMESTAMP) = hour and c.symbol = p.symbol -- JOIN aptos.core.fact_transactions b using(BLOCK_TIMESTAMP,TX_HASH) WHERE EVENT_ADDRESS = '0x9770fa9c725cbd97eb50b2be5f7416efdfd1f1554beb0750d4dae4c64e860da3' -- AND b.sender = '0x88d27fead5e6df32344cd0d015043429fd84ecf39c1836ff9d80e2a28d1b2866' AND EVENT_MODULE = 'controller' AND EVENT_RESOURCE ilike 'EndFlashLoanEvent%' AND SUCCESS = 'true' AND TX_TYPE = 'user_transaction' limit 100 GROUP by all UNION all -- ) -- , WithdrawTb as ( SELECT --a.* BLOCK_TIMESTAMP, TX_HASH, split((split(EVENT_TYPE, '<')[1]), '>')[0] as token_address, c.symbol, DECIMALS, split(EVENT_RESOURCE, '<')[1] as asset, EVENT_DATA:allow_borrow::Boolean as allow_borrow, EVENT_DATA:profile_name as profile_name, EVENT_DATA:sender as receiver, EVENT_DATA:sender as sender, 0 as deposit_amount, 0 as repay_amount, EVENT_DATA:borrow_amount/pow(10, DECIMALS) as borrow_amount, EVENT_DATA:withdraw_amount/pow(10, DECIMALS) as withdraw_amount, 0 as deposit_amount_usd, 0 as repay_amount_usd, (EVENT_DATA:borrow_amount/pow(10, DECIMALS))*usd_price as borrow_amount_usd, (EVENT_DATA:withdraw_amount/pow(10, DECIMALS))*usd_price as withdraw_amount_usd FROM aptos.core.fact_events a JOIN aptos.core.dim_tokens c on split((split(EVENT_TYPE, '<')[1]), '>')[0] = token_address JOIN priceTb p on date_trunc('hour',BLOCK_TIMESTAMP) = hour and c.symbol = p.symbol -- JOIN aptos.core.fact_transactions b using(BLOCK_TIMESTAMP,TX_HASH) WHERE EVENT_ADDRESS = '0x9770fa9c725cbd97eb50b2be5f7416efdfd1f1554beb0750d4dae4c64e860da3' -- AND b.sender = '0x88d27fead5e6df32344cd0d015043429fd84ecf39c1836ff9d80e2a28d1b2866' AND EVENT_MODULE = 'controller' AND EVENT_RESOURCE ilike 'WithdrawEvent%' AND SUCCESS = 'true' AND TX_TYPE = 'user_transaction' GROUP by all UNION all -- ) -- , BeginFlashLoanTb as ( SELECT BLOCK_TIMESTAMP, TX_HASH, split((split(EVENT_TYPE, '<')[1]), '>')[0] as token_address, c.symbol, DECIMALS, split(EVENT_RESOURCE, '<')[1] as asset, 'True' as allow_borrow, EVENT_DATA:profile_name as profile_name, EVENT_DATA:user_addr as receiver, EVENT_DATA:user_addr as sender, 0 as deposit_amount, 0 as repay_amount, EVENT_DATA:borrow_amount/pow(10, DECIMALS) as borrow_amount, EVENT_DATA:withdraw_amount/pow(10, DECIMALS) as withdraw_amount, 0 as deposit_amount_usd, 0 as repay_amount_usd, (EVENT_DATA:borrow_amount/pow(10, DECIMALS))*usd_price as borrow_amount_usd, (EVENT_DATA:withdraw_amount/pow(10, DECIMALS))*usd_price as withdraw_amount_usd FROM aptos.core.fact_events a JOIN aptos.core.dim_tokens c on split((split(EVENT_TYPE, '<')[1]), '>')[0] = token_address JOIN priceTb p on date_trunc('hour',BLOCK_TIMESTAMP) = hour and c.symbol = p.symbol -- JOIN aptos.core.fact_transactions b using(BLOCK_TIMESTAMP,TX_HASH) WHERE EVENT_ADDRESS = '0x9770fa9c725cbd97eb50b2be5f7416efdfd1f1554beb0750d4dae4c64e860da3' -- AND b.sender = '0x88d27fead5e6df32344cd0d015043429fd84ecf39c1836ff9d80e2a28d1b2866' AND EVENT_MODULE = 'controller' AND EVENT_RESOURCE ilike 'BeginFlashLoanEvent%' AND SUCCESS = 'true' AND TX_TYPE = 'user_transaction' GROUP by all ) SELECT symbol, round(sum(deposit_amount_usd),2) as "💲 Deposit Amount", round(sum(withdraw_amount_usd),2)*-1 as "💲 Withdraw Amount", "💲 Deposit Amount" + "💲 Withdraw Amount" as "💲 Net Deposited", round(sum(borrow_amount_usd),2) as "💲 Borrow Amount", round(sum(repay_amount_usd),2)*-1 as "💲 Repay Amount", "💲 Borrow Amount" + "💲 Repay Amount" as "💲 Net Borrowed", FROM DepositTb GROUP by 1

Query	Creator	Created
2023-07-23 08:58 PM aptos	amir007-Q63RX1	Jul 23, 2023
historical transaction fee base ethereum l2 evm	amir007-Q63RX1	Oct 15, 2024
dreadful-lime solana	amir007-Q63RX1	Oct 10, 2024
formidable-scarlet aptos	amir007-Q63RX1	Oct 27, 2024
living-lavender aptos	amir007-Q63RX1	Oct 20, 2024

amir007-Q63RX1