Customer Visits Without Transactions
Google
Problem Statement
Marketing analysts want to understand customer bounce rates—instances where a user visits the store but leaves without making a purchase.
Write an SQL query to find the IDs of the users who visited without making any transactions and the number of times they made these types of visits.
Rules:
- A visit without transaction is defined as a visit_id that exists in the Visits table but does not have a corresponding entry in the Transactions table.
- Return the customer_id and the count of these visits as count_no_trans.
- Results must be sorted by customer_id in ascending order.
Tests your understanding of
Basic SQL, Joins, LEFT JOIN, Filtering and Aggregation
Input Tables
visits
| visit_id(INTEGER) | customer_id(INTEGER) |
|---|---|
| 1 | 23 |
| 2 | 9 |
| 5 | 54 |
| 7 | 54 |
| 8 | 54 |
| 12 | 9 |
| 13 | 30 |
| 19 | 30 |
transactions
| transaction_id(INTEGER) | visit_id(INTEGER) | amount(INTEGER) |
|---|---|---|
| 2 | 5 | 310 |
| 3 | 12 | 560 |
| 9 | 19 | 400 |
Expected Output
| customer_id(INTEGER) | count_no_trans(INTEGER) |
|---|---|
| 9 | 1 |
| 23 | 1 |
| 30 | 1 |
| 54 | 2 |
Tags
EasyBasic SQLJoinsLEFT JOINFilteringAggregation
10-15 min
74%