with prices as ( select token_contract, case when token_contract = 'aurora' then 'WETH' else symbol end as symbol, avg(price_usd) AS price from near.core.fact_prices where timestamp::date >= CURRENT_DATE - 14 and token_contract in ('usn','aurora','wrap.near','meta-pool.near','token.burrow.near','linear-protocol.near','6b175474e89094c44da98b954eedeac495271d0f.factory.bridge.near','a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.factory.bridge.near','dac17f958d2ee523a2206206994597c13d831ec7.factory.bridge.near','2260fac5e5542a773aa44fbcfedf7c193bc2c599.factory.bridge.near','aaaaaa20d9e0e2461697782ef11675f668207961.factory.bridge.near') group by token_contract, symbol), deposits as ( select transactions.tx_hash, transactions.tx_signer AS wallet, prices.symbol as token, case when token in ('NEAR', 'stNEAR','STNEAR', 'LiNEAR','LINEAR','wNEAR') then (TRY_PARSE_JSON(calls.args):amount / POW(10, 24)) when token in ('WETH', 'DAI', 'USN', 'AURORA') then (TRY_PARSE_JSON(calls.args):amount / POW(10, 18)) when token in ('wBTC','WBTC') then (TRY_PARSE_JSON(calls.args):amount / POW(10, 8)) when token in ('USDT.e', 'USDC.e') then (TRY_PARSE_JSON(calls.args):amount / POW(10, 6)) else TRY_PARSE_JSON(calls.args):amount end as amount, (amount * prices.price) AS amount_usd from near.core.fact_transactions transactions join near.core.fact_actions_events_function_call calls ON transactions.tx_hash = calls.tx_hash join prices ON transactions.tx_receiver = prices.token_contract where transactions.block_timestamp::date >= CURRENT_DATE - 14 and left(SPLIT_PART(transactions.tx:receipt[0]:outcome:status, '"', 2), 7) = 'Success' and calls.method_name = 'ft_transfer_call' and TRY_PARSE_JSON(calls.args):receiver_id = 'contract.main.burrow.near' and TRY_PARSE_JSON(calls.args):msg = ''), borrows as ( select transactions.tx_hash, transactions.tx_signer as wallet, prices.symbol as token, case when token in ('NEAR', 'stNEAR','STNEAR', 'LiNEAR','LINEAR','wNEAR') then (TRY_PARSE_JSON(TRY_PARSE_JSON(calls.args):msg):Execute:actions[0]:Borrow:amount / POW(10, 24)) when token in ('WETH', 'DAI', 'USN', 'AURORA', 'USDC.e', 'USDT.e', 'wBTC','WBTC') then (TRY_PARSE_JSON(TRY_PARSE_JSON(calls.args):msg):Execute:actions[0]:Borrow:amount / POW(10, 18)) else TRY_PARSE_JSON(TRY_PARSE_JSON(calls.args):msg):Execute:actions[0]:Borrow:amount end as amount, (amount * prices.price) AS amount_usd from near.core.fact_transactions transactions join near.core.fact_actions_events_function_call calls ON transactions.tx_hash = calls.tx_hash join prices ON TRY_PARSE_JSON(TRY_PARSE_JSON(calls.args):msg):Execute:actions[0]:Borrow:token_id = prices.token_contract where transactions.block_timestamp::date >= CURRENT_DATE - 14 and left(SPLIT_PART(transactions.tx:receipt[0]:outcome:status, '"', 2), 7) = 'Success' and left(SPLIT_PART(transactions.tx:receipt[1]:outcome:status, '"', 2), 7) = 'Success' and transactions.tx_receiver = 'priceoracle.near' and calls.method_name = 'oracle_call' and TRY_PARSE_JSON(calls.args):receiver_id = 'contract.main.burrow.near' and left(SPLIT_PART(TRY_PARSE_JSON(TRY_PARSE_JSON(calls.args):msg):Execute:actions[0], '"', 2), 6) = 'Borrow'), repays as ( select transactions.tx_hash, transactions.tx_signer as wallet, prices.symbol as token, case when token in ('NEAR', 'stNEAR','STNEAR', 'LiNEAR','LINEAR','wNEAR') then (TRY_PARSE_JSON(calls.args):amount / POW(10, 24)) when token in ('WETH', 'DAI', 'USN', 'AURORA') then (TRY_PARSE_JSON(calls.args):amount / POW(10, 18)) when token in ('wBTC','WBTC') then (TRY_PARSE_JSON(calls.args):amount / POW(10, 8)) when token in ('USDT.e', 'USDC.e') then (TRY_PARSE_JSON(calls.args):amount / POW(10, 6)) else TRY_PARSE_JSON(calls.args):amount end as amount, (amount * prices.price) AS amount_usd from near.core.fact_transactions transactions join near.core.fact_actions_events_function_call calls ON transactions.tx_hash = calls.tx_hash join prices ON transactions.tx_receiver = prices.token_contract where transactions.block_timestamp::date >= CURRENT_DATE - 14 and left(SPLIT_PART(transactions.tx:receipt[0]:outcome:status, '"', 2), 7) = 'Success' and calls.method_name = 'ft_transfer_call' and TRY_PARSE_JSON(calls.args):receiver_id = 'contract.main.burrow.near' and left(SPLIT_PART(TRY_PARSE_JSON(TRY_PARSE_JSON(calls.args):msg):Execute:actions[0], '"', 2), 5) = 'Repay'), withdraws as ( select transactions.tx_hash, transactions.tx_signer AS wallet, prices.symbol AS token, case when token IN ('NEAR', 'stNEAR','STNEAR', 'LiNEAR','LINEAR','wNEAR') then (TRY_PARSE_JSON(REPLACE(transactions.tx:receipt[0]:outcome:logs[0], 'EVENT_JSON:', '')):data[0]:amount / POW(10, 24)) when token IN ('WETH', 'DAI', 'USN', 'AURORA', 'USDT.e', 'USDC.e', 'wBTC','WBTC') then (TRY_PARSE_JSON(REPLACE(transactions.tx:receipt[0]:outcome:logs[0], 'EVENT_JSON:', '')):data[0]:amount / POW(10, 18)) else TRY_PARSE_JSON(REPLACE(transactions.tx:receipt[0]:outcome:logs[0], 'EVENT_JSON:', '')):data[0]:amount end AS amount, (amount * prices.price) AS amount_usd from near.core.fact_transactions transactions join near.core.fact_actions_events_function_call calls on transactions.tx_hash = calls.tx_hash join prices ON TRY_PARSE_JSON(REPLACE(transactions.tx:receipt[0]:outcome:logs[0], 'EVENT_JSON:', '')):data[0]:token_id = prices.token_contract where transactions.block_timestamp::date >= CURRENT_DATE - 14 and left(SPLIT_PART(transactions.tx:receipt[0]:outcome:status, '"', 2), 7) = 'Success' and calls.method_name = 'execute' and transactions.tx_receiver = 'contract.main.burrow.near' and TRY_PARSE_JSON(REPLACE(transactions.tx:receipt[0]:outcome:logs[0], 'EVENT_JSON:', '')):event = 'withdraw_started') select 'Deposit' AS "Action", case when amount_usd < 10 then 'Less Than $10' when amount_usd >= 10 and amount_usd < 100 then '$10 - $100' when amount_usd >= 100 and amount_usd < 1000 then '$100 - $1000' when amount_usd >= 1000 and amount_usd < 10000 then '$1000 - $10000' else 'More Than $10000' end as type, count (distinct tx_hash) AS "Transactions" from deposits group by 1,2 UNION select 'Borrow' AS "Action", case when amount_usd < 10 then 'Less Than $10' when amount_usd >= 10 and amount_usd < 100 then '$10 - $100' when amount_usd >= 100 and amount_usd < 1000 then '$100 - $1000' when amount_usd >= 1000 and amount_usd < 10000 then '$1000 - $10000' else 'More Than $10000' end as type, count (distinct tx_hash) AS "Transactions" from borrows group by 1,2 UNION select 'Repay' AS "Action", case when amount_usd < 10 then 'Less Than $10' when amount_usd >= 10 and amount_usd < 100 then '$10 - $100' when amount_usd >= 100 and amount_usd < 1000 then '$100 - $1000' when amount_usd >= 1000 and amount_usd < 10000 then '$1000 - $10000' else 'More Than $10000' end as type, count (distinct tx_hash) AS "Transactions" from repays group by 1,2 UNION select 'Withdraw' AS "Action", case when amount_usd < 10 then 'Less Than $10' when amount_usd >= 10 and amount_usd < 100 then '$10 - $100' when amount_usd >= 100 and amount_usd < 1000 then '$100 - $1000' when amount_usd >= 1000 and amount_usd < 10000 then '$1000 - $10000' else 'More Than $10000' end as type, count (distinct tx_hash) AS "Transactions" from withdraws group by 1,2

NEAR - 13. Insight of the Week