tx_hash,

ETH_FROM_ADDRESS as Contributor,

amount

FROM optimism.core.ez_eth_transfers

where ETH_TO_ADDRESS in('0xc67ce28d0343e5972f3b1711c32e206ab5a238d6', '0x824026ca57bca5c7c0b98f62e5d0173cd1630dad')

and ORIGIN_FUNCTION_SIGNATURE in ('0xa2e4f607', '0x2b2d4b68')

),

open_table AS (

block_timestamp,

tx_hash,

topics[1] AS positionid,

regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}') AS segmented_data,

CONCAT('0x', substr(topics[2] :: STRING, 27, 42)) AS user_address,

ethereum.public.udf_hex_to_int(topics[3] :: STRING) AS productid,

ethereum.public.udf_hex_to_int(segmented_data [0] :: STRING) AS direction,

avg(ethereum.public.udf_hex_to_int(segmented_data [3] :: STRING)/pow(10,8)) AS margin,

avg(ethereum.public.udf_hex_to_int(segmented_data [4] :: STRING)/pow(10,8)) AS leverage,

ethereum.public.udf_hex_to_int(segmented_data [5] :: STRING)/pow(10,8) AS fee,

ethereum.public.udf_hex_to_int(segmented_data [7] :: STRING)/pow(10,8) AS funding,

0 AS PNL,

0 AS liquidation_status,

cast('Open' AS STRING) AS type

FROM optimism.core.fact_event_logs

WHERE topics[0] = '0xf0179f06d5592030053869d8d0ae508420786cda1b88f04f1611215f029a35ab'

and tx_status = 'SUCCESS'--AND user_address = '0x39fe1beef05ded01d35c2b97e3fb6939d4f0aee3'

GROUP BY 1,2,3,4,5,6,7,10,11),

direction_table AS (

SELECT positionid, direction

FROM open_table

),