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 - 90

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', 'USDC') 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 - 90

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 = '')

select wallet as "User",

count (distinct tx_hash) as "Transactions",

ROUND (SUM(amount_usd)) as "Volume USD"

FROM deposits

group by 1

order by 3 desc

limit 10