Problem

Table: Visits

+————-+———+ | Column Name | Type | +————-+———+ | visit_id | int | | customer_id | int | +————-+———+ visit_id is the column with unique values for this table. This table contains information about the customers who visited the mall.

Table: Transactions

+—————-+———+ | Column Name | Type | +—————-+———+ | transaction_id | int | | visit_id | int | | amount | int | +—————-+———+ transaction_id is column with unique values for this table. This table contains information about the transactions made during the visit_id.

Write a solution 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 result format is in the following example.

Examples

Example 1

 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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

Input: 
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 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.

## Solution

### Method 1 – LEFT JOIN and GROUP BY

#### Intuition

The key idea is to find all visits that do not have a corresponding transaction. By using a LEFT JOIN from Visits to Transactions and filtering for NULLs, we can count the number of such visits per customer.

#### Approach

1. LEFT JOIN Visits with Transactions on visit_id.
2. Filter for rows where transaction_id is NULL (no transaction for that visit).
3. GROUP BY customer_id and count the number of such visits as count_no_trans.
4. Return the result.

#### Code



    

         
         
         
        
            
            
        
            
            
                 
                 
                
            
        
            
            
        
            
            
                 
                 
                
            
        
            
            
        
            
            
                 
                 
                
            
        
            
            
        
    


    

        
         
        
        
         
         
        

             
            
             
            
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
            
            
            




1
2
3
4
5




SELECT v.customer_id, COUNT(*) AS count_no_trans
FROM Visits v
LEFT JOIN Transactions t ON v.visit_id = t.visit_id
WHERE t.transaction_id IS NULL
GROUP BY v.customer_id;





        

        
        
         
        
        
         
         
        

             
            
             
            
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
            
            
            




1
2
3
4
5




SELECT v.customer_id, COUNT(*) AS count_no_trans
FROM Visits v
LEFT JOIN Transactions t ON v.visit_id = t.visit_id
WHERE t.transaction_id IS NULL
GROUP BY v.customer_id;





        

        
        
         
        
        
         
         
        

             
            
             
            
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
            
            
            




1
2
3
4
5




def customer_no_transactions(visits: 'pd.DataFrame', transactions: 'pd.DataFrame') -> 'pd.DataFrame':
    merged = visits.merge(transactions, on='visit_id', how='left', indicator=True)
    no_trans = merged[merged['_merge'] == 'left_only']
    res = no_trans.groupby('customer_id').size().reset_index(name='count_no_trans')
    return res





        

        
        
         
        
        
    





#### Complexity

- ⏰ Time complexity: `O(N)`, where N is the number of visits, as each visit is processed once in the join and aggregation.
- 🧺 Space complexity: `O(C)`, where C is the number of customers, for storing the result.