select

block_id,

tx_hash,

method_name,

args:pool_id as pool_id

from near.core.fact_actions_events_function_call

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

and block_timestamp::date >= '2021-08-21'

,

dex_liquidity as (

select

a.block_id,

a.block_timestamp,

a.tx_hash,

RECEIPT_OBJECT_ID,

RECEIVER_ID as platform,

signer_id as from_address,

b.method_name,

b.pool_id,

split(REPLACE(REGEXP_SUBSTR(clean_log, '"([^"]+)"', 1, 1), '"', ''),' ')[0] :: int AS amount0,

split(REPLACE(REGEXP_SUBSTR(clean_log, '"([^"]+)"', 1, 1), '"', ''),' ')[1] :: string AS token0,

split(REPLACE(REGEXP_SUBSTR(clean_log, '"([^"]+)"', 1, 2), '"', ''),' ')[0] :: int AS amount1,

split(REPLACE(REGEXP_SUBSTR(clean_log, '"([^"]+)"', 1, 2), '"', ''),' ')[1] :: string AS token1,

case when b.method_name = 'add_liquidity' then split(split(clean_log,',')[2],' ')[2]

when b.method_name = 'remove_liquidity' then split(clean_log, ' ')[0]

end as lp_amount_raw

from near.core.fact_logs a

join all_liquidity_action b using(tx_hash, block_id)

where not pool_id is null

and not pool_id ilike '%near%'

and block_timestamp::date >= '2021-08-21'

,