Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Failed thrice in last 3 Attempts Puzzle

In this puzzle we have to find people who failed the exam in last 3 attempts.Please check the sample input and expected output.

Sample Input

Dt Name Number Result
2017-12-14 13:33:09.460 Pawan 1504579 pass
2017-12-14 13:33:12.460 Pawan 1504579 pass
2017-12-14 13:34:15.463 Pawan 1504579 pass
2017-12-14 13:36:18.470 Ramesh 1504579 fail
2017-12-14 13:37:21.470 Ramesh 1504579 pass
2017-12-14 13:37:24.473 Ramesh 1504579 fail
2017-12-14 13:38:27.473 vaibhav 1504579 fail
2017-12-14 13:38:40.473 vaibhav 1504579 fail
2017-12-14 13:38:40.473 vaibhav 1504579 fail
2017-12-14 13:38:49.477 vaibhav 1504579 pass
2017-12-14 13:38:51.477 K 1504579 fail
2017-12-14 13:39:02.477 K 1504579 fail
2017-12-14 13:39:11.480 mayank 1504579 pass
2017-12-14 13:39:18.480 mayank 1504579 fail
2017-12-14 13:39:21.480 mayank 1504579 fail
2017-12-14 13:39:24.480 mayank 1504579 fail

Expected Output

Name Number FailCount Result
K 1504579 2 Pass
mayank 1504579 3 Fail
Pawan 1504579 0 Pass
Ramesh 1504579 2 Pass
vaibhav 1504579 2 Pass

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE testPassFail
(
  Dt DATETIME
 ,Name VARCHAR(20)
 ,Number BIGINT
 ,Result VARCHAR(4)
)
GO

INSERT INTO testPassFail VALUES (GETDATE(),'Pawan','1504579','pass');
WAITFOR DELAY '00:00:03';
INSERT INTO testPassFail VALUES (GETDATE(),'Pawan','1504579','pass');
WAITFOR DELAY '00:01:03';
INSERT INTO testPassFail VALUES (GETDATE(),'Pawan','1504579','pass');
WAITFOR DELAY '00:02:03';
INSERT INTO testPassFail VALUES (GETDATE(),'Ramesh','1504579','fail');
WAITFOR DELAY '00:01:03';
INSERT INTO testPassFail VALUES (GETDATE(),'Ramesh','1504579','pass');
WAITFOR DELAY '00:00:03';
INSERT INTO testPassFail VALUES (GETDATE(),'Ramesh','1504579','fail');
WAITFOR DELAY '00:01:03';
INSERT INTO testPassFail VALUES (GETDATE(),'vaibhav','1504579','fail');
WAITFOR DELAY '00:00:13';
INSERT INTO testPassFail VALUES (GETDATE(),'vaibhav','1504579','fail');
WAITFOR DELAY '00:00:00';
INSERT INTO testPassFail VALUES (GETDATE(),'vaibhav','1504579','fail');
WAITFOR DELAY '00:00:09';
INSERT INTO testPassFail VALUES (GETDATE(),'vaibhav','1504579','pass');
WAITFOR DELAY '00:00:02';
INSERT INTO testPassFail VALUES (GETDATE(),'K','1504579','fail');
WAITFOR DELAY '00:00:11';
INSERT INTO testPassFail VALUES (GETDATE(),'K','1504579','fail');
WAITFOR DELAY '00:00:09';
INSERT INTO testPassFail VALUES (GETDATE(),'mayank','1504579','pass');
WAITFOR DELAY '00:00:07';
INSERT INTO testPassFail VALUES (GETDATE(),'mayank','1504579','fail');
WAITFOR DELAY '00:00:03';
INSERT INTO testPassFail VALUES (GETDATE(),'mayank','1504579','fail');
WAITFOR DELAY '00:00:03';
INSERT INTO testPassFail VALUES (GETDATE(),'mayank','1504579','fail');
GO

SELECT * FROM testPassFail

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1 | Using Ranking Functions

--

SELECT k.Name,k.Number,SUM(CASE WHEN Result = 'Fail' THEN 1 ELSE 0 END) FailCount
,CASE WHEN SUM(CASE WHEN Result = 'Fail' THEN 1 ELSE 0 END) >= 3 THEN 'Fail' ELSE 'Pass' END Result 
FROM 
(
	SELECT * , row_number()  OVER (PARTITION BY Name ORDER BY Dt desc) rk FROM testPassFail			
)k WHERE rk <= 3
GROUP BY k.Name,k.Number
--

Output

--

/*------------------------
SELECT k.Name,k.Number,SUM(CASE WHEN Result = 'Fail' THEN 1 ELSE 0 END) FailCount
,CASE WHEN SUM(CASE WHEN Result = 'Fail' THEN 1 ELSE 0 END) >= 3 THEN 'Fail' ELSE 'Pass' END Result 
FROM 
(
	SELECT * , row_number()  OVER (PARTITION BY Name ORDER BY Dt desc) rk FROM testPassFail			
)k WHERE rk <= 3
GROUP BY k.Name,k.Number
------------------------*/
Name                 Number               FailCount   Result
-------------------- -------------------- ----------- ------
K                    1504579              2           Pass
mayank               1504579              3           Fail
Pawan                1504579              0           Pass
Ramesh               1504579              2           Pass
vaibhav              1504579              2           Pass

(5 row(s) affected)


                                            
--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com