select

distinct tx_hash,

TX_SIGNER

from near.core.fact_transactions

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

and TX:actions[0]:FunctionCall:method_name = 'add_liquidity'

),

add_liq_1 as (

select

BLOCK_TIMESTAMP,

f.TX_HASH,

t.TX_SIGNER,

RECEIVER_ID,

regexp_substr(status_value, 'Success') as reg_success,

split(logs[0], ' ') as json,

split(json[2], '"') as liq_,

liq_[1] as liq_volume,

split(json[3], '"') as token_1,

token_1[0] as pool_1,

split(json[5], '"') as token_2,

token_2[0] as pool_2

from near.core.fact_receipts f join add_txs t on f.TX_HASH = t.TX_HASH

where 1=1

and RECEIVER_ID = 'v2.ref-finance.near'

and BLOCK_TIMESTAMP >= CURRENT_DATE - 14

and reg_success is not null

),

prices as (

select

TIMESTAMP::date as date,

TOKEN_CONTRACT,

avg(PRICE_USD) as price

from near.core.fact_prices

group by 1,2

),