SQL Puzzle | Failed thrice in last 3 attempts Puzzle

14 Thursday Dec 2017

Posted by Pawan Kumar Khowal in SQL SERVER, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

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

SQL Puzzle | Fetch duplicate data ( The 5 Minutes Puzzle )

14 Thursday Dec 2017

Posted by Pawan Kumar Khowal in SQL Puzzles, SQL SERVER, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ Leave a comment