TIL 23

가장 많이 사용된 채널

SELECT
		channel,
		COUNT(1) AS session_count,
		COUNT(DISTINCT userId) AS user_count,
FROM raw_data.user_session_count
GROUP BY 1
ORDER BY 2 DESC;

가장 많은 세션을 만들어낸 사용자 ID

SELECT
		userId,
		COUNT(*) as count
FROM raw_data.user_session_count
GROUP BY userId
ORDER BY count DESC
LIMIT 1;

월별 유니크한 사용자 수

SELECT
		TO_CHAR(t.ts, 'YYYY-MM') AS month,
		COUNT(DISTINCT s.userId) AS mau
FROM raw_data.user_session_count s
		JOIN raw_data.session_timestamp t
		ON s.sessionId = t.sessionId
GROUP BY 1
ORDER BY 1 DESC;

• TO_CHAR(ts, ‘YYYY-MM’)
• LEFT(ts, 7)
• DATE_TRUNC(’month’, ts)
  • month 이후 값은(day) 1이 return된다.
• SUBSTRING(ts, 1, 7)

월별 채널별 유니크한 사용자 수

SELECT
		TO_CHAR(t.ts, 'YYYY-MM') AS month,
		s.channel
		COUNT(DISTINCT s.userId) AS mau
FROM raw_data.user_session_count s
		JOIN raw_data.session_timestamp t
		ON s.sessionId = t.sessionId
GROUP BY 1, 2
ORDER BY 1 DESC;

CTAS(Create Table As Select)

SELECT로 테이블 생성하는 방법

CTE(Common Table Expression)

• 임시 결과 집합을 정의하는데 사용되는 문법
• 서브쿼리의 대안으로 사용될 수 있다.
• 쿼리 내에서 여러번 재사용될 수 있다.
• 재귀적으로 CTE를 사용하면 자기 자신을 참조하여 데이터를 조회할 수있다.

항상 시도해봐야 하는 데이터 품질 확인 방법

1. 중복된 레코드 체크하기

   SELECT COUNT(1)
   FROM session_summary;
   
   SELECT COUNT(1)
   FROM (
   		SELECT DISTINCT userId, sessionId, ts, channel
   		FROM session_summary
   );

   With ds AS (
   	 SELECT DISTINCT userId, sessionId, ts, channel
   	 FROM session_summary
   )
   SELECT COUNT(1)
   FROM ds;

2. 최신 데이터의 존재 여부 체크하기

   SELECT MIN(ts), MAX(ts)
   FROM session_summary;

3. Primary Key Uniqueness가 지켜지는지 체크하기

   SELECT sessionId, COUNT(1)
   FROM session_summary
   GROUP BY 1
   ORDER BY 2 DESC
   LIMIT 1;

4. 값이 비어있는 칼럼들이 있는지 체크하기

   SELECT
   		COUNT(CASE WHEN sessionId is NULL THEN 1 END) sessionid_null_count,
   		COUNT(CASE WHEN userId is NULL THEN 1 END) userid_null_count,
   		COUNT(CASE WHEN ts is NULL THEN 1 END) ts_null_count,
   		COUNT(CASE WHEN channel is NULL THEN 1 END) channel_null_count
   FROM session_summary;