Nov 05

SQL Query – Customer having maximum number of accounts

Today, we will discuss one interested and most frequently asked interview question in sql server on customer and account table. I asked this question to may candidates, some gave right answer and some failed to answer it. The question was “Write down sql query to retrieve the customer having maximum number of accounts”. The problem statement for that query was,

Problem Statement:

Statement 1: I have the customer and account details. Write down the query to retrieve the customer having maximum number of accounts or vice versa in descending order.

Statement 2: One customer can have multiple accounts and one account can have multiple customer.

Basically, idea behind this question is to select right candidate who has good knowledge(and hands on too) on design, SQL joins, clauses, database design, normalization of tables, inbuilt functions in SQL server and ability to convert business requirement into code. 

What I observed, candidates generally started to write query without understanding the requirement and discussing the approach they are going to use. Anyways…No issues at all !!

Candidates generally created only two tables and kept CustomerId in account table and tried to write query. 

Problem Solution:

The solution of above question is,

  1. Create Customer table with primary key
  2. Create Account table with primary key
  3. Create Mapping table for both accounts and add foreign keys by referring to customer and account tables
  4. Use inner join to join three tables
  5. Group the results using CustomerId or AccountId
  6. Apply order by clause 
  7. Write down select the statement

Once you create a physical tables in SQL Server then it will like below

Account and Customer Tables

Account and Customer Tables

I am not going in depth by creating physical tables and inserting the data. Rather, I would use the temporary table concept just to show case you, how the solution would be.

Create Customer table
DECLARE @Customer TABLE
(
	CustomerId INT,
	CustomerName VARCHAR(50)
)
Create Account Table
DECLARE @Account TABLE
(
	AccountId INT,
	AccountName VARCHAR(50)
)
Create CustomerAccount Table
DECLARE @CustomerAccount TABLE
(
	CustomerId INT,
	AccountId INT
)
Insert few records in Customer Table
INSERT INTO @Customer(CustomerId, CustomerName)
VALUES('1000', 'Customer 1')

INSERT INTO @Customer(CustomerId, CustomerName)
VALUES('1001', 'Customer 2')

INSERT INTO @Customer(CustomerId, CustomerName)
VALUES('1002', 'Customer 3')

INSERT INTO @Customer(CustomerId, CustomerName)
VALUES('1003', 'Customer 4')

INSERT INTO @Customer(CustomerId, CustomerName)
VALUES('1004', 'Customer 5')
Insert few records in Account Table
INSERT INTO @Account(AccountId, AccountName)
VALUES('2000', 'Account 1')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2001', 'Account 2')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2002', 'Account 3')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2003', 'Account 4')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2004', 'Account 5')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2005', 'Account 6')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2006', 'Account 7')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2007', 'Account 8')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2008', 'Account 9')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2009', 'Account 10')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2010', 'Account 11')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2011', 'Account 12')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2012', 'Account 13')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2013', 'Account 14')

INSERT INTO @Account(AccountId, AccountName)
VALUES('2014', 'Account 15')
Now create dummy data in mapping table i.e. insert few records in CustomerAccount table
INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1000, 2000)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1000, 2008)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1001, 2009)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1001, 2000)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1001, 2008)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1001, 2009)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1001, 2012)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1003, 2008)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1003, 2009)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1003, 2010)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1003, 2011)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1003, 2012)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1003, 2013)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1003, 2014)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1004, 2000)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1004, 2008)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1004, 2009)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1004, 2010)

INSERT INTO @CustomerAccount(CustomerId, AccountId)
VALUES(1002, 2011)

Now lets visualize the data in tabular form

CustomerId CustomerName
1000 Customer 1
1001 Customer 2
1002 Customer 3
1003 Customer 4
1004 Customer 5
AccountId AccountName
2000 Account 1
2001 Account 2
2002 Account 3
2003 Account 4
2004 Account 5
2005 Account 6
2006 Account 7
2007 Account 8
2008 Account 9
2009 Account 10
2010 Account 11
2011 Account 12
2012 Account 13
2013 Account 14
2014 Account 15
CustomerId AccountId
1000 2000
1000 2008
1001 2009
1001 2000
1001 2008
1001 2009
1001 2012
1003 2008
1003 2009
1003 2010
1003 2011
1003 2012
1003 2013
1003 2014
1004 2000
1004 2008
1004 2009
1004 2010
1002 2011
Query to display customer having maximum number of accounts
SELECT c.CustomerName, COUNT(ca.AccountId) AS AccountCount
FROM @Customer c 
INNER JOIN @CustomerAccount ca ON c.CustomerId = ca.CustomerId
INNER JOIN @Account a ON a.AccountId = ca.AccountId
GROUP BY ca.CustomerId, c.CustomerName
ORDER BY AccountCount DESC
Output Display
Customer with maximum accounts

Customer with maximum accounts

Query to display accounts having maximum number of customers
SELECT a.AccountName, COUNT(ca.CustomerId) AS CustomerCount
FROM @Customer c 
INNER JOIN @CustomerAccount ca ON c.CustomerId = ca.CustomerId
INNER JOIN @Account a ON a.AccountId = ca.AccountId
GROUP BY ca.AccountId, a.AccountName
ORDER BY CustomerCount DESC
Output Display
Accounts with maximum customer

Accounts with maximum customer

Interviewer Expectation 

Frankly speaking, interviewer never expecting that you should write complete compiled and syntactically correct query but expects the approach for the solution to solve the problem.

The above interview question judges your knowledge in terms of below points

  1. Candidates ability to design tables
  2. Normalization of database tables
  3. Knowledge on relationship between tables
  4. Understanding of SQL Joins
  5. Knowledge on inbuilt functions like Count, Max etc.
  6. Understanding of clauses in SQL
  7. Requirement understanding and converting business requirement to actual code

That’s It !!

I hope you found the right solution and idea behind the question. Don’t forget to give thumbs up, like and comment if you really find this solution helpful. 

Leave a Reply