LeetCode Medium 2084 Interview SQL Question with Detailed Explanation

No video

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

Рет қаралды 5,259

2 жыл бұрын

Question: leetcode.com/p...
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

Пікірлер: 28

@metricsview33 7 ай бұрын

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

@florincopaci6821 2 жыл бұрын

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

@EverydayDataScience 2 жыл бұрын

Sure, @florin. Great thought process on the alternative solutions.

@florincopaci6821 2 жыл бұрын

@@EverydayDataScience Thank you and please regarding that question because is give me headache. Thank you ! All the best!

@Bhaweshgupta 4 ай бұрын

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

@haleinan2727 2 жыл бұрын

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

@EverydayDataScience 2 жыл бұрын

Glad that you found the video useful, Hale.

@ritusantra8641 Ай бұрын

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;

@MrTrendCatcher 2 ай бұрын

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);

@roymou5856 5 ай бұрын

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)

@sivasrimakurthi206 Жыл бұрын

@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.

@EverydayDataScience Жыл бұрын

Thanks for such kind words 😊 Glad that the video was useful. All the best.

@ujjwalvarshney3188 Жыл бұрын

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;

@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 )

@anujkumar8341 2 жыл бұрын

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

@AbhijeetSachdev 3 ай бұрын

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 ) );