LeetCode Medium 1831 Interview SQL Question with Detailed Explanation

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

[cicichang]

Thank you so much! Well explained! here is another way to do it:
WITH cte AS(
SELECT *, FIRST_VALUE(amount) OVER(PARTITION BY day ORDER BY amount DESC) AS max_amount
FROM Transactions
)
SELECT transaction_id FROM cte WHERE amount = max_amount ORDER BY transaction_id

[mickyman]

so for these kind of questions, use max, first_value, rank/rownumber after count/no count needed, and the uise filter value=max_value we got from window function to filter rows in last select statement

[expandingourselves]

select transaction_id from
( select a.*, rank() OVER (Partition by date(day) order by amount desc) as rnk from
Transactions a
) X
Where rnk = 1
order by transaction_id asc

[gbemisolaadewuya]

Would rank() function be useful instead of First_value()?

[lizasingh]

Hi you explain it so well, Thanks a ton! Keep making videos.

[mrcool]

I appreciate your consistent hard work brother, thanks for all these videos🙌, keep posting

[pitou]

with cte as(
select *, CAST(transaction_date as date) as converted_date
from transactions),
cte2 as
(
select *, RANK()over (PARTITION by converted_date order by amount desc) as rn
from cte
)
select transaction_id from cte2 where rn=1
order by transaction_id

[kavyabanka]

this is fabulous explanation dude ....

[mickyman]

WITH ranked_transactions AS (
SELECT
transaction_id,
day,
amount,
RANK() OVER (PARTITION BY CAST(day AS DATE) ORDER BY amount DESC) AS rank
FROM
Transactions
)
SELECT
transaction_id
FROM
ranked_transactions
WHERE
rank = 1
ORDER BY
transaction_id;