LeetCode Hard 2362 “Generate the Invoice' Interview SQL Question Explanation | EDS

SQL Schema:

Create table If Not Exists Products (product_id int, price int)
Create table If Not Exists Purchases (invoice_id int, product_id int, quantity int)
Truncate table Products
insert into Products (product_id, price) values ('1', '100')
insert into Products (product_id, price) values ('2', '200')
Truncate table Purchases
insert into Purchases (invoice_id, product_id, quantity) values ('1', '1', '2')
insert into Purchases (invoice_id, product_id, quantity) values ('3', '2', '1')
insert into Purchases (invoice_id, product_id, quantity) values ('2', '2', '3')
insert into Purchases (invoice_id, product_id, quantity) values ('2', '1', '4')
insert into Purchases (invoice_id, product_id, quantity) values ('4', '1', '10')

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

[PraveenKumar-pdsx]

We can use partition to solve. That will be more efficient

[james-r]

I always bother about Limit in case we might face scenario like Nth highest order so another way is:


with cte1 as (
select a.invoice_id, a.product_id, a.quantity, a.quantity*b.price as price from sql_hard.purchases_8 a left join sql_hard.products_8 b
on a.product_id=b.product_id
),

cte2 as (
select invoice_id, sum(price) as total from cte1
group by invoice_id
),

cte3 as (select *, row_number() over(order by total desc, invoice_id) as rn from cte2 )

select product_id, quantity, price as price from cte1 where invoice_id in (select invoice_id from cte3 where rn=1)

[shravank]

thanks for explaining..can u please do more vids on leetcode hard

[yashshah]

Hi Man,
doing great work(no ads too).
one Request : can you please attach the link of leetcode question?