Customer Who Visited But Did Not Make Any Transactions Problem


Description

LeetCode Problem 1581.

Table: Visits

1
2
3
4
5
6
7
8
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| visit_id    | int     |
| customer_id | int     |
+-------------+---------+
visit_id is the primary key for this table.
This table contains information about the customers who visited the mall.

Table: Transactions

1
2
3
4
5
6
7
8
9
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| transaction_id | int     |
| visit_id       | int     |
| amount         | int     |
+----------------+---------+
transaction_id is the primary key for this table.
This table contains information about the transactions made during the visit_id.

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.

Return the result table sorted in any order.

The query result format is in the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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    |
+----------------+----------+--------+

Result table:
+-------------+----------------+
| customer_id | count_no_trans |
+-------------+----------------+
| 54          | 2              |
| 30          | 1              |
| 96          | 1              |
+-------------+----------------+
Customer with id = 23 visited the mall once and made one transaction during the visit with id = 12.
Customer with id = 9 visited the mall once and made one transaction during the visit with id = 13.
Customer with id = 30 visited the mall once and did not make any transactions.
Customer with id = 54 visited the mall three times. During 2 visits they did not make any transactions, and during one visit they made 3 transactions.
Customer with id = 96 visited the mall once and did not make any transactions.
As we can see, users with IDs 30 and 96 visited the mall one time without making any transactions. Also user 54 visited the mall twice and did not make any transactions.


MySQL Solution

1
2
3
4
5
6
7
select customer_id, count(visit_id) as count_no_trans
from Visits
where visit_id not in 
    (select visit_id
    from Transactions
    group by visit_id)
group by customer_id




Related Posts

Find The Missing IDs Problem

LeetCode 1613. Write an SQL query to find the missing...

The Most Frequently Ordered Products For Each Customer Problem

LeetCode 1596. Write an SQL query to find the most...

Sellers With No Sales Problem

LeetCode 1607. Write an SQL query to report the names...

Customer Who Visited But Did Not Make Any Transactions Problem

LeetCode 1581. Write an SQL query to find the IDs...

Bank Account Summary II Problem

LeetCode 1587. Write an SQL query to report the name...

Warehouse Manager Problem

LeetCode 1571. Write an SQL query to report how much...