SQL:
select customer_id, count(customer_id) as count_no_trans from visits
where visit_id not in
(select distinct visit_id from transactions)
group by customer_id
描述:
表1:Visits
+-------------+---------+ | Column Name | Type | +-------------+---------+ | visit_id | int | | customer_id | int | +-------------+---------+ visit_id 是该表中具有唯一值的列。 该表包含有关光临过购物中心的顾客的信息。
表2:Transactions
+----------------+---------+ | Column Name | Type | +----------------+---------+ | transaction_id | int | | visit_id | int | | amount | int | +----------------+---------+ transaction_id 是该表中具有唯一值的列。 此表包含 visit_id 期间进行的交易的信息。
有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个解决方案,来查找这些顾客的 ID ,以及他们只光顾不交易的次数。
返回以 任何顺序 排序的结果表。
示例:
输入: Visits
+----------+-------------+ | visit_id | customer_id | +----------+-------------+ | 1 | 23 | | 2 | 9 | | 4 | 30 | | 5 | 54 | | 6 | 96 | | 7 | 54 | | 8 | 54 | +----------+-------------+Transactions
+----------------+----------+--------+ | transaction_id | visit_id | amount | +----------------+----------+--------+ | 2 | 5 | 310 | | 3 | 5 | 300 | | 9 | 5 | 200 | | 12 | 1 | 910 | | 13 | 2 | 970 | +----------------+----------+--------+ 输出: +-------------+----------------+ | customer_id | count_no_trans | +-------------+----------------+ | 54 | 2 | | 30 | 1 | | 96 | 1 | +-------------+----------------+
分析:
需要找出访问过购物中心但没进行消费的客户,即在Visits表中存在的visit_id在Transactions表中并未出现,所以需要找出在visits表中存在但Transactions表中不存在的visit_id字段,即
not in (select distinct visit_id from transactions)
这样就找到了所需要的访问但没有消费过的客户,最后以客户的customer_id进行分组并调用count()函数进行统计得出答案。
select customer_id, count(customer_id) as count_no_trans from visits
where visit_id not in
(select distinct visit_id from transactions)
group by customer_id