LeetCode Medium 2084 Interview SQL Question with Detailed Explanation | Practice SQL

In this video I solve and explain a medium difficulty leetcode SQL question using MySQL query. This question has been asked in Apple, Facebook, Amazon, Google, Adobe, Microsoft, Adobe interviews or what we popularly call FAANG interviews.
I explain the related concept as well. This question includes points to keep in mind to develop SQL queries.

LeetCode is the best platform to help you enhance your skills, expand your knowledge and prepare for technical interviews.

If you found this helpful, Like and Subscribe to the channel for more content.

#LeetCodeSQL #FAANG #SQLinterviewQuestions

[PrithaMajumder]

Sir, Thanks a lot for This Amazing Video 😊
Problem - 45 Solved ✅ in Practice SQL - LeetCode 👩🏻‍💻

[metricsview]

with cte as (select order_id, customer_id, order_type, sum(order_type) over(partition by customer_id) as r_sum, min(order_type) over(partition by customer_id) as r_min from Orders)
select order_id, customer_id, order_type from cte
where r_sum in(0, 2) or order_type=r_min

[florincopaci]

Your videos are very useful! Thank you and if is possible please do in the future a video about 2153 question in the LeetCode. Please
Other 2 solutions at this question-
SELECT order_id, customer_id, order_type
FROM Orders
WHERE order_type = 0 OR
(order_type = 1 AND customer_id NOT IN (SELECT customer_id FROM Orders WHERE order_type = 0));

SELECT order_id, customer_id, order_type
FROM
(SELECT a.*, RANK() OVER(PARTITION BY customer_id ORDER BY order_type) AS rk FROM Orders a) t
WHERE rk = 1

[Bhaweshgupta]

with cet
(
select *, first_value(order_type) over(partition by customer_id order by order_type) fvl from Orders
)
select order_id, customer_id, order_type from cet where order_type=fvl

[haleinan]

Thanks a bunch with the detailed explanation, much appreciated!! Really helpful, and clear!

[Sigma-ysx]

One of the solutions:
SELECT * FROM Orders

WHERE (customer_id, order_type)
IN (SELECT customer_id, MIN(order_type)
FROM Orders
GROUP BY customer_id);

[shrinidhikulkarni]

with cte as (
select *, rank() over (partition by customer_id order by order_type ) as rankk from orders)

select order_id, customer_id, order_type from cte
where rankk=1

[sivasrimakurthi]

@everyday Data Science,
very nice explanation, these are the tricks everyone needs to understand to be a successful Data scientist/Analyst. The way you explained is easy to understand and because it is visual it gets more easy. Thanks a lot for time and kindness.

[SHIVANSHIAGARWAL]

hello sir,
Cant we just put a where clause where oeder_type is equal to min_order_type

[expandingourselves]

Alternative solution :

with cte as (
select distinct(order_id) from orders
where customer_id in ( select distinct(customer_id ) from orders where order_type = 0) and order_type <>1
union
select distinct(order_id) from orders
where customer_id not in ( select distinct(customer_id ) from orders where order_type = 0)
)

select * from Orders
where order_id IN ( select order_id from cte )

[muddassirnazar]

Straight forward Solution:
with cte as
(select *, sum(case when order_type=0 then 1 else 0 end )
over(partition by customer_id ) as zero_cnt,
sum(case when order_type=1 then 1 else 0 end )
over(partition by customer_id ) as one_cnt
from orders)
select order_id, customer_id, order_type from cte
where (zero_cnt>=1 and one_cnt>=1 and order_type=0) or (zero_cnt=0 or one_cnt=0)

[anujkumar]

correct me if i'm wrong, we can solve this problem with dense rank function

[ujjwalvarshney]

create temp table Orders1
(select *, min(order_type)over(partition by customer_id) as mn from orders)
select orders, customer_id, order_type*mn as order_type from Orders1;

[ritusantra]

with cte as (select
*,
rank() over(partition by customer_id order by order_type) as rnk
from orders)
select order_id, customer_id, order_type
from cte where rnk = 1
order by order_type desc;

with cte as (select
*,
min(order_type) over(partition by customer_id) as rnk
from orders)
select order_id, customer_id, order_type
from cte where order_type = rnk
order by order_type desc;

[AbhijeetSachdev]

Is it the right solution:

SELECT customer_id
FROM orders
WHERE customer_id NOT IN (
SELECT customer_id
FROM orders
WHERE order_type = 1
AND customer_id IN (
SELECT customer_id
FROM orders
WHERE order_type = 0
)
);

[roymou]

WITH CTE AS
(
select order_id, customer_id, order_type, min(order_type) over(partition by customer_id) as min_order
from Orders
)

SELECT order_id, customer_id, order_type
FROM CTE
where order_type + min_order in (0, 2)

[rayyanamir]

Please upload more medium questions sir.

[ShivamGupta-cxhy]

Hello Sir, I really like your videos but I have a doubt, are these questions available for.free on leetcode or are they in premium version

[mickyman]

WITH cte AS (
SELECT *, MIN(order_type) OVER (PARTITION BY customer_id) AS min_order_type
FROM Orders
)
SELECT order_id, customer_id, order_type
FROM cte
WHERE NOT (order_type = 1 AND min_order_type = 0);

[ShivamGupta-cxhy]

Also can I talk to you sir, I have many questions in my mind about how to prepare for data science.