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.