00:00:00

Description

Editorial

Dual Loan Category Qualification

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

loans =

[{"loan_id":683,"user_id":101,"loan_type":"Mortgage"},{"loan_id":218,"user_id":101,"loan_type":"AutoLoan"},{"loan_id":802,"user_id":101,"loan_type":"InSchool"},{"loan_id":593,"user_id":102,"loan_type":"Mortgage"},{"loan_id":138,"user_id":102,"loan_type":"Refinance"},{"loan_id":294,"user_id":102,"loan_type":"InSchool"},{"loan_id":308,"user_id":103,"loan_type":"Refinance"},{"loan_id":389,"user_id":104,"loan_type":"Mortgage"}]

Loading problem...

101

00:00:00

Description

Editorial

Dual Loan Category Qualification

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

loans =

Dual Loan Category Qualification

Hints

Dual Loan Category Qualification

Hints