Problem

Table: NPV

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

+---------------+---------+
| 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 - Left Join with COALESCE (Lookup)

Intuition

For each query (id, year) we want to look up the corresponding npv in the NPV table. If there is no matching row, we should return 0. This is naturally expressed as a LEFT JOIN where missing values become NULL and can be replaced with 0 using COALESCE.

Approach

Use a LEFT JOIN from Queries to NPV on (id, year) so every query is preserved. Use COALESCE(n.npv, 0) to convert NULL results into 0 for queries that don’t have a matching row in NPV.

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
4
5

-- Same query works in PostgreSQL; COALESCE behaves identically
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)

Complexity

  • ⏰ Time complexity: O(Q + N) – The LEFT JOIN processes the Queries rows and looks up matching NPV rows (linear in inputs).
  • 🧺 Space complexity: O(Q) – We produce a result row per query (plus temporary join buffers).