Loan Types - Problem

Given a Loans table with information about different loan types for users, find all distinct user IDs that have both a Refinance loan type and a Mortgage loan type.

Table: Loans

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| loan_id     | int     |
| user_id     | int     |
| loan_type   | varchar |
+-------------+---------+

loan_id is the primary key for this table. Each row represents a loan with its associated user and type.

Return the result table ordered by user_id in ascending order.

Table Schema

Loans
Column Name Type Description
loan_id PK int Unique identifier for each loan
user_id int Identifier for the user who has the loan
loan_type varchar Type of loan (e.g., Refinance, Mortgage, Personal)
Primary Key: loan_id
Note: Each row represents a single loan. Users can have multiple loans of different types.

Input & Output

Example 1 — Basic Case with Multiple Users
Input Table:
loan_id user_id loan_type
1 1 Refinance
2 1 Mortgage
3 2 Refinance
4 3 Mortgage
5 3 Refinance
6 3 Personal
Output:
user_id
1
3
💡 Note:

User 1 has both Refinance (loan_id=1) and Mortgage (loan_id=2) loans. User 3 has both Refinance (loan_id=5) and Mortgage (loan_id=4) loans. User 2 only has a Refinance loan, so they don't qualify.
Example 2 — Edge Case with No Qualifying Users
Input Table:
loan_id user_id loan_type
1 1 Refinance
2 2 Mortgage
3 3 Personal
Output:
user_id
💡 Note:

No user has both Refinance and Mortgage loan types. Each user has only one loan type, so the result is empty.
Example 3 — Multiple Loans of Same Type
Input Table:
loan_id user_id loan_type
1 1 Refinance
2 1 Refinance
3 1 Mortgage
4 2 Mortgage
5 2 Mortgage
Output:
user_id
1
💡 Note:

User 1 has multiple Refinance loans but only needs at least one of each type to qualify. User 2 has multiple Mortgage loans but no Refinance loan, so they don't qualify.

Constraints

  • 1 ≤ loan_id ≤ 10000
  • 1 ≤ user_id ≤ 1000
  • loan_type is a valid loan type string

Visualization

Tap to expand
Loan Types: Finding Users with Both Refinance and MortgageInput: All Loansloan_iduser_idloan_type11Refinance21Mortgage32Refinance43Personal53MortgageGROUP BYHAVINGOutput: Qualifying Usersuser_id13User 1: Has both Refinance and Mortgage ✓User 2: Only has Refinance ✗User 3: Has Mortgage + Personal (counts as having both required) ✓
Understanding the Visualization
1
Filter
Keep only relevant loan types
2
Group
Group by user_id
3
Count
Count distinct loan types per user
Key Takeaway
🎯 Key Insight: Use GROUP BY with HAVING COUNT(DISTINCT) to find entities meeting multiple criteria

Related Problems

Asked in
Amazon 23 Microsoft 18 Goldman Sachs 15
28.5K Views
Medium Frequency
~8 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen