Which four Transact-SQL segments should you use to deve…

Given answer is wrong! The “WITH” has always to be at the beginning of the first statement.

In my opinion the answer should be like the code example given below.

IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘tblUsersRoles’)
BEGIN
DROP TABLE dbo.tblUsersRoles;
END;
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘tblRoles’)
BEGIN
DROP TABLE dbo.tblRoles;
END;
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘tblUsers’)
BEGIN
DROP TABLE dbo.tblUsers;
END;

CREATE TABLE tblRoles
(
RoleId INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
,RoleName VARCHAR(20) NOT NULL
);

INSERT INTO tblRoles (RoleName) VALUES
(‘Role1’), (‘Role2’), (‘Role3’);

CREATE TABLE tblUsers
(
UserId INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
,UserName VARCHAR(20) UNIQUE NOT NULL
,IsActive BIT NOT NULL DEFAULT (1)
);

INSERT INTO tblUsers (UserName, IsActive) VALUES
(‘User1’, 1), (‘User2’,1), (‘User3’,0), (‘User4’,1), (‘User5’,0);

CREATE TABLE tblUsersRoles
(
UserId INT NOT NULL FOREIGN KEY REFERENCES tblUsers(UserId)
,RoleId INT NOT NULL FOREIGN KEY REFERENCES tblRoles(RoleId)
);

INSERT INTO tblUsersRoles (UserId, RoleId) VALUES
(3,2), (5,2), (1,3), (2,3), (4,3), (1,1), (2,1), (3,1), (5,1);

;
——– 1. Section
WITH ActiveUsers AS (
SELECT UserId
FROM tblUsers
WHERE IsActive = 1
),
——– 2. Section
RoleNCount AS (
SELECT RoleId, COUNT(*) AS ActiveUserCount
FROM tblUsersRoles BRG
INNER JOIN ActiveUsers U ON BRG.UserId = U.UserId
GROUP BY BRG.RoleId
),
——– 3. Section
RoleSummary AS (
SELECT R.RoleName, ISNULL(S.ActiveUserCount,0) AS ActiveUserCount
FROM tblRoles R
LEFT JOIN RoleNCount S ON R.RoleId = S.RoleId
),
——– 4. Section
Total AS (
SELECT COUNT(*) AS TotalCountInAllRoles FROM ActiveUsers
)
SELECT S.*, Total.TotalCountInAllRoles
FROM RoleSummary S, Total
ORDER BY S.ActiveUserCount
;