Active SCD2 record version on a given date
Rosbank's DDS layer is a classic SCD2 warehouse on Greenplum/Postgres. Each record carries a DATERANGE validity interval (half-open): the active version's upper bound is infinity. The analyst wants a snapshot of every contract on a given date.
Give me a contract snapshot as of 2024-03-15: exactly one row per contract — the version whose valid interval covers that date. Contracts that had no active version on that date should be left out. For dt_to, return upper(valid) or NULL when the upper bound is infinity.
Expected output sample
This is what a correct answer looks like — its row count is its own, it doesn't have to match the schema tables.
| dt_to | status | dt_from | rate_pct | contract_id |
|---|---|---|---|---|
| 2024-04-01 | OPEN | 2024-02-01 | 11.00 | 401 |
| NULL | OPEN | 2024-01-15 | 9.90 | 402 |
| 2024-04-15 | OPEN | 2024-03-15 | 7.50 | 405 |
Rosbank
Sign in to see submission history
Sign inSign in to use AI Mentor
Sign inFocus radio
Paused · SomaFM · Fluid