Skip to content

Instantly share code, notes, and snippets.

@YiLi225
Last active November 28, 2020 23:09
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save YiLi225/72229ec8928361271c9487b710c751e7 to your computer and use it in GitHub Desktop.
Save YiLi225/72229ec8928361271c9487b710c751e7 to your computer and use it in GitHub Desktop.
/** 2.2 Return more information about login records -- PARTITION BY **/
SELECT *
FROM
(
SELECT
dat1.*,
SUM(CASE WHEN location_var = 'NYC' THEN 1 END) OVER (PARTITION BY id_var) AS nyc_cnt,
SUM(CASE WHEN location_var = 'Illinois' THEN 1 END) OVER (PARTITION BY id_var) AS illinois_cnt
FROM
userlogin dat1
) dat2
WHERE
nyc_cnt > 0
AND illinois_cnt > 0
AND location_var IN ('NYC', 'Illinois')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment