How to get final length of a line in a query?

user_15174713 0
Viewed 38

I am just learning SQL and I got a task, that I need to find the final length of a discontinuous line when I have imput such as:

start | finish
  0   |   3
  2   |   7
  15  |   17

And the correct answer here would be 9, because it spans from 0-3 and then I am suppsed to ignore the parts that are present multiple times so from 3-7(ignoring the two because it is between 0 and 3 already) and 15-17. I am supposed to get this answer solely through an sql query(no functions) and I am unsure of how. I have tried to experiment with some code using with, but I can't for the life of me figure out how to ignore all the multiples properly. My half-attempt:

WITH temp AS(
  SELECT s as l, f as r FROM lines LIMIT 1),
  cte as(
  select s, f from lines where s < (select l from temp) or f > (select r from temp)
  )
 select * from cte

This really only gives me all the rows tha are not completly usless and extend the length, but I dont know what to do from here.

with-statement sqlite
1 Answers

Use a recursive CTE that breaks all the (start, finish) intervals to as many 1 unit length intervals as is the total length of the interval and then count all the distinct intervals:

WITH cte AS (
  SELECT start x1, start + 1 x2, finish FROM temp
  WHERE start < finish -- you can omit this if start < finish is always true
  UNION
  SELECT x2, x2 + 1, finish FROM cte
  WHERE x2 + 1 <= finish
)
SELECT COUNT(DISTINCT x1) length 
FROM cte

See the demo.
Result:

length
9
user_104988280
Related