with dates as (

timestampdiff('day', '2023-06-06 16:00:00.000', current_date) as X_days_after_bedrock,

'2023-06-06 16:00:00.000' as Bedrock_live,

current_date - (2*X_days_after_bedrock) as X_days_before_bedrock

),

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,

(CASE

WHEN block_timestamp < '2023-06-06 16:00:00.000' THEN 'Before Bedrock'

when block_timestamp > '2023-06-06 16:00:00.000' then 'After Bedrock' END

) AS "Label"

FROM optimism.core.fact_event_logs

WHERE topics[0] = '0xf0179f06d5592030053869d8d0ae508420786cda1b88f04f1611215f029a35ab'

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

direction_table AS (

SELECT positionid, direction