질문&답변
리텐션 과제 연습문제 4번(core_event)
1000자 이내라서, 글이 일부를 지워야해서, 답변에 추가로 글을 올립니다. (퍼널을 단순화하게 된 이유에 해당되는 쿼리) WITH user_events AS ( SELECT user_pseudo_id, event_name, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_timestamp, ROW_NUMBER() OVER (PARTITION BY user_pseudo_id, event_date ORDER BY event_timestamp) AS event_order FROM advanced.app_logs ), user_paths AS ( SELECT user_pseudo_id, STRING_AGG(event_name, ' -> ' ORDER BY EVENT_ORDER) AS event_path FROM user_events GROUP BY user_pseudo_id ) SELECT DISTINCT EVENT_PATH FROM USER_PATHS WHERE EVENT_PATH LIKE '%click_payment%' WITH USER_EVENTS AS ( SELECT user_pseudo_id, event_name, event_date, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_timestamp, LAG(event_name) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS prev_event_name FROM advanced.app_logs WHERE event_name != 'screen_view' ), FILTERED_EVENTS AS ( SELECT user_pseudo_id, event_name, event_date, event_timestamp, ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS event_order FROM USER_EVENTS WHERE event_name != prev_event_name OR prev_event_name IS NULL ), USER_EVENTS_WITH_PAYMENT_ORDER AS ( SELECT *, MIN(CASE WHEN event_name = 'click_payment' THEN event_order END) OVER (PARTITION BY user_pseudo_id) AS first_payment_order FROM FILTERED_EVENTS ) , UNIQUE_EVENTS AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_pseudo_id, event_name ORDER BY event_order) AS event_rank FROM USER_EVENTS_WITH_PAYMENT_ORDER WHERE event_order ' ORDER BY event_order) AS event_path FROM UNIQUE_EVENTS WHERE event_rank = 1 GROUP BY user_pseudo_id) WHERE event_path LIKE '%click_payment%' ORDER BY user_pseudo_id) SELECT DISTINCT event_path FROM temp
- 좋아요수
- 0
- 댓글수
- 2
- 조회수
- 254