1581 Customer Who Visited but Did Not Make Any Transacation

layout: post title: 1581 Customer Who Visited but Did Not Make Any Transaction description: SQL Query Explanation tag: SQL —

1581 Customer Who Visited but Did Not Make Any Transaction

Understanding the Problem

The goal is to identify customers who have visited but have not made any transaction. This means that their amount should be NULL in the Transactions table.

Approach

1. Use a LEFT JOIN between Visits and Transactions:
   • Since we need to check all customers, we perform a LEFT JOIN on visit_id, ensuring that all customer visits are included, even if they have no transactions.

   FROM Visits AS v
   LEFT JOIN Transactions AS t
   ON v.visit_id = t.visit_id
   

   • This ensures that if a customer did not make any transactions, their amount will be NULL.
2. Filter customers who have not made any transaction:
   • We select only those where Transactions.amount is NULL, ensuring that no transactions were recorded.

   WHERE t.amount IS NULL
   

3. Group by customer_id to count occurrences:
   • Since we want to count how many times each customer visited without making a transaction, we use:

   GROUP BY v.customer_id
   

   • This groups visits by customer, ensuring that we count the number of times each customer did not make a transaction.
4. Select relevant columns:
   • We use COUNT(*) to count the number of times a customer did not make a transaction.

   SELECT v.customer_id, COUNT(*) AS no_transaction_visits
   

Conclusion

By structuring the query in this way, we effectively identify customers who have visited but never completed a transaction, ensuring an accurate count of such instances.