select distinct token_contract,

TRUNC(TIMESTAMP,'hour') as timestamp_h,

avg(price_usd) as price_usd

from near.core.fact_prices

where timestamp >= (current_date - {{metric_days}})

group by 1,2

),

base_liquidity as (

select distinct

a.block_timestamp,

a.tx_hash,

b.tx_signer as user_address,

a.method_name,

parse_json(args) as args_parsed

from "NEAR"."CORE"."FACT_ACTIONS_EVENTS_FUNCTION_CALL" a

left join "NEAR"."CORE"."FACT_TRANSACTIONS" b

on a.tx_hash = b.tx_hash

where method_name in ('add_liquidity','remove_liquidity')

and a.block_timestamp >= (current_date - {{metric_days}})

limit 100

),

liquidity_total as (

select

a.block_timestamp,

a.tx_hash,

a.receiver_id,

b.user_address,

b.args_parsed,

b.method_name,

logs,

substring(logs[0],charindex('[',logs[0]),charindex(',',logs[0])-charindex('[',logs[0])) as token0_unclean,

substring(logs[0],charindex(',',logs[0]),charindex(']',logs[0])-charindex(',',logs[0])) as token1_unclean,

substring(token1_unclean,3) as token1_uncleaned,

substring(token0_unclean,3,charindex(' ', token0_unclean)-3) :: number as token0_amt,

substring(token0_unclean,charindex(' ', token0_unclean),len(token0_unclean)-charindex(' ', token0_unclean)) :: string as token0_name,