Leetcode SQL (#1581, #1587, #1596, #1607)

select 
v.customer_id, 
count(distinct v.visit_id) as count_no_trans
from visits v
    left join transactions s 
        on v.visit_id = s.visit_id
where s.amount is null
group by 1

select 
name, 
balance
from(
    select 
    t.account, 
    u.name, 
    sum(amount) as balance
    from transactions t
        left join users u 
            on t.account = u.account
    group by 1,2
    having sum(amount) > 10000
) a

select 
b.customer_id,
b.product_id,
p.product_name
from(
    select 
    customer_id,
    product_id,
    order_count,
    rank() over(partition by customer_id order by order_count desc) as r
    from(
        select 
        o.customer_id,
        o.product_id,
        count(distinct o.order_id) as order_count
        from Orders o
        group by 1,2
        having count(distinct o.order_id) >= 1 /* at least 1 order */
    ) a
) b
left join (
    select 
    product_id,
    product_name
    from Products
    group by 1,2
) p 
    on b.product_id = p.product_id
where b.r = 1

select
seller_name
from Seller s
/* sellers who haven't made any sales in 2020 */
where seller_id in (
    select 
    seller_id
    from Orders
    group by 1
    having max(year(sale_date)) <> 2020
    and min(year(sale_date)) <> 2020
)
/* sellers who haven't sold nothing */
or seller_id not in (
    select distinct
    seller_id
    from Orders
)
order by 1