LeetCode Medium 1164 Amazon Interview SQL Question with Detailed Explanation

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

[@saisushanthdurvasulabitsian]

thank you so much , alternate way: with cte as ( select product_id , first_value(new_price) over (partition by product_id order by change_date desc) as price from Products where change_date

[@nomalware2811]

same I generated...

[@turyaganguly447]

Your process of explaining is so good. Thanks for your videos. They are really helpful.

[@sivasrimakurthi206]

@everyday Data Sceince - what an eye you got, everything you see in DATA lens .. loved the way, you conquer the problem statement.

[@EverydayDataScience]

Glad that you are enjoying the series so far 😊

[@sivasrimakurthi206]

@@EverydayDataScience yes, I am watching the entire series. It is pure fun, going over each and every video and learning new stuff in each query, that is what amazed me how you have designed this series. Thanks for well thought series.

[@anasalnaggar4838]

thanks man , ur explanation is very good

[@mickyman753]

# Write your MySQL query statement below with cte1 as ( select distinct product_id,first_value(new_price) over(partition by product_id order by change_date desc) as new_price from products where change_date

[@sohailkan17]

I was stuck in joining the tables the cte with the Products table for id#3 but could not successfully retrieve the output data Thankyou i got to learn an alternative of join UNION set operators

[@changkris2844]

Thanks bro, really helpful

[@EverydayDataScience]

Glad that you found the video useful 😊

[@sauravchandra10]

My approach: WITH cte2 AS( SELECT *, RANK() OVER(PARTITION BY product_id ORDER BY change_date DESC) AS change_no FROM Products HAVING change_date

[@rupdeepthey]

Will this work if price of product id is changed more than once after the specified date? Won't it print duplicate ids in that case?

[@vbmudalige]

My solution: with cte as ( select product_id, last_value(new_price) over( partition by product_id order by change_date rows between current row and unbounded following ) as price from Products where change_date

[@saurabhmahajan3385]

Hi Brother, Hope you doing good. Can you please solve 601. Human Traffic of Stadium? Thanks in Advance, Saurabh 😊

[@shubhammishra2225]

Alternate way --- select product_id, max(case when change_date

[@metricsview33]

what if it is not neccessary that price will increase only with time, it can decrease too in big data, then max() will not work

[@jatinyadav8960]

great

[@gxrvish]

make it simple dude, you are making it more and more complicated...

[@nomalware2811]

WITH CTE AS (SELECT DISTINCT product_id, FIRST_VALUE(new_price) OVER(PARTITION BY product_id ORDER BY change_date DESC) price FROM Products WHERE change_date

[@HarshSharma-dm6yg]

solution without using subquerry: WITH mycte AS ( SELECT product_id, change_date, new_price, RANK() OVER(PARTITION BY product_id ORDER BY change_date DESC) as ranking FROM products WHERE change_date '2019-08-16'