near_price as (

SELECT

TO_TIMESTAMP(value[0]::string) as days,

value[1] as price_usd

FROM (

SELECT livequery.live.udf_api(

'https://api.coingecko.com/api/v3/coins/near/market_chart?vs_currency=usd&days=max&interval=daily&precision=3') as response

),LATERAL FLATTEN (input => response:data:prices)

),

metapool_stake_v1 as (

select

receipts.block_timestamp,

receipts.tx_hash,

receipts.actions:predecessor_id as user_address,

(

receipts.actions:receipt:Action:actions[0]:FunctionCall:deposit

) * pow(10, -24) as amount

from

near.core.fact_receipts receipts

join near.core.fact_transactions transactions using (tx_hash)

where

receipts.receiver_id = 'meta-pool.near'

and receipts.actions:receipt:Action:actions[0]:FunctionCall:method_name in ('deposit_and_stake')

and receipts.status_value:Failure is null

and transactions.tx_status = 'Success'

),

metapool_add_liquidity_v1 as (

select

receipts.block_timestamp,

receipts.tx_hash,

receipts.actions:predecessor_id as user_address,

(

receipts.actions:receipt:Action:actions[0]:FunctionCall:deposit