NPV Queries - Practice Coding Problems

NPV Queries - Problem

Database Easy

You're working as a financial analyst for an investment firm that needs to quickly lookup Net Present Value (NPV) data for various assets across different years.

You have two tables:

NPV Table: Contains historical NPV data for assets, with columns id, year, and npv
Queries Table: Contains lookup requests for specific assets and years

Your task is to match each query with its corresponding NPV value. If a query asks for an asset-year combination that doesn't exist in the NPV table, return null for that NPV value.

Goal: Write a SQL query that returns each query along with its corresponding NPV value (or null if not found).

Input & Output

example_1.sql — Basic NPV Lookup

$ 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 | | 1 | 2018 | | 13 | 2019 |

› Output: | id | year | npv | |----|------|------| | 1 | 2019 | 113 | | 2 | 2008 | 121 | | 3 | 2009 | 12 | | 7 | 2018 | null | | 1 | 2018 | 100 | | 13 | 2019 | 40 |

💡 Note: Each query is matched with its corresponding NPV value. Query (7, 2018) returns null because no NPV record exists for id=7 in year 2018.

example_2.sql — All Queries Have Matches

$ Input: NPV table: | id | year | npv | |----|------|-----| | 1 | 2020 | 50 | | 2 | 2020 | 75 | | 3 | 2020 | 25 | Queries table: | id | year | |----|------| | 1 | 2020 | | 2 | 2020 | | 3 | 2020 |

› Output: | id | year | npv | |----|------|-----| | 1 | 2020 | 50 | | 2 | 2020 | 75 | | 3 | 2020 | 25 |

💡 Note: Perfect match scenario where every query finds its corresponding NPV value in the NPV table.

example_3.sql — No Matches Found

$ Input: NPV table: | id | year | npv | |----|------|-----| | 1 | 2018 | 100 | | 2 | 2019 | 200 | Queries table: | id | year | |----|------| | 1 | 2020 | | 3 | 2018 | | 2 | 2020 |

› Output: | id | year | npv | |----|------|------| | 1 | 2020 | null | | 3 | 2018 | null | | 2 | 2020 | null |

💡 Note: Edge case where none of the queries match any records in the NPV table, resulting in all null NPV values.

Constraints

1 ≤ NPV.id, Queries.id ≤ 10⁴
1990 ≤ NPV.year, Queries.year ≤ 2050
-10⁸ ≤ NPV.npv ≤ 10⁸
1 ≤ NPV table rows ≤ 5 × 10⁴
1 ≤ Queries table rows ≤ 5 × 10⁴
Each (id, year) combination in NPV table is unique
Each (id, year) combination in Queries table is unique

Visualization

Tap to expand

Understanding the Visualization

Prepare Tables

Load both Queries (patron requests) and NPV (book catalog) tables

Execute LEFT JOIN

Database matches each query with corresponding NPV record using optimized join algorithm

Handle Missing Data

Queries without matches get NULL NPV values, ensuring all queries appear in result

Return Complete Result

Single result set containing all queries with their NPV values or NULL

Key Takeaway

🎯 Key Insight: LEFT JOIN is the perfect tool for lookup operations where you need to preserve all queries and gracefully handle missing data with NULL values.

Asked in

G Google 15 a Amazon 12 f Meta 8 ⊞ Microsoft 10

The optimal solution uses a LEFT JOIN in SQL to efficiently combine the Queries and NPV tables. This ensures all queries appear in the result with their corresponding NPV values, or NULL when no match exists. The database handles the join optimization internally, making this approach much more efficient than nested subqueries.

Common Approaches

Approach	Time	Space	Notes
✓ LEFT JOIN (Optimal SQL Solution)	O(n + m)	O(1)	Use LEFT JOIN to efficiently match queries with NPV data in one operation
Brute Force (Nested Subqueries)	O(n×m)	O(1)	Use correlated subquery for each row in Queries table

LEFT JOIN (Optimal SQL Solution) — Algorithm Steps

Use LEFT JOIN to combine Queries and NPV tables
Match on both id and year columns
Include all queries even when no NPV match exists
Return NULL for npv when no match is found

Visualization

Tap to expand

Step-by-Step Walkthrough

Prepare both tables

Database loads both Queries and NPV tables

Execute LEFT JOIN

Database uses optimized join algorithm to match records

Include all queries

All queries appear in result, with NULL for missing NPV

Return combined result

Single result set with all matched data

Code -

solution.c — C

// SQL Solution
const char* sqlQuery = 
    "SELECT q.id, q.year, n.npv "
    "FROM Queries q "
    "LEFT JOIN NPV n ON q.id = n.id AND q.year = n.year;";

// C implementation would be complex due to lack of built-in hash map
// This is a simplified version using linear search in a structured way
#include <stdio.h>
#include <stdlib.h>

typedef struct {
    int id, year, npv;
} NPVRecord;

typedef struct {
    int id, year;
} Query;

typedef struct {
    int id, year;
    int* npv;
} Result;

Result* npvQueriesOptimal(NPVRecord* npvData, int npvSize, Query* queries, int querySize) {
    Result* result = malloc(querySize * sizeof(Result));
    
    for (int i = 0; i < querySize; i++) {
        result[i].id = queries[i].id;
        result[i].year = queries[i].year;
        result[i].npv = NULL;
        
        // In C, we'd implement hash table or use optimized search
        for (int j = 0; j < npvSize; j++) {
            if (npvData[j].id == queries[i].id && npvData[j].year == queries[i].year) {
                result[i].npv = malloc(sizeof(int));
                *(result[i].npv) = npvData[j].npv;
                break;
            }
        }
    }
    
    return result;
}

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Linear scan of both tables with database join optimization

✓ Linear Growth

Space Complexity

O(1)

No additional space beyond result set (database handles join internally)

✓ Linear Space

24.7K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

LEFT JOIN (Optimal SQL Solution) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler