User Active Intervals View
- Devan Robertson
Name | UserActiveIntervals |
|---|---|
Type | View |
Description | A count of actions completed by users in 30 minute intervals. Only intervals with at least one completed action are included and these are considered active intervals. |
Version Availability | 24.02 |
Data Category | Event Field/Column Name | Data Type | Description | Applicability | Sample Value | Notes |
|---|---|---|---|---|---|---|
Key | TenantId | VARCHAR | ALWAYS | qvo-stg |
| |
Key | Tenant | VARCHAR |
| ALWAYS | Default |
|
Activity Details | PerformedByUserName | VARCHAR | Username of the user that completed the action. | ALWAYS | devan.robertson@quavo.com |
|
Activity Details | PerformedBy | VARCHAR | First initial and last name of the user that completed the action. | ALWAYS | D. Robertson |
|
Activity Details | Interval | DATE | Datetime that represents the start time of a 30 minute activity interval. | ALWAYS | 2024-11-23 00:00:00.000 |
|
Activity Details | Actions | Number | Count of actions completed during interval. | ALWAYS | 3 |
|
SQL
For greater transparency, and to provide clients with a “jumping off” point for their own queries, the SQL used to generated this view is provided below.
SELECT
a.tenantid,
t.name,
addedbyid,
CONCAT(LEFT(u.firstname, 1), '. ', u.lastname) AS PerformedBy,
IFF(MINUTE(a.createdatetime) < 30,
TO_TIMESTAMP_TZ(DATEADD(MI, 0, DATEADD(H, HOUR(a.createdatetime), TO_DATE(a.createdatetime)))::varchar || ' +0000'),
TO_TIMESTAMP_TZ(DATEADD(MI, 30, DATEADD(H, HOUR(a.createdatetime), TO_DATE(a.createdatetime)))::varchar || ' +0000')) AS "Interval",
count(*) AS "Actions"
FROM completedactions AS a
LEFT JOIN
(SELECT *,ROW_NUMBER() over (PARTITION BY tenantid, userid ORDER BY userrole) as rownum
FROM userprofile b) u ON u.tenantid = a.tenantid AND u.userid = a.addedbyid and rownum = 1
JOIN tenant AS t ON a.tenantid = t.tenantid
WHERE
addedbyid != 'System'
GROUP BY a.tenantid, t.name, addedbyid, PerformedBy, "Interval"
ORDER BY t.name, PerformedBy, "Interval" DESC
Change Log
Date | Change Summary |
|---|---|
12/11/2024 |
|
2/10/2025 | Client fields removed as this caused intervals to be duplicated when users complete actions across multiple clients. |