with pool_created as (

select

contract_address,

REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}') as segmented_data,

lower('0x' || substr(topics[1]::string, 27, 40) :: string) as token0,

lower('0x' || substr(topics[2]::string, 27, 40) :: string) as token1,

lower('0x' || substr(segmented_data[0]::string, 25, 40 ) :: string) as pool_address

from blast.core.fact_event_logs

WHERE topics[0] = '0x0d3648bd0f6ba80134a33ba9275ac585d9d315f0ad8355cddefde31afa28d0e9'

and tx_status = 'SUCCESS'

and contract_address IN ('0xb4a7d971d0adea1c73198c97d7ab3f9ce4aafa13', '0x37836821a2c03c171fb1a595767f4a16e2b93fc4') -- ThrusterFactory V2

,

swap as (

select

block_timestamp,

block_number,

origin_from_address,

origin_to_address,

ORIGIN_FUNCTION_SIGNATURE,

tx_hash,

event_index,

contract_address,

lower('0x' || substr(topics[1]::string, 27, 40) :: string) as sender, -- thruster - v2 (0.3% - 1%)

lower('0x' || substr(topics[2]::string, 27, 40) :: string) as address_to,

REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}') as segmented_data,

livequery.utils.udf_hex_to_int(segmented_data[0]::string) :: int as amount0In,

livequery.utils.udf_hex_to_int(segmented_data[1]::string) :: int as amount1In,

livequery.utils.udf_hex_to_int(segmented_data[2]::string) :: int as amount0Out,

livequery.utils.udf_hex_to_int(segmented_data[3]::string) :: int as amount1Out

from blast.core.fact_event_logs

where topics[0]::string = '0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822'

and block_timestamp::date >= '2024-02-28'

and tx_status = 'SUCCESS'