1549. The Most Recent Orders for Each Product

Question:

https://leetcode.com/problems/the-most-recent-orders-for-each-product/

Solution:

select 
p.product_name, 
o.product_id, 
o.order_id, 
o.order_date
from(
    select 
    product_id, 
    order_id, 
    order_date, 
    rank() over(partition by product_id order by order_date desc) as seq
    from orders 
) o
left join products p 
    on o.product_id = p.product_id
where o.seq = 1
order by 1,2,3

1555. Bank Account Summary

Question:

https://leetcode.com/problems/bank-account-summary/

Solution:

select
u.user_id,
u.user_name,
(u.credit - ifnull(sum(t.paid_amount),0) + ifnull(sum(t.received_amount),0)) as credit,
case 
    when (u.credit - ifnull(sum(t.paid_amount),0) + ifnull(sum(t.received_amount),0)) < 0 then 'Yes'
    else 'No'
end as credit_limit_breached
from Users u
    left join (
        select 
        paid_by as user_id,
        0 as received_amount,
        amount as paid_amount
        from Transactions
        union all
        select
        paid_to as user_id,
        amount as received_amount,
        0 as paid_amount
        from Transactions
    ) t
        on u.user_id = t.user_id
group by 1, 2  

1565. Unique Orders and Customers per Month

Question:

https://leetcode.com/problems/unique-orders-and-customers-per-month/

Solution:

select 
substr(order_date,1,7) as month, 
count(distinct order_id) as order_count,
count(distinct customer_id) as customer_count
from orders
where invoice > 20
group by 1

1571. Warehouse Manager

Question:

https://leetcode.com/problems/warehouse-manager/

Solution:

select 
warehouse_name, 
sum(total_volume) as volume
from(
    select 
    w.name as warehouse_name, 
    w.product_id, 
    w.units, 
    (p.width * p.length * p.height) as vol,
    (w.units * (p.width * p.length * p.height)) as total_volume
    from warehouse w
        left join products p 
            on w.product_id = p.product_id
) p
group by 1

Leave a Reply