Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/** 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