WITH # Firebase AnalyticsͰಠࣗʹఆٛΛ͍ͯ͠ΔΠϕϯτ(t_%)ͷΈͷநग़ͱܗ EVENT_MASTER AS ( SELECT user_id, event_timestamp as timestamp, event_name as type, max(case params.key WHEN 't_event_name' THEN params.value.string_value ELSE NULL END) AS event_name, max(case params.key WHEN 't_event_id' THEN params.value.string_value ELSE NULL END) AS event_id, max(case params.key WHEN 't_object_name' THEN params.value.string_value ELSE NULL END) AS object_name, max(case params.key WHEN 't_controller' THEN params.value.string_value ELSE NULL END) AS controller FROM `***.analytics_*****.events_*`, UNNEST(event_params) AS params WHERE user_id IS NOT NULL AND event_name LIKE 't_%' AND params.key LIKE 't_%' AND _TABLE_SUFFIX BETWEEN '20180101' AND ‘20180101' AND platform = 'IOS' GROUP BY user_id, event_timestamp, type ), # ֤Πϕϯτͷநग़ Event1 AS ( SELECT * FROM Event_Master WHERE type = 't_screen' AND controller = ‘***ViewController’ ), Event2 AS ( SELECT * FROM Event_Master WHERE type = 't_screen' AND controller = ‘***ViewController’ ), # ֤ΠϕϯτؒͰͷൃੜ࣌ؒͷൺֱɺ݅ʹద߹͢ΔΠϕϯτͷநग़ Event1_Extracted AS ( SELECT * FROM Event1 ), Event2_Extracted AS ( SELECT Event2.user_id, Event2.timestamp AS timestamp, Event2.event_name AS event_name, Event2.event_id AS event_id, Event2.object_name AS object_name, Event2.controller AS controller FROM Event1_Extracted INNER JOIN Event2 ON Event1_Extracted.user_id = Event2.user_id AND Event1_Extracted.timestamp < Event2.timestamp ), # ֤Πϕϯτͷूܭ Event1_Count AS ( SELECT 1 as index, COUNT(*) as count ϑΝωϧੳͷҝͷΫΤϦαϯϓϧ