With base_table AS (
SELECT
    json_extract(logicalTimestamp_json, '$.clockIdentifier') AS logic_clock_id
    ,json_extract(logicalTimestamp_json, '$.timestampInNanoseconds') AS logic_timestamp_ns
    ,json_extract(COALESCE(clusterRepresentativeId_json, componentId_json), '$.uuid') AS cluster_id
    ,lookup_component_name(json_extract(COALESCE(clusterRepresentativeId_json, componentId_json), '$.componentName')) AS cluster_component_name
    ,lookup_any_event_type(anyEventType) AS component_name
    ,lookup_inner_type(anyEventType, innerType) AS event_name
    ,anyEventType
    ,eventData
    ,componentId_json
FROM SiriSELFProcessedEvent
),

global_dim_agg AS (
SELECT
    logic_clock_id AS dfc_logic_clock_id
    ,MIN(logic_timestamp_ns) AS min_clock_ns
    ,MIN(IIF(event_name='deviceFixedContext', p_get(eventData, anyEventType, 'deviceFixedContext.timeIntervalSince1970'), NULL)) AS time_interval_since1970
FROM base_table
WHERE component_name='DIM_CLIENT_EVENT'
AND event_name='deviceFixedContext'
GROUP BY dfc_logic_clock_id
),

event_clock_id_agg AS (
SELECT
    logic_clock_id AS event_logic_clock_id
    ,MIN(logic_timestamp_ns) AS min_event_ns
FROM base_table
WHERE component_name = 'UEI_CLIENT_EVENT'
AND event_name = 'invocation'
GROUP BY logic_clock_id
),

join_table AS (
SELECT *
FROM event_clock_id_agg
LEFT JOIN global_dim_agg
ON event_clock_id_agg.event_logic_clock_id = global_dim_agg.dfc_logic_clock_id
)

SELECT
    event_logic_clock_id AS original_clock_id
    ,date(time_interval_since1970 + min_event_ns / 1000000000 - min_clock_ns / 1000000000, 'unixepoch') AS event_date
FROM join_table