How to find the robot count per folder per tenant

Query to find robot count per folder per tenant

Resolution

The SQL query is structured using Common Table Expressions (CTEs) to retrieve the number of unique robots associated with organization units under active tenants. Here’s a breakdown of each part:

WITH TenantCTE AS (

SELECT ID AS TenantID, Name AS TenantName

FROM dbo.Tenants t

WHERE IsDeleted = 0

),

OrgUnitCTE AS (

SELECT ou.Id, ou.TenantID, ou.DisplayName AS FolderName

FROM dbo.OrganizationUnits ou

JOIN TenantCTE t ON ou.TenantID = t.TenantID

WHERE ou.IsDeleted = 0

),

UserOrgUnitCTE AS (

SELECT DISTINCT uou.UserID, uou.OrganizationUnitID

FROM dbo.UserOrganizationUnits uou

JOIN OrgUnitCTE ou ON uou.OrganizationUnitID = ou.Id

WHERE uou.IsDeleted = 0

),

RobotCountCTE AS (

SELECT uou.OrganizationUnitID, COUNT(DISTINCT r.UserID) AS RobotCount

FROM UserOrgUnitCTE uou

JOIN dbo.Robots r ON uou.UserID = r.UserID

WHERE r.IsDeleted = 0

GROUP BY uou.OrganizationUnitID

)

SELECT

t.TenantName,

ou.FolderName,

rc.RobotCount

FROM RobotCountCTE rc

JOIN OrgUnitCTE ou ON rc.OrganizationUnitID = ou.Id

JOIN TenantCTE t ON ou.TenantID = t.TenantID;

Query output

TenantCTE

WITH TenantCTE AS (

SELECT ID AS TenantID, Name AS TenantName

FROM dbo.Tenants t

WHERE IsDeleted = 0

)

Purpose: This CTE selects active tenants (i.e., those that have not been marked as deleted).

Output: It provides a list of tenant IDs and their corresponding names.

OrgUnitCTE

OrgUnitCTE AS (

SELECT ou.Id, ou.TenantID, ou.DisplayName AS FolderName

FROM dbo.OrganizationUnits ou

JOIN TenantCTE t ON ou.TenantID = t.TenantID

WHERE ou.IsDeleted = 0

)

Purpose: This CTE retrieves active organization units that belong to the active tenants identified in the previous CTE.

Output: It provides the IDs, tenant IDs, and display names of the organization units.

UserOrgUnitCTE

UserOrgUnitCTE AS (

SELECT DISTINCT uou.UserID, uou.OrganizationUnitID

FROM dbo.UserOrganizationUnits uou

JOIN OrgUnitCTE ou ON uou.OrganizationUnitID = ou.Id

WHERE uou.IsDeleted = 0

)

Purpose: This CTE finds unique associations between users and organization units, filtering out any deleted entries.

Output: It produces a distinct list of user IDs along with their associated organization unit IDs.

RobotCountCTE

RobotCountCTE AS (

SELECT uou.OrganizationUnitID, COUNT(DISTINCT r.UserID) AS RobotCount

FROM UserOrgUnitCTE uou

JOIN dbo.Robots r ON uou.UserID = r.UserID

WHERE r.IsDeleted = 0

GROUP BY uou.OrganizationUnitID

)

Purpose: This CTE counts the unique users (referred to as "robots") linked to each organization unit, again filtering out deleted robots.

Output: It generates a count of distinct users (robots) for each organization unit.

Final SELECT Statement

SELECT

t.TenantName,

ou.FolderName,

rc.RobotCount

FROM RobotCountCTE rc

JOIN OrgUnitCTE ou ON rc.OrganizationUnitID = ou.Id

JOIN TenantCTE t ON ou.TenantID = t.TenantID;

Purpose: This final query combines the results from the previous CTEs to produce a consolidated output.

Output: It selects the tenant name, folder (organization unit) name, and the count of robots associated with each folder for active tenants.

Summary

Overall, this query allows you to understand how many unique robots (users) are associated with each active organization unit under active tenants, providing valuable insights into user engagement across different organizational divisions.