Loan Types - Problem

🏦 Loan Types Analysis

You're working as a data analyst for a financial institution that offers various loan products. Your task is to identify customers who have diversified their borrowing by taking both refinance loans and mortgage loans.

Given a Loans table with the following structure:

Column NameType
loan_idint
user_idint
loan_typevarchar

Your mission: Find all distinct user_ids that have at least one Refinance loan AND at least one Mortgage loan.

Output Requirements:
• Return only the user_id column
• Results must be ordered by user_id in ascending order
• Each user should appear only once in the result

This is a classic set intersection problem in SQL - you need to find users who belong to both the 'Refinance' group AND the 'Mortgage' group!

Input & Output

example_1.sql — Basic Example
$ Input: Loans table: +----------+---------+-----------+ | loan_id | user_id | loan_type | +----------+---------+-----------+ | 1 | 1 | Mortgage | | 2 | 1 | Refinance | | 3 | 2 | Mortgage | | 4 | 3 | Refinance | | 5 | 4 | Refinance | | 6 | 4 | Mortgage | +----------+---------+-----------+
› Output: +---------+ | user_id | +---------+ | 1 | | 4 | +---------+
💡 Note: User 1 has both Mortgage (loan_id=1) and Refinance (loan_id=2). User 4 has both Refinance (loan_id=5) and Mortgage (loan_id=6). User 2 only has Mortgage, User 3 only has Refinance, so they don't qualify.
example_2.sql — Single User Multiple Loans
$ Input: Loans table: +----------+---------+-----------+ | loan_id | user_id | loan_type | +----------+---------+-----------+ | 1 | 100 | Mortgage | | 2 | 100 | Mortgage | | 3 | 100 | Refinance | | 4 | 100 | Refinance | | 5 | 101 | Personal | +----------+---------+-----------+
› Output: +---------+ | user_id | +---------+ | 100 | +---------+
💡 Note: User 100 has multiple loans of both types (2 Mortgages and 2 Refinances), but still qualifies as having both types. User 101 only has a Personal loan, which is not one of our target types.
example_3.sql — No Qualifying Users
$ Input: Loans table: +----------+---------+-----------+ | loan_id | user_id | loan_type | +----------+---------+-----------+ | 1 | 1 | Personal | | 2 | 2 | Auto | | 3 | 3 | Mortgage | | 4 | 4 | Refinance | +----------+---------+-----------+
› Output: +---------+ | user_id | +---------+ (empty result)
💡 Note: No user has both Mortgage AND Refinance loans. Each user has at most one loan type, and none have the required combination of both target loan types.

Visualization

Tap to expand
RefinanceUsersMortgageUsersUsers withBOTH typesUser 3, 5User 2, 6User 1, 4GROUP BY user_id HAVING COUNT(DISTINCT loan_type) = 2This SQL query efficiently finds the intersection in one pass!Filter → Group → Count → Select qualified users
Understanding the Visualization
1
Identify Target Groups
We're looking for users in both the 'Refinance' and 'Mortgage' groups
2
Group by User
Aggregate all loan records by user_id to see each user's loan portfolio
3
Count Distinct Types
For each user, count how many distinct target loan types they have
4
Filter Qualified Users
Keep only users with count = 2 (both required loan types)
Key Takeaway
🎯 Key Insight: Instead of finding each set separately and computing intersection, use GROUP BY to count distinct memberships per user - elegant and efficient!

Time & Space Complexity

Time Complexity
⏱️
O(n)

Single pass through the table with GROUP BY operation, where n is the number of loan records

n
2n
✓ Linear Growth
Space Complexity
O(k)

Space proportional to number of distinct users (k), not total records (n)

n
2n
✓ Linear Space

Related Problems

Constraints

  • 1 ≤ loan_id ≤ 105
  • 1 ≤ user_id ≤ 104
  • loan_type is a non-empty string with maximum length 50
  • loan_id is unique (primary key)
  • The table contains at least 1 row
Asked in
Amazon 45 Microsoft 38 Goldman Sachs 32 JPMorgan 28
24.7K Views
High Frequency
~15 min Avg. Time
892 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