NPV Queries - Problem
You have two tables: NPV and Queries.
The NPV table contains information about the net present value (NPV) for each inventory item by id and year.
The Queries table contains queries asking for the NPV of specific inventory items by id and year.
Write a SQL solution to find the NPV for each query in the Queries table. If no matching NPV exists, return 0 as the NPV value.
Return the result in any order.
Table Schema
NPV
| Column Name | Type | Description |
|---|---|---|
id
PK
|
int | Inventory item identifier |
year
PK
|
int | Year of the inventory |
npv
|
int | Net present value for the item |
Primary Key: (id, year)
Queries
| Column Name | Type | Description |
|---|---|---|
id
PK
|
int | Inventory item identifier to query |
year
PK
|
int | Year to query for |
Primary Key: (id, year)
Input & Output
Example 1 — Basic NPV Lookup
Input Tables:
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 |
Queries
| id | year |
|---|---|
| 1 | 2019 |
| 2 | 2008 |
| 3 | 2009 |
| 7 | 2018 |
| 7 | 2019 |
| 7 | 2020 |
| 13 | 2019 |
Output:
| id | year | npv |
|---|---|---|
| 1 | 2019 | 113 |
| 2 | 2008 | 121 |
| 3 | 2009 | 12 |
| 7 | 2018 | 0 |
| 7 | 2019 | 0 |
| 7 | 2020 | 30 |
| 13 | 2019 | 40 |
💡 Note:
For each query, we look up the NPV value from the NPV table. Query (7, 2018) has no matching record, so it returns 0. Query (7, 2019) matches a record with npv=0, so it returns 0. All other queries find their corresponding NPV values.
Example 2 — All Missing NPV Records
Input Tables:
NPV
| id | year | npv |
|---|---|---|
| 1 | 2018 | 100 |
| 2 | 2019 | 200 |
Queries
| id | year |
|---|---|
| 1 | 2019 |
| 3 | 2020 |
| 4 | 2021 |
Output:
| id | year | npv |
|---|---|---|
| 1 | 2019 | 0 |
| 3 | 2020 | 0 |
| 4 | 2021 | 0 |
💡 Note:
None of the queries have matching records in the NPV table, so all NPV values are returned as 0 using COALESCE.
Constraints
-
1 ≤ NPV.id, Queries.id ≤ 1000 -
1980 ≤ year ≤ 2020 -
-1000 ≤ npv ≤ 1000
Visualization
Tap to expand
Understanding the Visualization
1
Input Tables
Queries and NPV tables with different records
2
LEFT JOIN
Match on id and year, preserve all queries
3
Result
NPV values with 0 for missing records
Key Takeaway
🎯 Key Insight: Use LEFT JOIN when you need to preserve all records from one table and fill missing values from another
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code