## 1549. The Most Recent Orders for Each Product

Question:

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:

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,
amount as paid_amount
from Transactions
union all
select
paid_to as user_id,
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:

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:

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``````