Consolidate and Rank Global Salaries in USD
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
employees_us
+---------+----------+--------+---------+
| emp_id | name | salary | country |
+---------+----------+--------+---------+
| 1 | Alice | 120000 | US |
| 2 | Bob | 95000 | US |
| 3 | Carol | 115000 | US |
exchange_rates
+----------+----------+
| currency | usd_rate |
+----------+----------+
| USD | 1.0 |
| EUR | 1.12 |
| JPY | 0.0091 |
##### Scenario
Global HR reporting: consolidate country files and rank worldwide salaries.
##### Question
Append several country-specific employee tables into a single global table. Using an exchange-rate reference table, find the top 10 salaries worldwide in USD.
##### Hints
Show UNION-ALL, JOIN with exchange rates, order by converted salary, and limit 10.
Overview: This question evaluates data consolidation, currency normalization, and ranking competencies, measuring the ability to combine country-specific employee data and convert salaries into a common USD basis within the Data Manipulation (SQL/Python) domain.
You are given country-specific employee tables and a reference table of currency exchange rates to USD. For this example, only the US employee table is shown (employees_us), but assume similar tables exist for other countries. Append all country-specific employee tables into a single global dataset. Then, by joining to the exchange_rates table and converting all salaries to USD, return the top 10 employees worldwide ordered by salary in USD (highest first). Include employee ID, name, country, currency, and the converted salary in USD.
Tables
employees_us(emp_id INTEGER, name VARCHAR(100), salary INTEGER, country VARCHAR(2))
exchange_rates(currency VARCHAR(3), usd_rate DECIMAL(12,6))
Hints
- Use UNION ALL to append multiple country-specific employee tables into a single derived table (CTE or subquery).
- Include a currency column in the unified employee dataset so you can join to exchange_rates.