SQL Puzzle: SQL Advance Query - Count the total number of missing Numbers

SQL Puzzle: SQL Advance Query – Count the total number of missing Numbers

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output to find the total number of missing numbers from the table.

Input Data:

Number

-----------

Expected Data:

TotalMissingNumbers

--------------------

Create a table with data:

CREATE TABLE tbl_Ranges (Number INTEGER PRIMARY KEY)

INSERT INTO tbl_Ranges VALUES

(1),(2),(3),(5),(6),(8),(9),(15),(19),(21),(22),(24),(29)

Solution:

;WITH CTE

(

SELECT

Number

,Number - row_number() OVER (ORDER BY Number) rnk

FROM tbl_Ranges

)

SELECT MAX(rnk) TotalMissingNumbers

FROM CTE

Please try the different solution for this puzzle and share it via comment...

Nov 25, 2017Anvesh Patel

Comments: 8

Harikrushna V. Adiecha

November 27, 2017 at 8:38 am

I solve this in mysql database. please check, i tried, and it’s working fine.

Solution #1:

SELECT SUM(p.diff) as TotalMissingNumbers from
(
SELECT t.Number,
t.Number – @last – 1 AS diff,
@last := t.Number AS last
FROM tbl_ranges t,
(SELECT @last := 0) r
) as p

I found one more solution, which is even simpler and straight forward

Solution #2:

SELECT MAX(Number) – COUNT(Number) as TotalMissingNumbers FROM `tbl_ranges`

———————————————————————————-

Apart from this, I wish to know how can we calculate complexity of the query we wrote, if you know any tool or any technique to get such calculations then please let me know.

I’m very curious to know such things.

Reply Cancel

Anvesh Patel
November 27, 2017 at 11:58 am
Not sure about any tool, but checking query execution plan we can estimate the complexity of any query.
Reply Cancel

Dinesh IS

November 30, 2017 at 7:05 am

Declare @MaxNumber As Int
Select @MaxNumber=Max(Number)
From TBL_Ranges
;
WITH CTE
As
(Select 1 As SLNo
Union All
Select SLNo+1
From CTE
Where SLNo<@MaxNumber)
Select Count(SLNo) As NoOfNumber From CTE
Where SLNo Not In(Select Number From TBL_Ranges)

Reply Cancel

aprkturk

January 15, 2018 at 11:27 am

If it starts with 1 it is ok otherwise no.

Reply Cancel

VINUTNA

May 22, 2018 at 12:01 pm

with cte as
(
select number, lead(number)over (order by number) as lead_values from tbl_Ranges
),
cte1 as
(
select (lead_values – number)-1 as required from cte
)
select sum(required) from cte1
where required >= 1

Reply Cancel