Customers Who Never Order... Wait - Customers With No Transactions on Visits

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You are given two tables - Visits and Transactions. The Visits table logs every time a customer visited a mall. The Transactions table logs purchases made during a visit. Not every visit results in a transaction. Write a SQL query to find the customer IDs of customers who visited the mall but made NO transactions, and count how many such visits each customer had. Return only customers who have at least one visit with no transaction. Return the result in any order.

Database Schema

Visits

Column Name	Example Value
visit_id	1
customer_id	23

Transactions

Column Name	Example Value
transaction_id	2
visit_id	5
amount	310

Example

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

Output

customer_id	count_no_trans
54	2
30	1
96	1

Explanation:

Customer 23 and 9 made transactions on every visit. Customer 30 and 96 each had 1 visit with no transaction. Customer 54 had 3 visits - only 1 had transactions (visit 5), the other 2 (visits 7 and 8) had none.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.