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 - SQL Problem INPUT: Loans Table user_id loan_type 1 Refinance 1 Mortgage 2 Refinance 2 Refinance 3 Mortgage 3 Mortgage 4 Refinance 4 Mortgage Find users with BOTH Refinance AND Mortgage Refinance Mortgage ALGORITHM STEPS 1 Self-Join Strategy Join Loans table with itself on matching user_id 2 Filter Conditions L1.loan_type = 'Refinance' L2.loan_type = 'Mortgage' 3 Select Distinct Use DISTINCT to remove duplicate user_id values 4 Order Results ORDER BY user_id ASC SELECT DISTINCT L1.user_id FROM Loans L1 JOIN Loans L2 ON L1.user_id = L2.user_id WHERE ... ORDER BY ... FINAL RESULT User 1: Has BOTH Refinance + Mortgage - OK User 2: Only Refinance Missing Mortgage - SKIP User 3: Only Mortgage Missing Refinance - SKIP User 4: Has BOTH Refinance + Mortgage - OK Output Table: user_id 1 4 Key Insight: Self-join allows comparing different rows of the same table. By joining on user_id and filtering for different loan types in each table alias, we find users who appear with BOTH loan types. DISTINCT eliminates duplicates when a user has multiple loans of each type. TutorialsPoint - Loan Types | Optimal Solution (Self-Join Approach)

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