from near.core.fact_actions_events_function_call a join near.core.fact_transactions b on a.tx_hash = b.tx_hash

where tx_receiver = 'v2.ref-finance.near'

and method_name in ('add_liquidity')

and a.block_timestamp::date >= '2023-01-01')

,

logs as ( select block_timestamp, a.tx_hash, method_name, tx_signer,

replace(value, 'EVENT_JSON:') as decode,

split(decode, ' ') as log,

log[2] as amount_1,

log[3] as token_1,

log[4] as amount_2,

log[5] as token_2

from near.core.fact_receipts a join action b on a.tx_hash = b.tx_hash, table(flatten(input =>logs))

where method_name = 'add_liquidity'

and (log[2] not ilike '%of%'

and log[2] not ilike '%removed%'

and log[2] not ilike '%from%'

and log[2] not ilike '%to%'

and log[4] not ilike '%of%'

and log[4] not ilike '%removed%'

and log[4] not ilike '%from%'

and log[4] not ilike '%to%'))

,

add_liquidity as ( select block_timestamp::date as date, tx_hash, method_name, tx_signer,

replace(amount_1,'["','') as amount_1_,

replace(token_1,'",','')::string as token_1_,

replace(amount_2,'"','') as amount_2_,

replace(token_2,'"],','')::string as token_2_

from logs

,

price as ( select timestamp::date as date, a.symbol, a.token_contract,decimals, avg(PRICE_USD) as avg_price