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 by user_id in ascending order.
The result format is in the following example.
Example 1:
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.
Problem Overview: You are given a table of loans where each record represents a loan issued to a user. The task is to summarize the data by loan category and report how many loans exist for each loan_type. This is a classic SQL aggregation problem where you group rows and compute counts per category.
Approach 1: Grouping and Aggregation with SQL (O(n) time, O(k) space)
The most direct solution uses SQL aggregation. Scan the Loans table and group rows by the loan_type column using GROUP BY. For each group, compute the number of records with COUNT(*). This produces one row per loan type along with the total number of loans in that category. The database engine internally builds aggregation buckets for each distinct type while iterating through the table.
The key insight is that relational databases are optimized for grouping operations. Instead of manually iterating through rows or performing multiple queries, a single aggregation pass computes all counts efficiently. The database query planner typically performs a linear scan of the table and maintains a small hash aggregation structure keyed by loan_type. If there are k distinct loan types, the extra memory required is proportional to O(k).
This pattern appears frequently in SQL interview questions and analytics workloads. Any time you need per-category statistics—counts, totals, averages—you should think of aggregation with GROUP BY. Similar patterns show up in problems involving database queries, SQL aggregation, and GROUP BY operations.
Recommended for interviews: The grouping approach is exactly what interviewers expect. It demonstrates that you understand relational aggregation and how to summarize datasets efficiently using SQL primitives. There is no meaningful brute force alternative in SQL—using GROUP BY with COUNT is both the simplest and optimal solution with linear scan complexity.
We can group the Loans table by user_id to find users who have both Refinance and Mortgage. Then, sort the results by user_id.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Grouping with COUNT(*) | O(n) | O(k) | Standard SQL aggregation when summarizing rows by category |
| GROUP BY with additional aggregates (COUNT, SUM) | O(n) | O(k) | When computing multiple statistics per loan type in a single query |
Leetcode 2990 - Loan Types - Solved by Everyday Data Science | GROUPBY, HAVING, COUNT DISTINCT • Everyday Data Science • 660 views views
Practice Loan Types with our built-in code editor and test cases.
Practice on FleetCode