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
NPV Queries - SQL Solution INPUT TABLES NPV Table id year npv 1 2018 100 1 2019 150 2 2018 200 3 2020 300 Queries Table id year 1 2018 2 2019 3 2018 1 2019 ALGORITHM STEPS 1 LEFT JOIN Join Queries with NPV on id AND year 2 Match Records Find NPV where both id and year match 3 Handle NULL Use IFNULL/COALESCE to replace NULL with 0 4 Return Result Output id, year, npv for each query SELECT q.id, q.year, IFNULL(n.npv, 0) AS npv FROM Queries q LEFT JOIN NPV n ON ... FINAL RESULT id year npv 1 2018 100 2 2019 0 3 2018 0 1 2019 150 Match found No match (0) OK - Query Complete 4 rows returned Key Insight: LEFT JOIN preserves all rows from the Queries table even when no matching NPV exists. IFNULL() or COALESCE() handles missing values by replacing NULL with 0, ensuring every query gets a result. The compound join condition (id AND year) ensures precise matching across both dimensions. TutorialsPoint - NPV Queries | Optimal Solution (LEFT JOIN with NULL handling)

Related Problems

Asked in
Amazon 15 Facebook 12 Microsoft 8
26.8K Views
Medium Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen