Problem

Table: Loans

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| loan_id     | int     |
| user_id     | int     |
| loan_type   | varchar |
+-------------+---------+
loan_id is column of unique values for this table.
This table contains loan_id, user_id, and loan_type.

Write a solution to find all distinct user_id’s that have at least one Refinance loan type and at least one Mortgage loan type.

Return the result table ordered byuser_id inascending 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

Input:
Loans table:
+---------+---------+-----------+
| loan_id | user_id | loan_type |
+---------+---------+-----------+
| 683     | 101     | Mortgage  |
| 218     | 101     | AutoLoan  |
| 802     | 101     | Inschool  |
| 593     | 102     | Mortgage  |
| 138     | 102     | Refinance |
| 294     | 102     | Inschool  |
| 308     | 103     | Refinance |
| 389     | 104     | Mortgage  |
+---------+---------+-----------+
**Output**
+---------+
| user_id | 
+---------+
| 102     | 
+---------+
**Explanation**
- User_id 101 has three loan types, one of which is a Mortgage. However, this user does not have any loan type categorized as Refinance, so user_id 101 won't be considered.
- User_id 102 possesses three loan types: one for Mortgage and one for Refinance. Hence, user_id 102 will be included in the result.
- User_id 103 has a loan type of Refinance but lacks a Mortgage loan type, so user_id 103 won't be considered.
- User_id 104 has a Mortgage loan type but doesn't have a Refinance loan type, thus, user_id 104 won't be considered.
Output table is ordered by user_id in ascending order.

Solution

Method 1 – SQL Grouping and Filtering (1)

Intuition

We need to find users who have at least one loan of type ‘Refinance’ and at least one of type ‘Mortgage’. By grouping by user and checking the presence of both types, we can filter the required users.

Approach

  1. Group the Loans table by user_id.
  2. For each user, count the number of distinct loan types that are ‘Refinance’ or ‘Mortgage’.
  3. Only keep users who have both types (count = 2).
  4. Return user_id in ascending order.

Code

1
2
3
4
5
6

SELECT user_id
FROM Loans
WHERE loan_type IN ('Refinance', 'Mortgage')
GROUP BY user_id
HAVING COUNT(DISTINCT loan_type) = 2
ORDER BY user_id;
1
2
3
4
5
6

SELECT user_id
FROM Loans
WHERE loan_type IN ('Refinance', 'Mortgage')
GROUP BY user_id
HAVING COUNT(DISTINCT loan_type) = 2
ORDER BY user_id;
1
2
3
4
5
6

class Solution:
    def loan_types(self, loans: pd.DataFrame) -> pd.DataFrame:
        filtered = loans[loans['loan_type'].isin(['Refinance', 'Mortgage'])]
        grouped = filtered.groupby('user_id')['loan_type'].nunique().reset_index()
        res = grouped[grouped['loan_type'] == 2][['user_id']].sort_values('user_id').reset_index(drop=True)
        return res

Complexity

  • ⏰ Time complexity: O(n), where n is the number of rows in Loans. We scan, group, and filter the table once.
  • 🧺 Space complexity: O(k), where k is the number of unique user_ids in Loans.