Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH temp3 AS (
- WITH temp1 AS (SELECT DATE(session_start_dt) AS DATE, user_id AS uuid, session_id FROM CANDIVORE.PROD.F_USER_SESSION_START
- WHERE seniority = 0 AND DATE(session_start_dt)>dateadd(dd,-30,getdate())),
- temp2 AS (SELECT * FROM CANDIVORE.PROD.F_USER_MATCH WHERE MATCH_END_DATE>dateadd(dd,-31,getdate()))
- SELECT ROW_NUMBER() OVER(PARTITION BY temp1.uuid ORDER BY match_end_date ASC) AS rn, temp1.*, user_matches.* FROM temp1
- LEFT JOIN temp2 AS user_matches ON temp1.session_id = user_matches.match_session_id
- )
- SELECT DATE, COUNT(uuid) AS new_users, COUNT(match_end_date) AS new_users_that_played, COUNT(match_end_date)/COUNT(uuid)*100 AS Percent FROM temp3
- WHERE rn = 1
- GROUP BY DATE
- ORDER BY DATE DESC
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
