Problem

Table: NPV

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| year          | int     |
| npv           | int     |
+---------------+---------+
(id, year) is the primary key (combination of columns with unique values) of this table.
The table has information about the id and the year of each inventory and the corresponding net present value.

Table: Queries

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| year          | int     |
+---------------+---------+
(id, year) is the primary key (combination of columns with unique values) of this table.
The table has information about the id and the year of each inventory query.

Write a solution to find the npv of each query of the Queries table.

Return the result table in any order.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

Input: 
NPV table:
+------+--------+--------+
| 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 table:
+------+--------+
| 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     |
+------+--------+--------+
Explanation: 
The npv value of (7, 2018) is not present in the NPV table, we consider it 0.
The npv values of all other queries can be found in the NPV table.

Solution

Method 1 -

Intuition

For each query (id, year), we need to find the corresponding npv from the NPV table. If not present, return 0. This is a classic left join problem.

Approach

Use a LEFT JOIN from Queries to NPV on (id, year). If npv is NULL, use COALESCE to return 0.

Code

1
2
3
4

SELECT q.id, q.year, COALESCE(n.npv, 0) AS npv
FROM Queries q
LEFT JOIN NPV n
  ON q.id = n.id AND q.year = n.year;
1
2
3

# Assuming npv and queries are pandas DataFrames
merged = queries.merge(npv, on=['id', 'year'], how='left')
merged['npv'] = merged['npv'].fillna(0)
1
2

String sql = "SELECT q.id, q.year, COALESCE(n.npv, 0) AS npv FROM Queries q LEFT JOIN NPV n ON q.id = n.id AND q.year = n.year";
// Execute and fetch result

Complexity

  • ⏰ Time complexity: O(Q + N) where Q = number of queries, N = number of NPV rows (due to join).
  • 🧺 Space complexity: O(Q) for result set.