Можно попробовать через CTE, хотя тут велосипедности не меньше.
with cte as (select product_id, count(order_id) as cnt from orders_products)
select top 1 a.product1, b.product2, a.cnt
from cte as a
left join cte as b on b.cnt = a.cnt and a.product_id > b.product_id
order by a.cnt desc
