← Amazon Interview Insights

Amazon·Data Scientist·Technical Phone Screen·Senior

Senior
May 2026

Summary

Amazon Data Scientist interview with a SQL-heavy question around global HR reporting. One question, pretty focused on combining data across tables and doing currency conversion before ranking.

Questions Asked (1)

Q1

You have separate employee tables for multiple countries, each with local salary data, and a currency-to-USD exchange rate table. How would you consolidate all the employee records into one global table and return the top 10 highest-paid employees worldwide in USD?

Data ModelingAlgorithms & Data Structures
Author's notes

The UNION ALL part I got immediately, that's just stacking tables.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify the schema and data types of the employee tables and exchange rate table, including how currencies are identified and whether rates are static or time-dependent. Then, outline a consolidation strategy using UNION ALL to combine employee records, followed by a JOIN with the exchange rate table to convert salaries to USD. Finally, use a window function or ORDER BY with LIMIT to retrieve the top 10 highest-paid employees globally.

Pro tip: Mention the importance of handling currency conversion consistently, such as using the latest exchange rate or a specific date, and discuss potential data quality issues like missing exchange rates or duplicate employees across tables.

1. Understand the data model

Identify the structure of each employee table (e.g., columns for employee ID, name, salary, currency) and the exchange rate table (e.g., currency code, rate to USD, date). Clarify whether exchange rates are static or vary over time.

2. Consolidate employee records

Use UNION ALL to combine all employee tables into a single dataset, ensuring column alignment and adding a country or source identifier if needed. Handle any schema differences or missing columns.

3. Convert salaries to USD

Join the consolidated employee data with the exchange rate table on the currency code. Multiply the local salary by the exchange rate to get USD salary. If rates are time-dependent, use the appropriate rate (e.g., latest or as-of date).

4. Rank and select top 10

Use a window function like ROW_NUMBER() or RANK() over the USD salary in descending order, or simply ORDER BY USD salary DESC and LIMIT 10. Ensure ties are handled appropriately based on business requirements.

5. Validate and present results

Check for anomalies such as negative salaries, missing exchange rates, or outliers. Present the final list with employee details, local salary, currency, exchange rate used, and USD salary.

Key Points to Mention

  • Use of UNION ALL to combine tables efficiently, avoiding duplicates unless intended.
  • Joining with exchange rate table on currency code, and handling cases where exchange rate is missing (e.g., INNER JOIN vs LEFT JOIN).
  • Consideration of time-varying exchange rates: using the latest rate or a specific date, and how that affects conversion.
  • Use of window functions (e.g., ROW_NUMBER, RANK) or ORDER BY with LIMIT for top-N selection.
  • Data quality checks: ensuring salaries are numeric, currencies are valid, and no duplicate employees across tables.
  • Scalability: if data is large, discuss partitioning, indexing, or using distributed computing (e.g., Spark) for consolidation and ranking.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.