SELECT c1.client_id FROM contracts c1
JOIN contracts c2 on c1.client_id = c2.client_id and c2.product_id =2 and c2.archive is null
WHERE c1.product_id=1 and c1.archive is not null

SELECT client_id 
FROM contracts
FROUP BY client_id
HAVING SUM(CASE WHEN product_id=1 AND archive IS NOT NULL THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN product_id=2 AND archive IS NULL THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN product_id IN (1, 2) THEN 0 ELSE 1 END) = 0

SELECT c1.client_id FROM contracts c1
JOIN contracts c2 on c1.client_id = c2.client_id and c2.product_id =2 and c2.archive is null
WHERE c1.product_id=1 and c1.archive is not null

NOT EXISTS (SELECT 1 FROM contracts x WHERE x.client_id = c1.client_id AND x.product_id <> c1.product_id AND x.product_id <>c2.product_id)

SELECT c1.client_id
FROM contracts c1
WHERE c1.product_id = 1 and c1.archive is not null

SELECT c1.client_id
FROM contracts c1
INNER JOIN contracts c2 on c1.client_id = c2.client_id and c2.product_id = 2 and c2.archive is null
WHERE c1.product_id = 1 and c1.archive is not null

SELECT c1.client_id
FROM contracts c1
INNER JOIN contracts c2 on c1.client_id = c2.client_id and c2.product_id = 2 and c2.archive is null
LEFT JOIN contracts c3 on c1.client_id = c3.client_id and c3.product_id != 2 and c3.product_id != 1
WHERE c1.product_id = 1 and c1.archive is not null

SELECT c1.client_id
FROM contracts c1
INNER JOIN contracts c2 on c1.client_id = c2.client_id and c2.product_id = 2 and c2.archive is null
LEFT JOIN contracts c3 on c1.client_id = c3.client_id and c3.product_id != 2 and c3.product_id != 1
WHERE c1.product_id = 1 and c1.archive is not null
GROUP BY c1.client_id HAVING COUNT(c1.product_id) = 1 AND COUNT(c2.product_id) = 1 AND COUNT(c3.product_id) = 0

т.к. пока в теме по SQL Server - то ответ на T-SQL:
declare @t table (client_id int, product_id int, archive date)
insert into @t values(11, 1,'2018-08-08'),(11, 2, null),(11, 3, '2018-08-08'),(11, 4, null),(12, 1, '2018-08-08')
,(12, 2, null),(13, 1, null),(13, 2, '2018-08-08'),(13, 3, '2018-08-08'),(13, 4, '2018-08-08'),(15, 1, '2018-08-08')
,(15, 2, null),(15, 3, null),(15, 4, null),(16, 1, '2018-08-08'),(16, 2, null),(17, 1, null),(17, 2, '2018-08-08')
,(17, 3, null),(17, 4, '2018-08-08')
select client_id from @t group by client_id having count(distinct product_id)=2
intersect --можно и inner join
select client_id from @t where (product_id=1 and archive is not null) or (product_id=2 and archive is null) group by client_id

select c.client_id from contracts c
join contracts c1 on c.client_id=c1.client_id and c1.product_id=1 and c1.archive is not null
join contracts c2 on c.client_id=c2.client_id and c2.product_id=2 and c2.archive is null
group by c.client_id
having count(*)=2