SQL Query to find employees with X Consecutive A Grades

One of my friend (Satya) asked a very interesting interview question during the Lunch today. Question is to write a SQL Query to find the employees who received Grade ‘A’ for 3 consecutive years.

Self-join is the simple answer to find employees with 3 consecutive A Grades but thought about finding X consecutive A Grades. Explored possibility by creating recursive Query, CTE, etc… but found only one solution to create a Function and View with calculated column.

I am still looking for a better solution, try and comment if you can find a better solution to find employee with X consecutive A Grades 

SQL Query for your reference:

use tempdb

Create Table Employee
(EmpName Varchar(20),
EYear Numeric(4),
Grade Varchar(1))

Insert Into Employee values ('Satya',2009,'B')
Insert Into Employee values ('Satya',2010,'A')
Insert Into Employee values ('Satya',2011,'A')
Insert Into Employee values ('Satya',2012,'A')
Insert Into Employee values ('Satya',2013,'A')
Insert Into Employee values ('Satya',2014,'A')
Insert Into Employee values ('Satya',2015,'A')
Insert Into Employee values ('Syed',2009,'B')
Insert Into Employee values ('Syed',2010,'A')
Insert Into Employee values ('Syed',2011,'A')
Insert Into Employee values ('Syed',2012,'B')
Insert Into Employee values ('Syed',2013,'A')
Insert Into Employee values ('Syed',2014,'C')
Insert Into Employee values ('Syed',2015,'A')
Insert Into Employee values ('Venu',2009,'A')
Insert Into Employee values ('Venu',2010,'A')
Insert Into Employee values ('Venu',2011,'A')
Insert Into Employee values ('Venu',2012,'B')
Insert Into Employee values ('Venu',2013,'A')
Insert Into Employee values ('Venu',2014,'A')
GO

-- Simple query to given scenario
select distinct e.EmpName from
(select * from Employee where Grade = 'A' ) e 
join (select * from Employee where Grade = 'A' ) e2 on e2.EYEar = e.EYEar-1 AND e2.EmpName = e.EmpName 
join (select * from Employee where Grade = 'A' ) e3 on e3.EYEar = e.EYEar+1 AND e3.EmpName = e.EmpName

-- Complicated one with scope to expand! Create a function, view and query based on View! This is expensive...
CREATE FUNCTION dbo.findconsecutive(@name varchar(20), @year int) 
 RETURNS INT
BEGIN
declare @ret INT 
 select @ret=isnull(cons,0)+1 from myview where EYear = @year-1 and EmpName = @name
return ISNULL(@ret,1)
END 
Go

create view myview as select EmpName, EYear, Grade, dbo.findconsecutive(EmpName, EYear) as cons from Employee
GO

-- Employee with 5 consecutive ‘A’ grades
select * from myview where cons = 5

drop table Employee
--
--
Advertisements
This entry was posted in Productivity, SQL Query, SQL Server and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s