Activity by team queries

Last update: Tue Mar 11 2025 00:00:00 GMT+0000 (Coordinated Universal Time)

Topics:
Reports and Dashboards

CREATED FOR:

User

You can use the queries in this article to create data visualizations similar to those in Enhanced Analytics.

IMPORTANT

Queries will produce similar results to those shown in Enhanced Analytics, but they may not match exactly.

Prerequisites

Before you begin, you must

Establish a connection with your Business Intelligence (BI) tool:
1. Create a reader account or connection for Snowflake
2. Establish a connection to Workfront Data Connect

Once you establish a connection, you can use the queries in this article to extract and visualize data.

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,
    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, TO_DATE(ul.lastlogindate)

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)

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)

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)

Home Teams’ users’ task status change events

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)

Home Teams’ users’ task status change events: drill down

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)

Teams’ users’ task status change events

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)

Teams’ users’ task status change events: drill down

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)

Home Teams’ users’ task completion events

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)

Home Teams’ users’ task completion events: drill down

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)

Teams’ users’ task completion events

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)

Teams’ users’ task completion events: drill down

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,
    tmc.userid,
    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, tmc.userid, TO_DATE(tasks_done.task_completion_date)

recommendation-more-help

5f00cc6b-2202-40d6-bcd0-3ee0c2316b43