# 1581. 进店却未进行过交易的顾客
# https://leetcode-cn.com/problems/customer-who-visited-but-did-not-make-any-transactions/

# SQL架构
Create table If Not Exists Visits(visit_id int, customer_id int);
Create table If Not Exists Transactions(transaction_id int, visit_id int, amount int);
Truncate table Visits;
insert into Visits (visit_id, customer_id) values ('1', '23');
insert into Visits (visit_id, customer_id) values ('2', '9');
insert into Visits (visit_id, customer_id) values ('4', '30');
insert into Visits (visit_id, customer_id) values ('5', '54');
insert into Visits (visit_id, customer_id) values ('6', '96');
insert into Visits (visit_id, customer_id) values ('7', '54');
insert into Visits (visit_id, customer_id) values ('8', '54');
Truncate table Transactions;
insert into Transactions (transaction_id, visit_id, amount) values ('2', '5', '310');
insert into Transactions (transaction_id, visit_id, amount) values ('3', '5', '300');
insert into Transactions (transaction_id, visit_id, amount) values ('9', '5', '200');
insert into Transactions (transaction_id, visit_id, amount) values ('12', '1', '910');
insert into Transactions (transaction_id, visit_id, amount) values ('13', '2', '970');

# Write your MySQL query statement below
select
    v.customer_id as customer_id,
    count(v.customer_id) as count_no_trans
from
    Visits v
    left join Transactions t on v.visit_id = t.visit_id
where
    t.amount is null
group by
    v.customer_id;

# clean-up
drop table Visits;
drop table Transactions;