SELECT DISTINCT tx_hash,

left(input, 10) as function_sig

FROM berachain.testnet.fact_traces

WHERE to_address = LOWER('{{lp_address}}')

AND tx_succeeded = true

-- AND left(input, 10) = '0xa15112f9'

AND block_timestamp BETWEEN '2024-08-18' AND '2024-08-20'

AND function_sig IN ('0x668e2cdb', '0x23b872dd')

-- AND tx_hash = '0xbdc3c363617416aca685b067d879b0bc3e9591a40b66c3b6ae3e07dd08f5da5a'

LIMIT 100

),

token_transfers AS (

SELECT

block_timestamp,

block_number,

tx_hash,

from_address,

to_address,

left(input, 10) as function_sig,

utils.udf_hex_to_int(

CASE

WHEN to_address = LOWER('{{lp_address}}') THEN regexp_substr_all(SUBSTR(input, 11), '.{64}')[4]

ELSE COALESCE(NULLIF(regexp_substr_all(SUBSTR(input, 11), '.{64}')[2], ''), regexp_substr_all(SUBSTR(input, 11), '.{64}')[1])

END :: string

) as token_value

FROM

berachain.testnet.fact_traces

WHERE

tx_hash IN (SELECT tx_hash FROM relevant_txs)

AND to_address IN (

LOWER('{{token0}}'),

LOWER('{{token1}}'),

LOWER('{{lp_address}}')

)