Created
November 23, 2015 03:31
-
-
Save leonpanokarren/56f313130118dad47113 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Select | |
case | |
when session_category = 2 and y.SessionId is not null | |
then 3 | |
when session_category = 2 | |
then 2 | |
else 1 | |
end session_category | |
, count(distinct x.UserId) user_count | |
from | |
( | |
select | |
[SessionId] | |
,[UserId] | |
,case | |
when sum(case when [Action] = 'action_1' then 1 else 0 end) = count(*) | |
then 1 | |
else 2 | |
end as session_category | |
from | |
[Test].[dbo].[UserAction] (nolock) | |
where | |
[Action] in ('action_1', 'action_2') | |
group by | |
[SessionId] | |
, [UserId] | |
)x | |
left outer join | |
( | |
SELECT | |
[SessionId] | |
,[UserId] | |
FROM | |
[Test].[dbo].[UserPlay] (nolock) | |
WHERE | |
[Play] = 'I want to play more' | |
/* | |
You might not need this | |
GROUP BY if a user can choose | |
'I want to play more' only | |
once per session | |
*/ | |
GROUP BY | |
[SessionId] | |
,[UserId] | |
)y | |
on | |
x.SessionId = y.SessionId | |
and | |
x.UserId = y.UserId | |
group by | |
case | |
when session_category = 2 and y.SessionId is not null | |
then 3 | |
when session_category = 2 | |
then 2 | |
else 1 | |
end |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment