Loadingone moment…
Sign in to run and submit your work
Reading is open to everyone. Running code and saving drafts need an account so your work is yours and comes back on your next visit.
or
CODE WORKSPACE
Profile history is supposed to be a sequence of non-overlapping positions, and the experience timeline renders wrongly when it is not. linkedin_positions has one row per position, and ended_on is NULL while the member still holds the role.
Return one row per overlapping pair, ordered by overlap length descending.
Result columns · in this order
member_id | Member whose profile has the overlap. |
earlier_position | The lower position id of the pair. |
later_position | The higher position id of the pair. |
overlap_days | Days the two ranges share. |
How to approach it
Two ranges overlap when each starts before the other ends. Then decide what an open end means.
Sample input
| position_id | member_id | company | started_on | ended_on |
|---|---|---|---|---|
| 1 | m1 | Northwind | 2023-01-01 | 2024-06-30 |
| 2 | m1 | Contoso | 2024-07-01 | 2025-12-31 |
| 3 | m1 | Fabrikam | 2026-01-01 | null |
| 4 | m2 | Initech | 2024-03-01 | 2025-04-30 |
| 5 | m2 | Umbrella | 2025-01-28 | 2025-11-30 |
| 6 | m3 | Globex | 2024-05-01 | null |
| 7 | m3 | Soylent | 2025-06-01 | 2025-09-30 |
| 8 | m4 | Hooli | 2024-02-01 | 2025-02-01 |
| 9 | m4 | Pied Piper | 2025-02-01 | 2026-01-31 |
| 10 | m5 | Stark | 2023-06-01 | 2024-08-31 |
| 11 | m5 | Wayne | 2024-08-01 | 2025-07-31 |
| 12 | m5 | Acme | 2025-09-01 | null |
| 13 | m6 | Vandelay | 2024-01-01 | 2026-01-01 |
| 14 | m6 | Bluth | 2024-09-01 | 2025-03-01 |
| 15 | m7 | Cyberdyne | 2025-01-01 | 2025-12-31 |
| 16 | m7 | Tyrell | 2026-01-01 | null |
| 17 | m8 | Aperture | 2024-04-01 | 2025-04-01 |
| 18 | m8 | Black Mesa | 2025-05-01 | null |
| 19 | m9 | Weyland | 2023-09-01 | 2025-01-31 |
| 20 | m9 | Yutani | 2024-12-01 | 2025-10-31 |
20 rows — scroll inside the table to see them all.
Expected output
| member_id | earlier_position | later_position | overlap_days |
|---|---|---|---|
| m6 | 13 | 14 | 181 |
| m3 | 6 | 7 | 121 |
| m2 | 4 | 5 | 92 |
| m9 | 19 | 20 | 61 |
| m5 | 10 | 11 | 30 |
5 rows — all rows shown.
Constraints
NULL ended_on means the position is still running — treat it as open ended, not as missing.m4 ends one position on 2025-02-01 and starts the next the same day, which is fine.overlap_days is the number of days the two ranges share, as a whole number.overlap_days descending, then member_id.Worked example
m3 joined Globex on 2024-05-01 and never closed the row, then added Soylent from 2025-06-01 to 2025-09-30. Those overlap by 121 days.
Write the condition as a.started_on < b.ended_on AND b.started_on < a.ended_on and m3 never appears: a.ended_on is NULL, the comparison is unknown, and the pair is filtered out. The open-ended rows are the ones most likely to be wrong, and they are exactly the ones that condition cannot see. Joining on b.position_id <> a.position_id is the other trap — it reports every pair twice, once in each direction.
What this tests
Interval overlap as a self-join: the two-sided comparison that defines it, treating an open end as infinity rather than as unknown, and using an inequality on the key to report each pair once.
Submit for review to find out what your query gets right, what it gets wrong, and how it compares with the best working query for this exercise.
This scenario runs a full workspace — editor, canvas and results side by side. It needs a laptop or desktop to be usable. Open this page on a bigger screen to start building.