Leetcode SQL (#1549, #1555, #1565, #1571)

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

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  

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

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