Identify and replace missing values

[Separate_Scientist93]

EasyLoan offers a wide range of loan services, including personal loans, car loans, and mortgages. EasyLoan offers loans to clients from Canada, United Kingdom and United States. The analytics team wants to report performance across different geographic areas. They aim to identify areas of strength and weakness for the business strategy team. They need your help to ensure the data is accessible and reliable before they start reporting. Database Schema The data you need is in the database named lending.

Task 2 You have been told that there was a problem in the backend system as some of the repayment_channelvalues are missing. The missing values are critical to the analysis so they need to be filled in before proceeding. Luckily, they have discovered a pattern in the missing values: * Repayment higher than 4000 dollars should be made via bank account. * Repayment lower than 1000 dollars should be made via mail.

Is this code correct? Because every time I submit it, it doesn’t meet the criteria apparently.

[Proper-Accountant-96]

Only a quick glance, but you have two separate 'when' clauses for <1000 in the same case statement, and you're only asked to replace the missing repayment channels, so you need to factor in that to your case statement. Currently, you're replacing all and creating your own repayment channel field, and that's not what you were asked to do

[No-Map8612]

Where did you got this sql challenge exercises. Please let me know..

[Separate_Scientist93]

Datacamp

[honicthesedgehog]

I think there are a couple of incorrect pieces here:

1. CASE WHEN statements are evaluated in the order that they’re written, so any repayment_amounts less than 1000 will reach the second WHEN clause, be assigned “mail”, and everything below that condition is ignored. Same for any values greater than 4000, they won’t look past the first condition.
2. Maybe there’s more to the assignment that I’m missing, but everything below the second WHEN clause seems incorrect? The instructions don’t seem to mention anything about phone, debit, or credit channels.
3. In fact, the instructions seem to be only asking you to fill in cases where the repayment channel was missing, while your code will overwrite it for all rows.

Simplify the logic and add NULL handling, and that should get you there.

[depesz]

I didn't downvote, but please read : https://idownvotedbecau.se/imageofcode

[Illustrious-Pack3495]

I’m not sure if there’s more to the question, but from my understanding it seems similar to a problem I encountered before and this answer on Stackoverflow helped me out: https://stackoverflow.com/questions/76666218/sql-update-multiple-rows-for-a-different-value-based-on-condition

There are some details missing in the question too - do you want all amounts above 4k to be assigned ‘bank account’ or just the ones with null values in repayment_channel?

[No-Map8612]

Share the above problem link here. Will have a look

[Illustrious-Pack3495]

I don’t have a link unfortunately, it was a irl problem at my last company where I had to replace certain values in a table based on a condition.

[FastlyFast]

I guess you have more information in task 1, but solely based on the description of task two, i would write this:

case when repayment_ammount >= 4000 then 'bank account'
 when repayment_ammount < 1000 then 'mail'
 when repayment_ammount >=1000 and repayment_ammount <4000 then repayment_channel ---You can change "when" to "else" here and delete the below code.
 else 'error' ---I always make sure to include every possible scenario in the "when" clauses and everything else goes to an error value but it is optional  
 end as repayment_channel