Tags

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


SQL PUZZLE | Count records the where the Vals is decreased by more than 2 between current & previous records | SQL Interview Question

In this puzzle you have to count the number of records where the Vals is decreased by more than 2 between current & previous records. Please see the sample input and expected output.

Sample Input

Dt Vals
2015-09-13 16:11:00.000 10
2015-09-13 16:16:00.000 11
2015-09-13 16:21:00.000 12
2015-09-13 16:26:00.000 11
2015-09-13 16:31:00.000 9
2015-09-13 16:36:00.000 10
2015-09-13 16:41:00.000 12
2015-09-13 16:46:00.000 9
2015-09-13 16:46:00.000 3

Expected Output

Count
3

Script – DDL and INSERT Sample Data

--

CREATE TABLE GetDiffOfTwo
(
	 Dt DATETIME
	,Vals INT
)
GO

INSERT INTO GetDiffOfTwo(Vals,dt) VALUES
(10,'2015-09-13 16:11:00.000'),
(11,'2015-09-13 16:16:00.000'),
(12,'2015-09-13 16:21:00.000'),
(11,'2015-09-13 16:26:00.000'),
(9 ,'2015-09-13 16:31:00.000'),
(10,'2015-09-13 16:36:00.000'),
(12,'2015-09-13 16:41:00.000'),
(9 ,'2015-09-13 16:46:00.000'),
(3 ,'2015-09-13 16:46:00.000')
GO

SELECT * FROM GetDiffOfTwo
GO

--

SOLUTION – 1

--

;WITH CTE AS
(
	SELECT IIF(Vals- ISNULL(LAG(Vals) OVER (ORDER BY Dt ASC),Vals)<-1,1,0) cnt FROM GetDiffOfTwo
)
SELECT SUM(cnt) [Count] FROM CTE

--

Output – 1

--

Count
-----------
3

(1 row affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Advertisements