SQL INTERVIEW QUESTION | Retrieve DISTINCT Orders

[@Hope-xb5jv]

select order_id from OrderDetails
where product_id in(100,200)
group by order_id
having count(distinct product_id) = 2

[@Ilovefriendswebseries]

great

[@prajju8114]

with cte_order as
(
select order_id,product_id,quantity,row_number() over(partition by order_id order by product_id) as rnk from OrderDetails where product_id in(100,200)
)
Select distinct order_id from cte_order group by order_id having count(distinct product_id)>=2

[@Ilovefriendswebseries]

with cte as (select *
from OrderDetails where order_id in (select order_id from OrderDetails group by order_id having count(product_id)>2)),cte2 as (
select order_id,
case when product_id =100 then 1
when product_id=200 then 1 end as flg
from cte),cte3 as (
select distinct order_id,sum(flg) over (partition by order_id) as sum_flg from cte2 )
select distinct order_id from cte3 where sum_flg>=2

[@HARSHRAJ-gp6ve]

with cte as(
select order_id,
COUNT(CASE when Product_id=100 THEN 1 ELSE NULL END) as 100_count,
COUNT(CASE when Product_id=200 THEN 1 ELSE NULL END) as 200_count
FROM OrderDetails GROUP BY order_id
)
select order_id FROM cte where 100_count>=1 and 200_count>=1;

[@arjundev4908]

WITH CTE AS(SELECT DISTINCT order_id,product_id,
CASE WHEN product_id = 100 OR product_id = 200 THEN 'YES' ELSE 'NO' END AS STAT
from OrderDetails)
SELECT order_id,count(product_id) AS distinct_products
FROM CTE
WHERE STAT = 'YES'
group by 1
having count(product_id) > 1;

[@Hope-xb5jv]

Initially i thought your question is find order_id who order product_id each time unique product but they also ordered atleast 100,200 product_id that's why i write this way and output is order_id 1,4
with cte as
(
select * from OrderDetails
where order_id in (select order_id from OrderDetails
where product_id in(100,200)
group by order_id
having count(distinct product_id) = 2)
),cte2 as
(
select order_id,
row_number()over(partition by order_id order by product_id)
-dense_rank()over(partition by order_id order by product_id) as diff
from cte
) select order_id
from cte2
group by order_id
having sum(diff) = 0

[@VARUNTEJA73]

select order_id from(
select order_id,product_id,count(product_id)over(partition by order_id order by product_id)ranks from orderdetails
where product_id =100 or product_id=200 group by order_id,product_id )t1 where t1.ranks=2

[@varunas9784]

my attempt on SQL server:
=============================
with collated_products as (select order_id, STRING_AGG(product_id, ',') within group (order by product_id) [products]
from (select distinct order_id, product_id
from OrderDetails
) s1
group by order_id)
select order_id
from collated_products
where [products] like '100,200%'
=============================

[@florincopaci6821]

hello
1 solution using exists operator
select distinct order_id
from OrderDetails od
where product_id=100
and exists (select * from orderdetails where product_id=200 and od.order_id=order_id)
2nd solution with window functions
with flo as (
select order_id, count(count(distinct case when product_id in (100,200) then 1 else 0 end))over(partition by order_id )as both_products
from orderdetails
where product_id in (100,200)
group by order_id, product_id
)
select distinct order_id
from flo
where both_products=2