두 테이블을 사용한 내부 조인 및 다른 테이블과 결과를 왼쪽 조인

Inner Join두 테이블 을 사용 하여 세 테이블에서 데이터를 쿼리 Left Join하고 다른 테이블을 사용하여 결과 를 쿼리하고 싶습니다 .

내가 여기서 뭘하려고하면에있는 모든 레코드를 가져올 것입니다 UserTable및 Activity아이템이 활동 완료 테이블에 또는 다른 해당 항목에 0의 값을 만들 경우 레코드의 수를 증가시킨다.

그리고를 계산 ItemCompleted하고 ItemRemainingActivityCompletion 및 활동 테이블에서.

원하는 출력 :

ActivityDuration |  Name    |ItemCompleted |ItemRemaining
-----------------|----------|--------------|-------------
Week 7           |  A B     |   2          |    3
Week 7           |  A C     |   1          |    4
Week 7           |  A C A C |   0          |    5
Week 7           |  A C B   |   1          |    4
Week 7           |  A C C   |   2          |    3
Week 7           |  A C D   |   2          |    3
Week 7           |  A C E   |   2          |    3
Week 7           |  A C F   |   2          |    3
Week 8           |  A B     |   0          |    3
Week 8           |  A C     |   0          |    3
Week 8           |  A C A C |   0          |    3
Week 8           |  A C B   |   1          |    2
Week 8           |  A C C   |   2          |    0
Week 8           |  A C D   |   0          |    3
Week 8           |  A C E   |   0          |    3   
Week 8           |  A C F   |   0          |    3
Week 9           |  A B     |   3          |    0
Week 9           |  A C     |   1          |    2
Week 9           |  A C A C |   1          |    2
Week 9           |  A C B   |   0          |    3
Week 9           |  A C C   |   0          |    3
Week 9           |  A C D   |   0          |    3
Week 9           |  A C E   |   0          |    3
Week 9           |  A C F   |   0          |    3
                             

샘플 기록 :

create table UserTable (
  Id integer not null,
  Name varchar(12) not null
);

insert into UserTable  values (1,  'A B');
insert into UserTable  values (2,  'A C');
insert into UserTable  values (3,  'A C A C');
insert into UserTable  values (4,  'A C C');
insert into UserTable  values (5,  'A C B');
insert into UserTable  values (6,  'A C C');
insert into UserTable  values (7,  'A C D');
insert into UserTable  values (8,  'A C E');
insert into UserTable  values (9,  'A C F');


create table Activity (
  Id integer not null,
  Activity varchar(12) not null,
  ActivityDuration varchar(12) not null,
);

insert into Activity  values (1, 'Videos', 'Week 9');
insert into Activity  values (2, 'Text',   'Week 9');
insert into Activity  values (3, 'Page',   'Week 9');
insert into Activity  values (6, 'Chat',   'Week 8');
insert into Activity  values (11, 'Image', 'Week 8');
insert into Activity  values (12, 'Chat',  'Week 8');
insert into Activity  values (13, 'Chat',  'Week 7');
insert into Activity  values (17, 'Vidoes','Week 7');
insert into Activity  values (18, 'Vidoes','Week 7');
insert into Activity  values (19, 'Chat',  'Week 7');
insert into Activity  values (19, 'Chat',  'Week 7');


create table ActivityCompletion (
  Id integer not null,
  UserId integer not null,
  ActivityId integer not null,
);
insert into ActivityCompletion  values (1,  1 , 1); --A B --Week 9
insert into ActivityCompletion  values (2,  1 , 2); --A B --Week 9 
insert into ActivityCompletion  values (3,  1 , 3); --A B --Week 9
insert into ActivityCompletion  values (4,  2 , 2); --A C --Week 9
insert into ActivityCompletion  values (5,  3 , 2); --A C A C --Week 9
insert into ActivityCompletion  values (6,  4 ,13); --A C C --Week 7
insert into ActivityCompletion  values (7,  7 ,13); --A C D --Week 7
insert into ActivityCompletion  values (7,  7 ,13); --A C D --Week 7
insert into ActivityCompletion  values (7,  8 ,13); --A C E --Week 7
insert into ActivityCompletion  values (7,  8 ,18); --A C E --Week 7
insert into ActivityCompletion  values (10, 9 ,18); --A C F--Week 7
insert into ActivityCompletion  values (11, 9 ,13); --A C F--Week 7
insert into ActivityCompletion  values (12, 4 ,13); --A C C--Week 7
insert into ActivityCompletion  values (13, 5 ,18); --A C B--Week 7 
insert into ActivityCompletion  values (14, 5 ,11); --A C B--Week 8
insert into ActivityCompletion  values (15, 2 ,13); --A C --Week 7
insert into ActivityCompletion  values (16, 1 ,13); --A B --Week 7
insert into ActivityCompletion  values (17, 1 ,13); --A B --Week 7
insert into ActivityCompletion  values (18, 6 ,11); --A C C --Week 8
insert into ActivityCompletion  values (19, 6 ,11); --A C C--Week 8

내가 시도한 것은 :

SELECT AT.ActivityDuration,  UA.Name, COUNT(AT.Activity) ItemCompleted
FROM dbo.Activity AT 
LEFT JOIN  (select UT.Name, UT.Id, AC.ActivityId from UserTable UT
  INNER JOIN ActivityCompletion AC ON UT.Id = AC.UserId
) UA ON UA.ActivityId = AT.Id
GROUP BY AT.ActivityDuration, UA.Name
ORDER BY AT.ActivityDuration

이 쿼리가 적절한 값을 반환하지 않는 이유를 찾을 수 없습니다. 내가 놓친 곳은 어디입니까?

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8b5439f8d627c25596ed1cfc767837ef