SELECT a.send_hour send_hour, a.event_hour event_hour, a.account_id account_id, a.channel_id channel_id, a.session_id session_id, a.event_type event_type, d.channelKey channel_key, d.sessionKey session_key, a.event_count event_count FROM (SELECT sendHour send_hour, INTEGER(UTC_USEC_TO_HOUR (minEventTime*1000)/1000) AS event_hour, accountId account_id, channelId channel_id, sessionId session_id, 'uniqueopen' event_type, count(1) event_count FROM (SELECT min(eventTime) AS minEventTime, messageGuid, channelId, sessionId, accountId, sendHour FROM WHERE eventType='open' AND eventTime-sendTime < 1209600000 GROUP EACH BY messageGuid, channelId, sessionId, accountId, sendHour HAVING minEventTime>=TIMESTAMP_TO_MSEC(TIMESTAMP('20140416'))) GROUP EACH BY send_hour, event_hour, account_id, channel_id, session_id) a LEFT JOIN EACH [messagebus_com_prod_ACCOUNT_DATA_DS.ACCOUNT_DETAIL] d ON d.sessionId = a.session_id AND d.channelId = a.channel_id
Demonstrate your proficiency to design, build and manage solutions on Google Cloud Platform.
Follow