Inventory Net Present Value Lookup

EASY15 pts

A finance data platform stores annual net present value (NPV) records for inventory entities and receives lookup requests from downstream tools.

Table: inventory_npv

id: inventory identifier
year: fiscal year
npv: net present value for that inventory-year pair
(id, year) is unique

Table: lookup_requests

id: inventory identifier requested by a report
year: fiscal year requested by a report
(id, year) is unique

Task: For each row in lookup_requests, return one output row with:

id
year
npv for the same (id, year) from inventory_npv

Missing-key rule:

If an exact (id, year) pair does not exist in inventory_npv, return npv = 0.

Output requirements:

Return exactly three columns: id, year, npv
Include every request row exactly once
Sort by id ascending, then year ascending

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

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 |

Output

[
  {"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.

Example

Input

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 |

Output

[
  {"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.

Example

Input

inventory_npv:
| id | year | npv |
|----|------|-----|
| (no rows) |

lookup_requests:
| id | year |
|----|------|
| 5  | 2021 |
| 6  | 2022 |

Output

[
  {"id":5,"year":2021,"npv":0},
  {"id":6,"year":2022,"npv":0}
]

Explanation

When inventory_npv is empty, every lookup request returns npv = 0.

Accepted0/0·0% Acceptance

Constraints

0 <= rows in inventory_npv <= 200000
0 <= rows in lookup_requests <= 200000
1 <= id <= 1000000000
1900 <= year <= 2200
-1000000000 <= npv <= 1000000000
(id, year) is unique in inventory_npv
(id, year) is unique in lookup_requests
Missing (id, year) keys must map to npv = 0
Return exactly three columns: id, year, npv
Sort output by id ASC, then year ASC
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: id, year, npv

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

inventory_npv =

[{"id":1,"year":2018,"npv":100},{"id":7,"year":2020,"npv":30},{"id":13,"year":2019,"npv":40},{"id":1,"year":2019,"npv":113},{"id":2,"year":2008,"npv":121},{"id":3,"year":2009,"npv":12},{"id":11,"year":2020,"npv":99},{"id":7,"year":2019,"npv":0}]

lookup_requests =

[{"id":1,"year":2019},{"id":2,"year":2008},{"id":3,"year":2009},{"id":7,"year":2018},{"id":7,"year":2019},{"id":7,"year":2020},{"id":13,"year":2019}]

Inventory Net Present Value Lookup

Hints

Inventory Net Present Value Lookup

Hints