Context

At companies like Northstar Analytics, analysts often need to combine a current client list with historical revenue records to build reports or validate coverage. Interviewers use this question to check whether you understand how to match records correctly and preserve missing cases.

Core Question

Explain how you would use SQL lookup logic to merge a client list with historical revenue data. In your answer, discuss:

1. Which SQL operation you would use to match clients to revenue records
2. When to use LEFT JOIN versus INNER JOIN
3. How to handle clients with no revenue history
4. How to aggregate historical revenue after merging
5. Common data quality issues such as duplicate client rows or missing keys

Scope Guidance

The interviewer is looking for a practical SQL explanation, not spreadsheet-specific lookup functions. You should describe the join strategy, null handling, and aggregation choices clearly, and mention how your approach changes depending on whether the goal is full client coverage or only matched revenue records.