OneNoughtOne
0/308
Loading content...
A finance data platform stores annual net present value (NPV) records for inventory entities and receives lookup requests from downstream tools.
Table: inventory_npv
Table: lookup_requests
Task: For each row in lookup_requests, return one output row with:
Missing-key rule:
Output requirements:
Supported submission environments:
inventory_npv:
| id | year | npv |
|----|------|-----|
| 1 | 2018 | 100 |
| 7 | 2020 | 30 |
| 13 | 2019 | 40 |
| 1 | 2019 | 113 |
| 2 | 2008 | 121 |
| 3 | 2009 | 12 |
| 11 | 2020 | 99 |
| 7 | 2019 | 0 |
lookup_requests:
| id | year |
|----|------|
| 1 | 2019 |
| 2 | 2008 |
| 3 | 2009 |
| 7 | 2018 |
| 7 | 2019 |
| 7 | 2020 |
| 13 | 2019 |[
{"id":1,"year":2019,"npv":113},
{"id":2,"year":2008,"npv":121},
{"id":3,"year":2009,"npv":12},
{"id":7,"year":2018,"npv":0},
{"id":7,"year":2019,"npv":0},
{"id":7,"year":2020,"npv":30},
{"id":13,"year":2019,"npv":40}
]Explanation
Pair (7, 2018) is absent in inventory_npv, so the output uses npv = 0 for that request. All other requests are direct matches.
inventory_npv:
| id | year | npv |
|----|------|-----|
| 10 | 2022 | -50 |
| 10 | 2023 | 70 |
| 14 | 2022 | 0 |
lookup_requests:
| id | year |
|----|------|
| 10 | 2022 |
| 10 | 2023 |
| 10 | 2024 |
| 14 | 2022 |[
{"id":10,"year":2022,"npv":-50},
{"id":10,"year":2023,"npv":70},
{"id":10,"year":2024,"npv":0},
{"id":14,"year":2022,"npv":0}
]Explanation
Existing keys keep their original values, including negative and zero. Missing key (10, 2024) is imputed with 0.
inventory_npv:
| id | year | npv |
|----|------|-----|
| (no rows) |
lookup_requests:
| id | year |
|----|------|
| 5 | 2021 |
| 6 | 2022 |[
{"id":5,"year":2021,"npv":0},
{"id":6,"year":2022,"npv":0}
]Explanation
When inventory_npv is empty, every lookup request returns npv = 0.
Constraints