All_burrow_txs copy

Updated 2025-01-22

-- forked from elvis / All_burrow_txs @ https://flipsidecrypto.xyz/elvis/q/Ex6z6ovLCJWp/all_burrow_txs

-- What types of user_ids are taking advantage of the existing APY?

-- Are they doing anything else within Burrow? Or is there another Near protocol that is grabbing user_ids attention?

-- Are there comparable protocols on other ecosystems that are performing better? Worse? The same?

-- Translation: which user_ids are supplying on burrow but not staking to take advantage of the APY boosts? Why? What else are they doing (in burrow or otherwise)?

-- Get some sample txs from the burrow contract: contract.main.burrow.near

-- Problems: We have no staked Frax prize > we'll just pretend it's FRAX

-- token_id: shadow_ref_v1-4179 (unknown name, symbol or price) > ¯\_(ツ)_/¯

-- token_id: NearX (unknown deciamls and price) > ¯\_(ツ)_/¯

-- LINEAR prices are not reliable in the prices table, often empty.

WITH lending_and_borrowing_txs AS (

SELECT

L.*,

CASE WHEN token_id = 'v2-nearx.stader-labs.near' THEN 'v2-nearx' ELSE C.name END as token_name,

CASE WHEN token_id = 'v2-nearx.stader-labs.near' THEN 'NearX' ELSE C.symbol END as token_symbol,

C.decimals,

amount_unadj/POW(10,CASE WHEN C.decimals > 18 THEN C.decimals ELSE 18 END) as amount_adj,

amount_adj*P.price as amount_usd

FROM(

SELECT

block_timestamp,

PARSE_JSON(clean_log)['data'][0]['amount'] as amount_unadj,

PARSE_JSON(clean_log)['data'][0]['position'] as position,

PARSE_JSON(clean_log)['data'][0]['token_id'] as token_id,

PARSE_JSON(clean_log)['event'] as action,

signer_id as user_id,

tx_hash

FROM near.core.fact_logs

WHERE receiver_id = 'contract.main.burrow.near'

AND receipt_succeeded = TRUE

AND PARSE_JSON(clean_log)['event'] IN ('deposit', 'withdraw_succeeded', 'increase_collateral', 'decrease_collateral', 'borrow', 'repay')

) L LEFT JOIN near.core.dim_ft_contract_metadata C ON L.token_id=C.contract_address

LEFT JOIN (SELECT hour, symbol, price, token_address, is_native, blockchain FROM near.price.ez_prices_hourly) -- restricting the search to a subset of the prices yields no performance improvement

QueryRunArchived: QueryRun has been archived