by BEN [SQL Query] snowflake query sql [2021.03.16 14:40:53]
WITH cte0 AS (SELECT A.USER_PSEUDO_ID, B.USER_PSEUDO_ID FROM DW.USER.D_GA_USER A LEFT JOIN DW.BEHAVE.F_GA_SESSION_SUMMARIES B ON A."USER_PSEUDO_ID" = B."USER_PSEUDO_ID"), cte1 AS (SELECT E.USER_PSEUDO_ID, D.USER_PSEUDO_ID FROM DW.USER.D_GA_USER D LEFT JOIN DW.BEHAVE.F_GA_SESSION_SUMMARIES E ON D."USER_PSEUDO_ID" = E."USER_PSEUDO_ID" AND D.CREATED_DATE = E.EVENT_DATE - INTERVAL '1 day') SELECT E.EVENT_DATE, count (DISTINCT E.EVENT_DATE)/count (DISTINCT A.CREATED_DATE) * 100 AS D1_retention_rate FROM cte0 JOIN cte1 ON E.USER_PSEUDO_ID = A.USER_PSEUDO_ID GROUP BY B.EVENT_DATE ORDER BY B.EVENT_DATE DESC LIMIT 20;
안녕하세요 초보라 아직 배울게 많습니다.
snowflake 에서 d1 리텐션 구하려고 이것저것 시도중인데
마지막 문단 join 부분에서 invalid identifier 'E.USER_PSEUDO_ID' 에러가 떠서 도저히 모르겠습니다...
예명을 주셔야 할것 같습니다..
WITH cte0 AS
(SELECT A.USER_PSEUDO_ID AS A_ID, B.USER_PSEUDO_ID AS B_ID FROM DW.USER.D_GA_USER A
LEFT JOIN DW.BEHAVE.F_GA_SESSION_SUMMARIES B ON A."USER_PSEUDO_ID" = B."USER_PSEUDO_ID"),
cte1 AS
(SELECT E.USER_PSEUDO_ID AS E_ID, D.USER_PSEUDO_ID AS D_ID FROM DW.USER.D_GA_USER D
LEFT JOIN DW.BEHAVE.F_GA_SESSION_SUMMARIES E ON D."USER_PSEUDO_ID" = E."USER_PSEUDO_ID" AND D.CREATED_DATE = E.EVENT_DATE - INTERVAL '1 day')