The interview is the process where candidates and companies want to understand how well they match each other. It's hard to do so in a short time. Nonetheless, there are some techniques to improve the process. One of them is the real tasks the company face. It shouldn't be a question where you may guess the right answer. Moreover, it's fine to be wrong (yeah, we are people, we make mistakes). You should rather be examining how well you understand conceptions and how fast you can learn from your mistakes. The interviewer wants to see the way the candidate thinks.
And here we go. I will share some interesting tasks I have faced in my career. I found them really good for a few criteria:
You have four tables:
create table #manager ([id] int, [name] nvarchar(100));create table #customer ([id] int, [name] nvarchar(100));create table #manager_history ([customer] int, [manager] int,[start_date] date);create table #order ([customer] int, [order_date] date, [qty] numeric);
You may insert auto-generated data to play with or generate yourself:
insert into #manager ([id],[name]) values (1,'John Wein')
insert into #manager ([id],[name]) values (2,'Udo Harrinson')
insert into #customer ([id],[name]) values (1,'Peter Shop')
insert into #customer ([id],[name]) values (2,'Banana Garden')
insert into #customer ([id],[name]) values (3,'Adam GmbH')
insert into #customer ([id],[name]) values (4,'Sunny Ltd')
insert into #customer ([id],[name]) values (5,'Smile market')
insert into #manager_history ([customer],[manager],[start_date]) values (1,1,'20100101')
insert into #manager_history ([customer],[manager],[start_date]) values (1,2,'20120501')
insert into #manager_history ([customer],[manager],[start_date]) values (2,2,'20120201')
insert into #manager_history ([customer],[manager],[start_date]) values (3,1,'20100701')
insert into #manager_history ([customer],[manager],[start_date]) values (4,2,'20100201')
insert into #manager_history ([customer],[manager],[start_date]) values (4,1,'20120701')
insert into #manager_history ([customer],[manager],[start_date]) values (5,1,'20100101')
insert into #manager_history ([customer],[manager],[start_date]) values (5,2,'20110301')
insert into #manager_history ([customer],[manager],[start_date]) values (5,1,'20120101')
insert into #order ([customer],[order_date], [qty]) values (1,'20100301',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20100401',36)
insert into #order ([customer],[order_date], [qty]) values (1,'20100501',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20100601',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20100701',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20100801',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20100901',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20101001',54)
insert into #order ([customer],[order_date], [qty]) values (1,'20101101',54)
insert into #order ([customer],[order_date], [qty]) values (1,'20101201',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20110101',54)
insert into #order ([customer],[order_date], [qty]) values (1,'20110201',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20110301',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20110401',12)
insert into #order ([customer],[order_date], [qty]) values (1,'20110501',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20110601',-48)
insert into #order ([customer],[order_date], [qty]) values (1,'20110701',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20110801',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20110901',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20111001',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20111101',60)
insert into #order ([customer],[order_date], [qty]) values (1,'20111201',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20120101',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20120201',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20120301',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20120401',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20120501',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20120601',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20120701',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20120801',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20120901',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20121001',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20121101',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20121201',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20130101',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20130201',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20130301',24)
insert into #order ([customer],[order_date], [qty]) values (1,'20130401',24)
insert into #order ([customer],[order_date], [qty]) values (2,'20100101',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20100201',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20100301',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20100401',-102)
insert into #order ([customer],[order_date], [qty]) values (2,'20100501',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20100601',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20100701',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20100801',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20100901',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20101001',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20101101',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20101201',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20110101',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20110201',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20110301',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20110401',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20110501',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20110601',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20110701',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20110801',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20110901',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20111001',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20111101',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20111201',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20120101',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20120201',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20120301',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20120401',43)
insert into #order ([customer],[order_date], [qty]) values (2,'20120501',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20120601',24)
insert into #order ([customer],[order_date], [qty]) values (2,'20121001',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20121101',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20121201',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20130101',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20130201',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20130301',52)
insert into #order ([customer],[order_date], [qty]) values (2,'20130401',52)
insert into #order ([customer],[order_date], [qty]) values (3,'20100701',18)
insert into #order ([customer],[order_date], [qty]) values (3,'20100801',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20100901',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20101001',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20101101',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20101201',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20110101',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20110201',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20110301',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20110401',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20110501',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20110601',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20110701',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20110801',18)
insert into #order ([customer],[order_date], [qty]) values (3,'20110901',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20111001',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20111101',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20111201',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20120101',18)
insert into #order ([customer],[order_date], [qty]) values (3,'20120201',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20120301',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20120401',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20120501',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20120601',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20120701',12)
insert into #order ([customer],[order_date], [qty]) values (3,'20120801',-24)
insert into #order ([customer],[order_date], [qty]) values (4,'20100101',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20100201',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20100301',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20100401',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20100501',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20100601',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20100701',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20100801',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20100901',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20101001',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20101101',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20101201',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20110101',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20110201',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20110301',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20110401',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20110501',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20110601',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20110701',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20110801',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20110901',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20111001',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20111101',30)
insert into #order ([customer],[order_date], [qty]) values (4,'20111201',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20120101',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20120201',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20120301',1)
insert into #order ([customer],[order_date], [qty]) values (4,'20120401',1)
insert into #order ([customer],[order_date], [qty]) values (4,'20120501',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20120601',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20120901',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20121001',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20121101',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20121201',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20130101',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20130201',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20130301',6)
insert into #order ([customer],[order_date], [qty]) values (4,'20130401',12)
insert into #order ([customer],[order_date], [qty]) values (5,'20100101',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20100201',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20100301',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20100401',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20100501',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20100601',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20100701',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20100801',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20100901',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20101001',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20101101',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20101201',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20110101',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20110201',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20110301',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20110401',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20110501',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20110601',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20110701',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20110801',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20110901',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20111001',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20120201',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20120301',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20120401',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20120501',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20120601',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20120701',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20120801',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20120901',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20121001',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20121101',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20121201',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20130101',48)
insert into #order ([customer],[order_date], [qty]) values (5,'20130201',22)
insert into #order ([customer],[order_date], [qty]) values (5,'20130301',28)
insert into #order ([customer],[order_date], [qty]) values (5,'20130401',38)
Business: Please provide totals (Qty) by every manager.
Before coding/querying: explain your plan, do you have enough information to solve the problem properly?