Dual Loan Category Qualification

Easy

A lending analytics team is auditing loan portfolios. The table loans contains: loan_id: unique loan record ID user_id: borrower identifier loan_type: category label for the loan Task: Return every distinct user_id that has both of the following: At least one loan where loan_type is exactly "Mortgage" At least one loan where loan_type is exactly "Refinance" Output must contain one column: user_id Sort the final result by user_id in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

loans:
| loan_id | user_id | loan_type  |
|---------|---------|------------|
| 683     | 101     | Mortgage   |
| 218     | 101     | AutoLoan   |
| 593     | 102     | Mortgage   |
| 138     | 102     | Refinance  |
| 308     | 103     | Refinance  |
| 389     | 104     | Mortgage   |

Output

[
  {"user_id":102}
]

Explanation

Only user 102 owns at least one Mortgage and at least one Refinance loan.

Example 2

Input

loans:
| loan_id | user_id | loan_type   |
|---------|---------|-------------|
| 1       | 10      | Mortgage    |
| 2       | 10      | Refinance   |
| 3       | 10      | Refinance   |
| 4       | 11      | Mortgage    |
| 5       | 11      | Personal    |

Output

[
  {"user_id":10}
]

Explanation

Duplicate qualifying rows do not change the result. User 11 is missing Refinance.

Example 3

Input

loans:
| loan_id | user_id | loan_type   |
|---------|---------|-------------|
| 20      | 501     | mortgage    |
| 21      | 501     | Refinance   |
| 22      | 502     | Mortgage    |
| 23      | 502     | refinance   |

Output

[]

Explanation

Matching is case-sensitive. Neither user has both exact labels.

Constraints

1 <= number of rows in loans <= 20,000
1 <= loan_id <= 1,000,000,000
1 <= user_id <= 1,000,000,000
loan_id is unique within each test case
loan_type is a short string category; exact value matching is case-sensitive
Additional loan types may exist beyond Mortgage and Refinance
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with one column: user_id

loans: | loan_id | user_id | loan_type | |---------|---------|------------| | 683 | 101 | Mortgage | | 218 | 101 | AutoLoan | | 593 | 102 | Mortgage | | 138 | 102 | Refinance | | 308 | 103 | Refinance | | 389 | 104 | Mortgage |

loans: | loan_id | user_id | loan_type | |---------|---------|-------------| | 1 | 10 | Mortgage | | 2 | 10 | Refinance | | 3 | 10 | Refinance | | 4 | 11 | Mortgage | | 5 | 11 | Personal |

loans: | loan_id | user_id | loan_type | |---------|---------|-------------| | 20 | 501 | mortgage | | 21 | 501 | Refinance | | 22 | 502 | Mortgage | | 23 | 502 | refinance |