Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, missing Gaps Puzzle, MSBISkills, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL FAQs, SQL Interview, SQL Interview Puzzles, SQL IQs, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL Server 2012 Analysis Services, SQL SERVER Interview questions, SQL SERver performance, SQL SERVER Puzzles, SQL SERVER2005/2008, SQL Skills, SQL Sudoku, SQLPuzzles, SQLQueries, SQLSERVER, SSRS, T SQL Puzzles, T-SQL Challenge, T-SQL Query | [ Finding the missing Gaps Puzzle ], Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
T-SQL Query | [ Finding the missing Gaps Puzzle ]
The puzzle is simple. Here you have to find the missing gaps between sequential numbers. Please check out the sample input and expected output for details.
Sample Input
| num |
| 1 |
| 2 |
| 3 |
| 6 |
| 8 |
| 11 |
| 12 |
| 13 |
| 15 |
| 17 |
| 28 |
Expected output
| GapStart | GapEnd |
| 4 | 5 |
| 7 | 7 |
| 9 | 10 |
| 14 | 14 |
| 16 | 16 |
| 18 | 27 |
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
Script
Use the below script to generate the source table and fill them up with the sample data.
CREATE table findGaps (num int) insert into findGaps select 1 union all select 2 union all select 3 union all select 6 union all select 8 union all select 11 union all select 12 union all select 13 union all select 15 union all select 17 union all select 28 GO CREATE CLUSTERED INDEX Ix_Num ON findGaps(num) |
UPDATE – 11-Apr-2015 – Solution 1
-- --Sol 1 SELECT GapStart + 1 GapStart , GapEnd - 1 GapEnd FROM ( SELECT num GapStart , LEAD(num,1,0) OVER (ORDER BY Num) GapEnd , LEAD(num,1,0) OVER (ORDER BY Num) - num Gap FROM findGaps ) a WHERE Gap > 1 -- |
Execution Plan below – Its a clean plain execution plan with some simple maths calculation.
Add a comment if you have any other solution in mind. We all need to learn.
Keep Learning
Improving my SQL BI Skills 
select * from
(
select a.num+1 GapStart
,(select min(c.num) -1 from findgaps c where c.num > a.num) GapEnd
from findGaps a
left join findGaps b on a.num = b.num -1
where b.num is null
) z where z.GapEnd is not null
LikeLike
;with cte
as
(
select *,ROW_NUMBER() over (order by num) as rwnum from #findGaps
)
,cte1
as
(
select a.num as curr ,b.num as next from cte a
join cte b
on a.rwnum+1=b.rwnum
)
select curr+1 as Gapstart,next-1 as GapEnd from cte1
where next-curr>1
LikeLike
with cte as
(select
f.num GapStart,
nextone.num GapEnd
from findGaps f
cross apply (select top 1 g.num from findGaps g where g.num > f.num) nextone
)
select GapStart + 1 AS GapStart, GapEnd – 1 AS GapEnd
from cte
where GapEnd – GapStart > 1
LikeLike
SELECT number AS GapStart,leadnum-1 AS GapEnds FROM
(
SELECT number,lead(num) over(order by (SELECT null )) AS leadnum
FROM
(
SELECT number,number-1 AS numberMinus1 FROM
(
SELECT DISTINCT number FROM master.dbo.spt_values
WHERE number between (SELECT MIN(num) FROM findGaps) and (SELECT MAX(num) FROM findGaps)
)tbl1
WHERE number not in (SELECT num FROM findGaps)
)tbl2
RIGHT JOIN findGaps fg on tbl2.numberMinus1=fg.num
)tbl3
WHERE number IS NOT NULL
LikeLike
;with cte as
(
select distinct number as num from master..spt_values where number =(select min(m1.num) id from findGaps m1)
except
select num from findGaps as m1
), cte2 as
(
select *, num-ROW_NUMBER() over (order by num) as y from cte
)
select distinct min(num) over (partition by y order by y) as Gapstart, max(num) over (partition by y order by y) as Gapend from cte2
LikeLike
;with cte as (
select MIN(num) IDs, MAX(num) maxnum from findGaps
union all
select IDs+1, maxnum from cte where maxnum > IDs ), cte1 as (
select IDs, IDs-dense_rank() over (partition by case when num is null then 0 else num end order by ids) rnk from cte left join findGaps a on a.num=cte.IDs where a.num is null)
select MIN(IDs) GapStart, MAX(IDs) GapEnd from cte1 group by rnk
LikeLike