select

trunc(hour, 'day') as price_date,

symbol as asset,

decimals,

avg(price) as avg_price

from near.price.ez_prices_hourly

group by 1, 2, 3

),

deposits as (

select

trunc(block_timestamp, 'day') as date,

args:amount::numeric / pow(10, p.decimals) * p.avg_price as amount_usd,

case

when predecessor_id ilike '%doge%' then 'DOGE'

when predecessor_id ilike '%usdc%' or predecessor_id='17208628f84f5d6ad33f0da3bbbeb27ffcb398eac501a31bd6ad2011e36133a1' then 'USDC'

when predecessor_id ilike '%usdt%' then 'USDT'

when predecessor_id ilike '%eth%' then 'ETH'

when predecessor_id ilike '%aurora%' then 'AURORA'

when predecessor_id ilike '%btc%' then 'BTC'

when predecessor_id ilike '%pepe%' then 'PEPE'

when predecessor_id ilike '%shib%' then 'SHIB'

when predecessor_id ilike '%link%' then 'LINK'

when predecessor_id ilike '%uni%' then 'UNI'

when predecessor_id ilike '%arb%' then 'ARB'

when predecessor_id ilike '%aave%' then 'AAVE'

when predecessor_id ilike '%gmx%' then 'GMX'

when predecessor_id ilike '%mog%' then 'MOG'

when predecessor_id ilike '%brett%' then 'BRETT'

when predecessor_id ilike '%sweat%' then 'SWEAT'

WHEN predecessor_id ilike '%:sol.%' THEN 'SOL'

WHEN predecessor_id ilike '%xrp%' THEN 'XRP'

when predecessor_id ilike '%wrap.near%' then 'NEAR'

else NULL

end as asset

from near.core.fact_actions_events_function_call d