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 BETWEEN (CURRENT_DATE - 31) AND (CURRENT_DATE - 1)

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.block_timestamp::date AS date,

transactions.tx_hash,

transactions.tx_signer AS wallet,

prices.symbol AS token,

CASE

WHEN token IN ('NEAR', 'stNEAR', 'LiNEAR') 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))