Tasks Details
hard
Compute the total length covered by 1-dimensional segments.
Task Score
100%
Correctness
100%
Performance
Not assessed
You are given a table segments with the following structure:
create table segments ( l integer not null, r integer not null, check(l <= r), unique(l,r) );Each record in this table represents a contiguous segment of a line, from l to r inclusive. Its length equals r − l.
Consider the parts of a line covered by the segments. Write an SQL query that returns the total length of all the parts of the line covered by the segments specified in the table segments. Please note that any parts of the line that are covered by several overlapping segments should be counted only once.
For example, given:
l | r --+-- 1 | 5 2 | 3 4 | 6your query should return 5, as the segments cover the part of the line from 1 to 6.
Copyright 2009–2026 by Codility Limited. All Rights Reserved. Unauthorized copying, publication or disclosure prohibited.
Solution
Programming language used SQL (SQLite)
Time spent on task 20 minutes
Notes
not defined yet
Code: 16:56:00 UTC,
sql,
verify,
result: Passed
select sum(diff) from (
SELECT (max(r) - min(l)) as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
)
union all
select * from (
SELECT r-l as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE not EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
) )
)
Analysis
Code: 16:56:17 UTC,
sql,
verify,
result: Failed
select nvl(sum(diff), 0) from (
SELECT (max(r) - min(l)) as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
)
union all
select * from (
SELECT r-l as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE not EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
) )
)
Analysis
expand all
Example tests
1.
0.097 s
RUNTIME ERROR,
tested program terminated unexpectedly
stderr:
error on query: select nvl(sum(diff), 0) from (
SELECT (max(r) - min(l)) as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
)
union all
select * from (
SELECT r-l as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE not EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
) )
)
, details: no such function: nvl
Code: 17:01:25 UTC,
sql,
verify,
result: Passed
select sum(diff) from (
SELECT (max(r) - min(l)) as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
)
union all
select * from (
SELECT r-l as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE not EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
) )
)
Analysis
Code: 17:04:43 UTC,
sql,
verify,
result: Failed
select nanvl(sum(diff),0) from (
SELECT (max(r) - min(l)) as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
)
union all
select * from (
SELECT r-l as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE not EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
) )
)
Analysis
expand all
Example tests
1.
0.091 s
RUNTIME ERROR,
tested program terminated unexpectedly
stderr:
error on query: select nanvl(sum(diff),0) from (
SELECT (max(r) - min(l)) as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
)
union all
select * from (
SELECT r-l as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE not EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
) )
)
, details: no such function: nanvl
Code: 17:06:13 UTC,
sql,
verify,
result: Failed
select decode(sum(diff),null, 0, sum(diff)) from (
SELECT (max(r) - min(l)) as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
)
union all
select * from (
SELECT r-l as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE not EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
) )
)
Analysis
expand all
Example tests
1.
0.095 s
RUNTIME ERROR,
tested program terminated unexpectedly
stderr:
error on query: select decode(sum(diff),null, 0, sum(diff)) from (
SELECT (max(r) - min(l)) as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
)
union all
select * from (
SELECT r-l as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE not EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
) )
)
, details: no such function: decode
Code: 17:06:35 UTC,
sql,
verify,
result: Passed
select sum(diff) from (
SELECT (max(r) - min(l)) as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
)
union all
select * from (
SELECT r-l as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE not EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
) )
)
Analysis
Code: 17:06:58 UTC,
sql,
verify,
result: Failed
select sum(diff) from (
SELECT nvl((max(r) - min(l)), 0) as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
)
union all
select * from (
SELECT r-l as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE not EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
) )
)
Analysis
expand all
Example tests
1.
0.092 s
RUNTIME ERROR,
tested program terminated unexpectedly
stderr:
error on query: select sum(diff) from (
SELECT nvl((max(r) - min(l)), 0) as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
)
union all
select * from (
SELECT r-l as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE not EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
) )
)
, details: no such function: nvl
Code: 17:10:44 UTC,
sql,
verify,
result: Failed
select nvl(total, 0) from (
select sum(diff) as total from (
SELECT (max(r) - min(l)) as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
)
union all
select * from (
SELECT r-l as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE not EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
) )
))
Analysis
expand all
Example tests
1.
0.092 s
RUNTIME ERROR,
tested program terminated unexpectedly
stderr:
error on query: select nvl(total, 0) from (
select sum(diff) as total from (
SELECT (max(r) - min(l)) as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
)
union all
select * from (
SELECT r-l as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE not EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
) )
))
, details: no such function: nvl
Code: 17:14:51 UTC,
sql,
verify,
result: Passed
select sum(case when diff is null
then 0
else diff
end
) as total from (
SELECT (max(r) - min(l)) as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
)
union all
select * from (
SELECT r-l as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE not EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
) )
)
Analysis
Code: 17:15:14 UTC,
sql,
verify,
result: Passed
select sum(case when diff is null
then 0
else diff
end
) as total from (
SELECT (max(r) - min(l)) as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
)
union all
select * from (
SELECT r-l as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE not EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
) )
)
Analysis
Code: 17:15:16 UTC,
sql,
final,
score:
100
select sum(case when diff is null
then 0
else diff
end
) as total from (
SELECT (max(r) - min(l)) as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
)
union all
select * from (
SELECT r-l as diff
FROM (SELECT rowid, l, r
FROM segments
ORDER BY l) a
WHERE not EXISTS (SELECT rowid, 1
FROM segments b
WHERE b.l <= a.r
AND b.r >= a.l
and a.rowid != b.rowid
) )
)Analysis summary
The solution obtained perfect score.
Analysis
expand all
Correctness tests
1.
0.097 s
OK
1.
0.097 s
OK
1.
0.097 s
OK
1.
0.097 s
OK
1.
0.098 s
OK
2.
0.096 s
OK
1.
0.097 s
OK
2.
0.096 s
OK
1.
0.097 s
OK
1.
0.097 s
OK
1.
0.097 s
OK
1.
0.097 s
OK
1.
0.097 s
OK
1.
0.253 s
OK
1.
0.252 s
OK
1.
0.100 s
OK
1.
0.101 s
OK