task Validation & types · hard
Detect overlapping history periods
Within one employee's salary_history, periods must not overlap. Return employee_id, first_from and second_from for every pair of overlapping periods, where first_from is the earlier valid_from.
what to return
- Columns
employee_idfirst_fromsecond_fromin this order- Rows
- 2 rows · any order
- Watch for
- A period runs from valid_from up to but not including valid_to; NULL valid_to means it is still open.
- Periods that touch (one ends the day the next starts) do not overlap.
- Report each overlapping pair once.
- Skill
- Validate that history intervals don't overlap. Refresher: Self-joins & referral relationships →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result2 rows · any order
| employee_id | first_from | second_from |
|---|---|---|
| 6 | 2022-07-18 | 2023-11-01 |
| 13 | 2023-01-01 | 2024-06-15 |
all 2 rows
data you can query
Sample rows from each table. Each column shows its type underneath; → marks a link to another table.
salary_history4 columns · 21 rows
| employee_idinteger | salaryreal | valid_fromtext | valid_totext |
|---|---|---|---|
| 2 | 165000 | 2019-06-15 | 2022-01-01 |
| 2 | 180000 | 2022-01-01 | NULL |
| 4 | 120000 | 2020-05-04 | 2022-04-01 |
| 4 | 140000 | 2022-04-01 | NULL |
| 5 | 118000 | 2021-02-01 | 2022-02-01 |
showing 5 of 21 rows
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.