asked
Interview question
I have table called **credit** in which there are multiple bank let's say hsbc, axis, hdfc, pnb, canara. now I need to find the loyal customer of the bank who has account with them only not with other banks.This question was asked in interview, please help. CustomerName hsbc canara hdfc axis pnb ------------ ------ ------ ------ ------ ------ A NULL 2 NULL NULL NULL B NULL 4 NULL NULL NULL C 3 2 NULL NULL NULL D NULL 2 NULL NULL 1 E NULL 2 NULL NULL NULL F 1 NULL 1 NULL 1
@Rohit_3 Something is missing here. Where is the customer information? There is no column for customer id or name or some other identifiable info. Also, are they asking about a specific bank or for a select statement which will return all rows where the not null value is set in only one column? What do the numbers in the columns' values represent? Also, why does the table have a column per bank instead of a row per customer/bank combination? This is a very stupid design because it will necessitate changes to the table (adding a column) and the application code (if any) any time there is a need to add another bank. Please confirm that the table structure is correct, clarify what do the numbers in columns' values represent, and whether they are asking for a specific bank or for all rows with values set in only one column. Thank you.
No, they are not asking for any particular bank , they want to know the name of the customers who are loyal to bank let's assume that customer names are like A,B,C,D,E,F. Here number represents customers . For instance in the first row canara bank has 3 customer who are loyal to bank and they don't have any account in other banks. Null represents no account . Thank you in advance :)
@Rohit_3 You keep mentioning customer names but there are no columns related to customer info. Also, the first row in your sample data has the value of **2**, not **3** in the column named **canara**. What does that number represent? Is it a number of customers? Or is it something else? All you have in your sample data is the table with 6 rows 5 columns where each column is actually named after a bank. Where do the customers come into play with such structure? Please clarify.
Oleg ,it was a scenario based question which was asked during an interview . Yes, that's 2 not 3 it was typing mistake I'm sorry for that . The number represents customers as I explained in the above comment as well and yes you're right Oleg all I have is this structure . They wanted to know the name of the customers who are loyal to bank let's assume that customer names are like A,B,C,D,E,F(you can add that column ).
@Rohit_3 OK, I updated the sample data in your question to include the column named CustomerName. I have done it just to show that your explanation about the meaning of the numbers in the bank columns is still unclear. If every row identifies a single customer then how is it possible that the numbers represent customers? What does it even mean, represent customers? The question you asked is about a very basic T-SQL functionality and there is no problem to come up with the simple query to answer it, but I just would like to know the details in order to come up with the answer producing expected results. Please have a look at the updated sample data, now including the customer name column. Does this change reflect what you said? Please let me know and I will come up with the select statement. Thank you.
Up this point, I ain't the faintest about the meaning of the numbers in the columns **hsbc**, **canara**, **hdfc**, **axis** and **pnb**. What do this numbers represent is still a mystery. However, this problem aside, here are 2 methods of selecting the list of loyal customers (those who have account only with one bank and not any other banks). First method allows all columns of the table to be included in the select because there is no any aggregation (group by) used in the query. The idea is as follows: if you divide the value of the column by itself then the result is going to be either NULL or 1. Now, replacing the possible NULL values with zero via isnull, adding them all up will yield the number of banks where the customer has account. The loyal customers will have account in only one bank, so: select * from credit where isnull(hsbc/hsbc, 0) + isnull(canara/canara, 0) + isnull(hdfc/hdfc, 0) + isnull(axis/axis, 0) + isnull(pnb/pnb, 0) = 1; Based on the sample data in question, the query produces the following results: CustomerName hsbc canara hdfc axis pnb ------------ ------ ------ ------ ------ ------ A NULL 2 NULL NULL NULL B NULL 4 NULL NULL NULL E NULL 2 NULL NULL NULL Second method is somewhat limited because it does not include any other columns outside of the requested cusomer name column, but it still answers the question about providing the list of loyal customers. It is based on the functionality of the aggregate functions in SQL which always exclude NULL values from their respective calculations: select CustomerName from credit group by CustomerName having count(hsbc) + count(canara) + count(hdfc) + count(axis) + count(pnb) = 1; Based on the sample data in question, the query produces the following results: CustomerName ------------ A B E Hope this helps. Oleg
Yes, it really did @Oleg, I really appreciate the time and effort you put into these articles, which have greatly enhanced my understanding , I was trying and looking for something similar to 2nd one. @Oleg could you please explain indexes(depth) in Microsoft sql server as I have been working on it only. If possible can we schedule a skype call, because I really need to clear my concept about it.
18 People are following this question.
