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(clusterRepresentativeId_json, '$.uuid') AS cluster_id
    ,lookup_any_event_type(anyEventType) AS component_name
    ,lookup_inner_type(anyEventType, innerType) AS event_name
    ,componentId_json
    ,eventData
    ,anyEventType
FROM SiriSELFProcessedEvent
where eventData is not null
),
cluster_id_base as (
    SELECT
        cluster_id
        ,logic_timestamp_ns
        ,component_name
        ,event_name
        ,logic_clock_id
        ,IIF(component_name = 'UEI_CLIENT_EVENT' AND event_name = 'invocation', p_get(eventData, anyEventType, 'invocation.invocationSource'), NULL) AS invocation_source
        ,IIF(component_name = 'UEI_CLIENT_EVENT' AND event_name = 'deviceDynamicContext', p_get(eventData, anyEventType, 'deviceDynamicContext.location.latitude'), NULL) AS latitude
        ,IIF(component_name = 'UEI_CLIENT_EVENT' AND event_name = 'deviceDynamicContext', p_get(eventData, anyEventType, 'deviceDynamicContext.location.longitude'), NULL) AS longitude
        ,IIF(component_name = 'MH_CLIENT_EVENT' and event_name = 'voiceTriggerContext' and p_get(eventData, anyEventType, 'voiceTriggerContext.ended.voiceTriggerEventInfo.recognizerScore') is not null, COALESCE(p_get(eventData, anyEventType, 'voiceTriggerContext.failed.voiceTriggerEventInfo.recognizerScore'), p_get(eventData, anyEventType, 'voiceTriggerContext.ended.voiceTriggerEventInfo.recognizerScore'), p_get(eventData, anyEventType, 'voiceTriggerContext.cancelled.voiceTriggerEventInfo.recognizerScore')), NULL) AS mh_vt_recognizer_score
        ,IIF(component_name = 'MH_CLIENT_EVENT' and event_name = 'voiceTriggerContext' and p_get(eventData, anyEventType, 'voiceTriggerContext.ended.voiceTriggerEventInfo.recognizerScore') is not null, COALESCE(p_get(eventData, anyEventType, 'voiceTriggerContext.failed.voiceTriggerEventInfo.threshold'), p_get(eventData, anyEventType, 'voiceTriggerContext.ended.voiceTriggerEventInfo.threshold'), p_get(eventData, anyEventType, 'voiceTriggerContext.cancelled.voiceTriggerEventInfo.threshold')), NULL) AS mh_vt_threshold
        ,IIF(component_name = 'MH_CLIENT_EVENT' and event_name = 'voiceTriggerContext' and p_get(eventData, anyEventType, 'voiceTriggerContext.ended.voiceTriggerEventInfo.recognizerScore') is not null, COALESCE(p_get(eventData, anyEventType, 'voiceTriggerContext.failed.voiceTriggerEventInfo.satTriggered'), p_get(eventData, anyEventType, 'voiceTriggerContext.ended.voiceTriggerEventInfo.satTriggered'), p_get(eventData, anyEventType, 'voiceTriggerContext.cancelled.voiceTriggerEventInfo.satTriggered')), NULL) AS sat_triggered
        ,IIF(component_name = 'MH_CLIENT_EVENT' and event_name = 'voiceTriggerContext' and p_get(eventData, anyEventType, 'voiceTriggerContext.ended.voiceTriggerEventInfo.recognizerScore') is not null, COALESCE(p_get(eventData, anyEventType, 'voiceTriggerContext.failed.voiceTriggerEventInfo.isWakeFromSleep'), p_get(eventData, anyEventType, 'voiceTriggerContext.ended.voiceTriggerEventInfo.isWakeFromSleep'), p_get(eventData, anyEventType, 'voiceTriggerContext.cancelled.voiceTriggerEventInfo.isWakeFromSleep')), NULL) AS is_wake_from_sleep
        ,IIF(component_name = 'MH_CLIENT_EVENT' and event_name = 'voiceTriggerContext' and p_get(eventData, anyEventType, 'voiceTriggerContext.ended.voiceTriggerEventInfo.recognizerScore') is not null, COALESCE(p_get(eventData, anyEventType, 'voiceTriggerContext.failed.voiceTriggerEventInfo.isMediaPlaying'), p_get(eventData, anyEventType, 'voiceTriggerContext.ended.voiceTriggerEventInfo.isMediaPlaying'), p_get(eventData, anyEventType, 'voiceTriggerContext.cancelled.voiceTriggerEventInfo.isMediaPlaying')), NULL) AS is_media_playing
        ,IIF(component_name = 'MH_CLIENT_EVENT' and event_name = 'voiceTriggerContext' and p_get(eventData, anyEventType, 'voiceTriggerContext.ended.voiceTriggerEventInfo.recognizerScore') is not null, COALESCE(p_get(eventData, anyEventType, 'voiceTriggerContext.failed.voiceTriggerEventInfo.triggerPhrase'), p_get(eventData, anyEventType, 'voiceTriggerContext.ended.voiceTriggerEventInfo.triggerPhrase'), p_get(eventData, anyEventType, 'voiceTriggerContext.cancelled.voiceTriggerEventInfo.triggerPhrase')), NULL) AS trigger_phrase
        ,IIF(component_name = 'MH_CLIENT_EVENT' and event_name = 'voiceTriggerContext' and p_get(eventData, anyEventType, 'voiceTriggerContext.ended.voiceTriggerEventInfo.recognizerScore') is not null, COALESCE(p_get(eventData, anyEventType, 'voiceTriggerContext.failed.voiceTriggerEventInfo.deviceHandheld'), p_get(eventData, anyEventType, 'voiceTriggerContext.ended.voiceTriggerEventInfo.deviceHandheld'), p_get(eventData, anyEventType, 'voiceTriggerContext.cancelled.voiceTriggerEventInfo.deviceHandheld')), NULL) AS is_device_handheld
        ,IIF(component_name = 'MH_CLIENT_EVENT' AND event_name = 'attendingContext', 1, 0) AS mh_ac_count
        ,IIF(component_name = 'MH_CLIENT_EVENT' AND event_name = 'assistantDaemonAudioRecordingContext', p_get(eventData, anyEventType, 'assistantDaemonAudioRecordingContext.startedOrChanged.source'), NULL) AS audio_source
        ,IIF(component_name = 'MH_CLIENT_EVENT' AND event_name = 'assistantDaemonAudioRecordingContext', p_get(eventData, anyEventType, 'assistantDaemonAudioRecordingContext.startedOrChanged.audioInterfaceVendorId'), NULL) AS audio_interface_vendor_id
        ,IIF(component_name = 'MH_CLIENT_EVENT' AND event_name = 'assistantDaemonAudioRecordingContext', p_get(eventData, anyEventType, 'assistantDaemonAudioRecordingContext.startedOrChanged.audioInterfaceProductId'), NULL) AS audio_interface_product_id
        ,IIF(component_name = 'MH_CLIENT_EVENT' AND event_name = 'asrAudioConfigureStarted', p_get(eventData, anyEventType, 'asrAudioConfigureStarted.audioCodec'), NULL) AS audio_codec
        ,IIF(component_name = 'MH_CLIENT_EVENT' AND event_name = 'asrAudioConfigureStarted', p_get(eventData, anyEventType, 'asrAudioConfigureStarted.audioSkippedNumSamples'), NULL) AS audio_skipped_num_samples
        ,IIF(component_name = 'ASR_CLIENT_EVENT' AND event_name = 'requestContext', p_get(eventData, anyEventType, 'requestContext.startedOrChanged.task'), NULL) AS asr_task
        ,IIF(component_name = 'ASR_CLIENT_EVENT' AND event_name = 'intermediateUtteranceInfoTier1', p_get(eventData, anyEventType, 'intermediateUtteranceInfoTier1.unrepairedPostItn'), NULL) AS post_itn_1best
        ,IIF(component_name = 'ORCH_CLIENT_EVENT' AND event_name = 'requestContext', p_get(eventData, anyEventType, 'requestContext.cancelled.reason'), NULL) AS orch_cancelled_reason
        ,IIF(component_name = 'ORCH_CLIENT_EVENT' AND event_name = 'requestContext', p_get(eventData, anyEventType, 'requestContext.ended.exists'), NULL) AS orch_ended
        ,IIF(component_name = 'ORCH_CLIENT_EVENT' AND event_name = 'orchDeviceDynamicContext', p_get(eventData, anyEventType, 'orchDeviceDynamicContext.motionActivity'), NULL) AS motion_activity
        ,IIF(component_name = 'ORCH_CLIENT_EVENT' AND event_name = 'requestContext', 1, 0) AS orch_count
        ,IIF(component_name = 'FLOW_CLIENT_EVENT' AND event_name = 'flowStep', p_get(eventData, anyEventType, 'flowStep.appContext.bundleId'), NULL) AS bundle_id
        ,IIF(component_name = 'FLOW_CLIENT_EVENT' AND event_name = 'flowStep', p_get(eventData, anyEventType, 'flowStep.flowState.currentTaskName'), NULL) AS current_task_name
        ,IIF(component_name = 'FLOW_CLIENT_EVENT' AND event_name = 'flowStep', p_get(eventData, anyEventType, 'flowStep.domainContexts[0].informationPluginContext.executedPegasusDomain'), NULL) AS executed_pegasus_domain
        ,IIF(component_name='FLOW_CLIENT_EVENT' AND event_name='flowStep', p_get(eventData, anyEventType, 'flowStep.flowState.flowStateType'), NULL) AS flow_state_type
        ,IIF(component_name = 'CNV_CLIENT_EVENT' AND event_name = 'intentFinalExecutionContext', p_get(eventData, anyEventType, 'intentFinalExecutionContext.startedOrChanged.plugin'), NULL) AS cnv_plugin
        ,IIF(component_name='UEI_CLIENT_EVENT' AND event_name='invocation', p_get(eventData, anyEventType, 'invocation.carPlayInvocationContext.directAction'), NULL) AS uei_direct_action_type
        ,IIF(component_name='UEI_CLIENT_EVENT' AND event_name='invocation', p_get(eventData, anyEventType, 'invocation.keyboardInvocationContext.keyboardPresented'), NULL) IS NOT NULL AS uei_is_keyboard_presented
    FROM base_table
    WHERE cluster_id is not null
),
cluster_id_agg as (
    select
        cluster_id as request_id,
        MAX(logic_clock_id) AS logic_clock_id,
        MIN(logic_timestamp_ns) AS min_event_ns,
        max(invocation_source) as invocation_source,
        max(orch_cancelled_reason) as orch_cancelled_reason,
        max(motion_activity) as motion_activity,
        max(audio_interface_vendor_id) as audio_interface_vendor_id,
        max(audio_interface_product_id) as audio_interface_product_id,
        max(mh_vt_recognizer_score) as mh_vt_recognizer_score,
        max(mh_vt_threshold) as mh_vt_threshold,
        max(sat_triggered) as sat_triggered,
        max(trigger_phrase) as trigger_phrase,
        max(is_wake_from_sleep) as is_wake_from_sleep,
        max(is_media_playing) as is_media_playing,
        max(is_device_handheld) as is_device_handheld,
        max(audio_codec) as audio_codec,
        max(audio_source) as audio_source,
        max(audio_skipped_num_samples) as audio_skipped_num_samples,
        sum(mh_ac_count) as mh_ac_count,
        sum(orch_count) as orch_count,
        max(post_itn_1best) as post_itn_1best,
        max(asr_task) as asr_task,
        max(bundle_id) as bundle_id,
        max(current_task_name) as current_task_name,
        max(executed_pegasus_domain) as executed_pegasus_domain,
        max(cnv_plugin) as cnv_plugin,
        max(latitude) as latitude,
        max(longitude) as longitude,
        max(uei_direct_action_type) as uei_direct_action_type,
        max(flow_state_type) as flow_state_type,
        MAX(invocation_source='INVOCATIONSOURCE_CARD_SECTION' AND uei_is_keyboard_presented) AS is_interactive_snippet,
        MAX(IIF(audio_interface_vendor_id='76', audio_interface_product_id, NULL)) AS first_party_audio_interface_product_id
    from
        cluster_id_base
    group by 1
),
global_dim_agg AS (
    SELECT
        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
        ,MAX(IIF(event_name='deviceFixedContext', p_get(eventData, anyEventType, 'deviceFixedContext.deviceType'), NULL)) AS device_type
        ,MAX(IIF(event_name='deviceFixedContext', p_get(eventData, anyEventType, 'deviceFixedContext.systemBuild'), NULL)) AS system_build
        ,MAX(IIF(event_name='deviceFixedContext', p_get(eventData, anyEventType, 'deviceFixedContext.programCode'), NULL)) AS program_code
        ,MAX(IIF(event_name='deviceFixedContext', p_get(eventData, anyEventType, 'deviceFixedContext.dataSharingOptInState'), NULL)) AS data_sharing_opt_in_state
        ,MAX(IIF(event_name='deviceFixedContext', p_get(eventData, anyEventType, 'deviceFixedContext.siriInputLocale'), NULL)) AS siri_input_locale
        ,MAX(IIF(event_name='siriAccountInformation', p_get(eventData, anyEventType, 'siriAccountInformation.siriDeviceId'), NULL)) AS assistant_id
        ,MAX(IIF(event_name='ephemeralToAggregationIdentifierMap', p_get(eventData, anyEventType, 'ephemeralToAggregationIdentifierMap.deviceAggregationId.value'), NULL)) AS device_aggregation_id
        ,MAX(IIF(event_name='ephemeralToAggregationIdentifierMap', p_get(eventData, anyEventType, 'ephemeralToAggregationIdentifierMap.userAggregationIdRotationTimestampMs'), NULL)) AS user_aggregation_id_rotation_timestamp_ms
        ,MAX(IIF(event_name='ephemeralToAggregationIdentifierMap', p_get(eventData, anyEventType, 'ephemeralToAggregationIdentifierMap.userAggregationIdExpirationTimestampMs'), NULL)) AS user_aggregation_id_expiration_timestamp_ms
    FROM base_table
    WHERE component_name = 'DIM_CLIENT_EVENT'
    GROUP BY logic_clock_id
)
select
    c.*,
    COALESCE(uei_direct_action_type,cnv_plugin,flow_state_type,executed_pegasus_domain,IIF(is_interactive_snippet, 'CARD_SECTION_INTERACTION_TURN', NULL)) AS sub_domain,
    device_aggregation_id,
    time_interval_since1970,
    system_build,
    siri_input_locale,
    device_type
from
    cluster_id_agg c left outer join global_dim_agg g on c.logic_clock_id = g.logic_clock_id
where
    orch_count > 0