## 1581. Customer Who Visited but Did Not Make Any Transactions

Question:

Solution:

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

## 1587. Bank Account Summary II

Question:

Solution:

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

## 1596. The Most Frequently Ordered Products for Each Customer

Question:

Solution:

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

## 1607. Seller with No Sales

Question:

Solution:

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