1581. Customer Who Visited but Did Not Make Any Transactions

Question:

https://leetcode.com/problems/customer-who-visited-but-did-not-make-any-transactions/

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:

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

Solution:

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

1596. The Most Frequently Ordered Products for Each Customer

Question:

https://leetcode.com/problems/the-most-frequently-ordered-products-for-each-customer/

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:

https://leetcode.com/problems/sellers-with-no-sales/

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

Leave a Reply