task History, SCD & as-of · hard
Salary at the time of each review
For every performance review, return review_id, employee_id, review_date and salary_at_review: the salary from salary_history whose period contains the review date.
what to return
- Columns
review_idemployee_idreview_datesalary_at_reviewin this order- Rows
- 13 rows · any order
- Watch for
- A period covers a date when valid_from ≤ date and (valid_to is NULL or date < valid_to).
- If more than one period covers the date (bad data), use the one with the latest valid_from.
- If no period covers it, salary_at_review is NULL — keep the review.
- Skill
- Join facts to the history version valid at the event date. Refresher: Protect fact grain when joining versions →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result13 rows · any order
| review_id | employee_id | review_date | salary_at_review |
|---|---|---|---|
| 1 | 2 | 2021-12-15 | 165000 |
| 2 | 4 | 2022-04-01 | 140000 |
| 3 | 5 | 2022-01-31 | 118000 |
first 3 of 13 rows
data you can query
Sample rows from each table. Each column shows its type underneath; → marks a link to another table.
performance_reviews4 columns · 13 rows
| idinteger · key | employee_idinteger | review_datetext | ratinginteger |
|---|---|---|---|
| 1 | 2 | 2021-12-15 | 4 |
| 2 | 4 | 2022-04-01 | 5 |
| 3 | 5 | 2022-01-31 | 4 |
| 4 | 5 | 2024-02-01 | 5 |
| 5 | 6 | 2023-12-15 | 3 |
showing 5 of 13 rows
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.