SQL 데이터 탐색기

select EVENT_DATE, count(distinct USER_ID) from EVENTSwhere EVENT_DATE > CURRENT_DATE-7group by EVENT_DATEorder by EVENT_DATE desc

미션 통계

SELECT EVENT_JSON:missionID::INTEGER AS missionID,    count(distinct case when event_name = 'missionStarted' then user_id else null end) AS starts,    round((count(distinct case when event_name = 'missionFailed' then user_id else null end) /starts)*100) AS "Players failed %"FROM EVENTSWHERE missionID <=10 -- Focus on the first few levels    AND event_name in ('missionStarted','missionFailed')    AND event_date>current_date-30 -- Use a date filter for efficiencyGROUP BY missionIDORDER BY missionID

event_date>current_date-30

(mod)hash

SELECT count(distinct event_name, user_id)/count(distinct user_id) as "avg number of event types per user"FROM EVENTSWHERE event_date>current_date-7

SELECT count(distinct event_name, user_id)/count(distinct user_id) as "avg number of event types per user"FROM EVENTSWHERE event_date>current_date-7AND mod(hash(user_id),100) = 63

데이터 간소화

approximate_count_distinct

SELECT approximate_count_distinct(event_name, user_id)/ approximate_count_distinct(user_id) as "avg number of event types per user"FROM EVENTSWHERE mod(hash(user_id),100) = 63

• EVENTS
• USERS: 게임 내에서의 활동 주기 지표와 함께 각 플레이어의 정보가 단일 행에 담겨 있습니다.
• FACT_USER_SESSIONS_DAY: 각 플레이어의 세션별 데이터를 포함합니다.
• FACT_EVENT_TYPE_USERS_DAY: 플레이어가 매일 전송한 각 이벤트가 총 개수와 함께 하나의 행으로 구성됩니다.
• FACT_WAU_USERS 및 FACT_MAU_USERS: 주어진 날로부터 지난 1주일이나 한 달 동안 플레이한 사용자의 프로필 데이터를 포함합니다.

SELECT event_date, round((SUM(case when event_name='missionFailed' then number_of_events else null end) / SUM(case when event_name='missionStarted' then number_of_events else null end))*100) as overallFailRateFROM FACT_EVENT_TYPE_USERS_DAYWHERE event_name in ('missionStarted','missionFailed')AND event_date>current_date-30GROUP BY event_dateORDER BY event_date

데이터 피벗

이벤트 스트림

SELECT event_timestamp, event_name, EVENT_JSON:sessionID::STRINGFROM EVENTSWHERE client_version LIKE '0.0%'AND user_id IN (SELECT user_idFROM USERSWHERE start_date BETWEEN '2022-07-01' AND '2022-07-08'LIMIT 1 —- pull out just one user)ORDER BY event_timestamp

SQL 데이터 탐색기 쿼리 가능 테이블

EVENTS

USERS

FACT_USER_SESSIONS_DAY

FACT_EVENT_TYPE_USERS_DAY:#

FACT_WAU_USERS

FACT_MAU_USERS