Last active
July 3, 2020 12:48
-
-
Save jorpic/88cfa6efb3d6b658b05d026f13237ecf to your computer and use it in GitHub Desktop.
Пользователи, которые работают когда не в статусе Busy.
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
with | |
states_and_events as ( | |
select userid, ctime, state, null as patch from "UserState" | |
union all | |
select userid, ctime, null, patch | |
from "Event" | |
where patch::text <> '{"id":' || modelid || '}' | |
), | |
state_groups as ( | |
select s.*, count(state) over (partition by userid order by ctime) as state_grp | |
from states_and_events s | |
where ctime > '2019-05-01' and ctime < '2019-06-01' | |
), | |
events_with_states as ( | |
select userid, ctime, max(state) over (partition by userid, state_grp) as state, patch | |
from state_groups | |
) | |
select * | |
from events_with_states | |
where patch is not null | |
and state <> 'Busy' | |
order by userid, ctime; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment