Aanmeldingsgebeurtenissen van gebruikers van Home Teams: naar beneden boren
WITH userlogins as (
SELECT
userid,
lastlogindate
FROM (
SELECT
userid,
lastlogindate,
lag(lastlogindate, 1, '1990-01-01') OVER (PARTITION BY userid ORDER BY begin_effective_timestamp) as previous_login
FROM users_event
)
WHERE lastlogindate != previous_login
)
SELECT
uc.hometeamid,
uc.userid,
TO_DATE(ul.lastlogindate),
count(*)
FROM users_current uc
INNER JOIN userlogins ul ON uc.userid = ul.userid
WHERE hometeamid is not null
GROUP BY uc.hometeamid, uc.userid, TO_DATE(ul.lastlogindate)
Aanmeldingsgebeurtenissen van gebruikers van teams
WITH userlogins as (
SELECT
userid,
lastlogindate
FROM (
SELECT
userid,
lastlogindate,
lag(lastlogindate, 1, '1990-01-01') OVER (PARTITION BY userid ORDER BY begin_effective_timestamp) as previous_login
FROM users_event
)
WHERE lastlogindate != previous_login
)
SELECT
tmc.teamid,
TO_DATE(ul.lastlogindate),
count(*)
FROM teammembers_current tmc
inner join teams_current tc on tc.teamid = tmc.teamid and tc.teamtype != 'PROJECT'
inner join userlogins ul on tmc.userid = ul.userid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, TO_DATE(ul.lastlogindate)
Aanmeldingsgebeurtenissen van gebruikers van teams: naar beneden boren
WITH userlogins as (
SELECT userid, lastlogindate
FROM (
SELECT userid, lastlogindate, lag(lastlogindate, 1, '1990-01-01') OVER (PARTITION BY userid ORDER BY begin_effective_timestamp) as previous_login
FROM users_event
)
WHERE lastlogindate != previous_login
)
SELECT tmc.teamid, tmc.userid, TO_DATE(ul.lastlogindate), count(*)
FROM teammembers_current tmc
INNER JOIN teams_current tc ON tc.teamid = tmc.teamid AND tc.teamtype != 'PROJECT'
INNER JOIN userlogins ul ON tmc.userid = ul.userid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, tmc.userid, TO_DATE(ul.lastlogindate)
Gebeurtenissen voor de wijziging van de taakstatus van de gebruikers in de thuisteams
WITH task_status_changes as (
SELECT
taskid,
status,
begin_effective_timestamp
FROM (
SELECT
taskid,
status,
begin_effective_timestamp,
lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
FROM tasks_event
WHERE status != 'CPL'
)
WHERE status != previous_status
)
SELECT
uc.hometeamid,
TO_DATE(tsc.begin_effective_timestamp),
count(tsc.taskid) as task_status_updates
FROM users_current uc
INNER JOIN assignments_current ac ON uc.userid = ac.assignedtoid
INNER JOIN task_status_changes tsc ON ac.taskid = tsc.taskid
WHERE hometeamid is not null
GROUP BY uc.hometeamid, TO_DATE(tsc.begin_effective_timestamp)
Taakstatuswijzigingsgebeurtenissen van gebruikers van Home Teams: naar beneden boren
WITH task_status_changes as (
SELECT
taskid,
status,
begin_effective_timestamp
FROM (
SELECT
taskid,
status,
begin_effective_timestamp,
lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
FROM tasks_event
WHERE status != 'CPL'
)
WHERE status != previous_status
)
SELECT
uc.hometeamid,
uc.userid,
TO_DATE(tsc.begin_effective_timestamp),
count(tsc.taskid) as task_status_updates
FROM users_current uc
INNER JOIN assignments_current ac ON uc.userid = ac.assignedtoid
INNER JOIN task_status_changes tsc ON ac.taskid = tsc.taskid
WHERE hometeamid is not null
GROUP BY uc.hometeamid, uc.userid, TO_DATE(tsc.begin_effective_timestamp)
Taakstatuswijzigingsgebeurtenissen van gebruikers van teams
WITH task_status_changes as (
SELECT
taskid,
status,
begin_effective_timestamp
FROM (
SELECT
taskid,
status,
begin_effective_timestamp,
lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
FROM tasks_event
WHERE status != 'CPL'
)
WHERE status != previous_status
)
SELECT tmc.teamid, TO_DATE(tsc.begin_effective_timestamp), count(tsc.taskid) as task_status_updates
FROM teammembers_current tmc
INNER JOIN teams_current tc ON tc.teamid = tmc.teamid AND tc.teamtype != 'PROJECT'
INNER JOIN assignments_current ac ON tmc.userid = ac.assignedtoid
INNER JOIN task_status_changes tsc ON ac.taskid = tsc.taskid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, TO_DATE(tsc.begin_effective_timestamp)
Taakstatuswijzigingsgebeurtenissen van gebruikers van teams: naar beneden boren
WITH task_status_changes as (
SELECT
taskid,
status,
begin_effective_timestamp
FROM (
SELECT
taskid,
status,
begin_effective_timestamp,
lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
FROM tasks_event
WHERE status != 'CPL'
)
WHERE status != previous_status
)
SELECT
tmc.teamid,
tmc.userid,
TO_DATE(tsc.begin_effective_timestamp),
count(tsc.taskid) as task_status_updates
FROM teammembers_current tmc
inner join teams_current tc ON tc.teamid = tmc.teamid AND tc.teamtype != 'PROJECT'
inner join assignments_current ac ON tmc.userid = ac.assignedtoid
inner join task_status_changes tsc ON ac.taskid = tsc.taskid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, tmc.userid, TO_DATE(tsc.begin_effective_timestamp)
Gebeurtenissen voor het voltooien van de taak van de gebruikers van de Home Teams
WITH task_status_done as (
SELECT
taskid,
status,
begin_effective_timestamp
FROM (
SELECT
taskid,
status,
begin_effective_timestamp,
lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
FROM tasks_event te
WHERE status = 'CPL'
)
WHERE status != previous_status
), task_percentage_done as (
SELECT
taskid,
percentcomplete,
begin_effective_timestamp
FROM (
SELECT
taskid,
percentcomplete,
begin_effective_timestamp,
lag(percentcomplete, 1, '0') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_percent_complete
FROM tasks_event
WHERE TO_NUMBER(percentcomplete) = 100
)
WHERE percentcomplete != previous_percent_complete
), task_completion_date_exists as (
SELECT
taskid,
actualcompletiondate
FROM tasks_event
WHERE actualcompletiondate is not null
)
SELECT
uc.hometeamid,
TO_DATE(tasks_done.task_completion_date),
count(tasks_done.taskid) as task_completed
FROM users_current uc
INNER JOIN assignments_current ac ON uc.userid = ac.assignedtoid
INNER JOIN (
SELECT distinct taskid, task_completion_date
FROM (
SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
FROM task_status_done
UNION
SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
FROM task_percentage_done
UNION
SELECT taskid, TO_DATE(actualcompletiondate) as task_completion_date
FROM task_completion_date_exists
)
) tasks_done ON ac.taskid = tasks_done.taskid
WHERE uc.hometeamid is not null
GROUP BY uc.hometeamid, TO_DATE(tasks_done.task_completion_date)
Gebeurtenissen voor het voltooien van de taak van de gebruikers van de Home Teams: naar beneden boren
WITH task_status_done as (
SELECT
taskid,
status,
begin_effective_timestamp
FROM (
SELECT
taskid,
status,
begin_effective_timestamp,
lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
FROM tasks_event te
WHERE status = 'CPL'
)
WHERE status != previous_status
), task_percentage_done as (
SELECT
taskid,
percentcomplete,
begin_effective_timestamp
FROM (
SELECT
taskid,
percentcomplete,
begin_effective_timestamp,
lag(percentcomplete, 1, '0') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_percent_complete
FROM tasks_event
WHERE TO_NUMBER(percentcomplete) = 100
)
WHERE percentcomplete != previous_percent_complete
), task_completion_date_exists as (
SELECT
taskid,
actualcompletiondate
FROM tasks_event
WHERE actualcompletiondate is not null
)
SELECT
uc.hometeamid,
uc.userid,
TO_DATE(tasks_done.task_completion_date),
count(tasks_done.taskid) as task_completed
FROM users_current uc
INNER JOIN assignments_current ac ON uc.userid = ac.assignedtoid
INNER JOIN (
SELECT distinct taskid, task_completion_date
FROM (
SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
FROM task_status_done
UNION
SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
FROM task_percentage_done
UNION
SELECT taskid, TO_DATE(actualcompletiondate) as task_completion_date
FROM task_completion_date_exists
)
) tasks_done ON ac.taskid = tasks_done.taskid
WHERE uc.hometeamid is not null
GROUP BY uc.hometeamid, uc.userid, TO_DATE(tasks_done.task_completion_date)
Taakvoltooiingsgebeurtenissen van gebruikers van teams
WITH task_status_done as (
SELECT
taskid,
status,
begin_effective_timestamp
FROM (
SELECT
taskid,
status,
begin_effective_timestamp,
lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
FROM tasks_event te
WHERE status = 'CPL'
)
WHERE status != previous_status
), task_percentage_done as (
SELECT
taskid,
percentcomplete,
begin_effective_timestamp
FROM (
SELECT
taskid,
percentcomplete,
begin_effective_timestamp,
lag(percentcomplete, 1, '0') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_percent_complete
FROM tasks_event
WHERE TO_NUMBER(percentcomplete) = 100
)
WHERE percentcomplete != previous_percent_complete
), task_completion_date_exists as (
SELECT
taskid,
actualcompletiondate
FROM tasks_event
WHERE actualcompletiondate is not null
)
SELECT
tmc.teamid,
TO_DATE(tasks_done.task_completion_date),
count(tasks_done.taskid) as task_completed
FROM teammembers_current tmc
INNER JOIN teams_current tc ON tc.teamid = tmc.teamid and tc.teamtype != 'PROJECT'
INNER JOIN assignments_current ac ON tmc.userid = ac.assignedtoid
INNER JOIN (
SELECT distinct taskid, task_completion_date
FROM (
SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
FROM task_status_done
UNION
SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
FROM task_percentage_done
UNION
SELECT taskid, TO_DATE(actualcompletiondate) as task_completion_date
FROM task_completion_date_exists
)
) tasks_done ON ac.taskid = tasks_done.taskid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, TO_DATE(tasks_done.task_completion_date)