Coding questions · Amazon
Append country tables and rank salaries in USD
This question evaluates skills in data manipulation and integration, specifically appending country-level tables, normalizing salaries via historical exchange rates, deduplicating by a composite primary key, and ranking results, with implementations expected in both SQL and pandas.
You have separate country-level employee tables that must be appended and ranked by salary converted to USD using an exchange rate table. SQL schema and small samples: employees_us(emp_id INT, name TEXT, salary DECIMAL, currency_code CHAR(3), country_code CHAR(2)) rows: 1001, Alice, 120000, USD, US; 1002, Bob, 90000, USD, US. employees_uk(emp_id INT, name TEXT, salary DECIMAL, currency_code CHAR(3), country_code CHAR(2)) rows: 2001, Claire, 80000, GBP, GB; 2002, Dan, 95000, GBP, GB. employees_jp(emp_id INT, name TEXT, salary DECIMAL, currency_code CHAR(3), country_code CHAR(2)) rows: 3001, Emi, 12000000, JPY, JP; 3002, Fumi, 8500000, JPY, JP. exchange_rates(currency_code CHAR(3), rate_to_usd DECIMAL(10,4), rate_date DATE) rows: USD, 1.0000, 2025-08-31; GBP, 1.2700, 2025-08-31; JPY, 0.0068, 2025-08-31. Tasks: 1) Write a single SQL query that appends the country tables (assume same columns) and returns the top 10 employees by salary_usd, with columns (country_code, emp_id, name, salary_original, currency_code, salary_usd), using the most recent exchange rate per currency on or before 2025-08-31. 2) Ensure the plan avoids duplicate counting if an employee appears in multiple country tables (use primary key (country_code, emp_id)). 3) Provide a Python (pandas) alternative that reads all CSVs from a parent directory (pattern employees_*.csv), concatenates, joins to exchange_rates.csv, computes salary_usd, and returns the top 10 by salary_usd.
Append country employee tables and rank salaries in USD using latest FX rate
You are given three country-level employee tables and an exchange rate table. Each employee table has the same schema, and salaries are stored in the local currency. The exchange_rates table may have multiple rows per currency_code at different rate_date values.
Tables:
- employees_us(emp_id, name, salary, currency_code, country_code)
- employees_uk(emp_id, name, salary, currency_code, country_code)
- employees_jp(emp_id, name, salary, currency_code, country_code)
- exchange_rates(currency_code, rate_to_usd, rate_date)
Using these tables, write a single SQL query that:
- Appends (unions) all three employee tables into one logical set.
- Ensures that if an employee appears more than once across the country tables with the same (country_code, emp_id), they are only counted once (treat (country_code, emp_id) as the primary key and pick one row per key).
- For each employee, looks up the most recent exchange rate per currency_code with rate_date on or before 2025-08-31, and converts the original salary to USD as salary_usd = salary_original * rate_to_usd.
- Returns the top 10 employees by salary_usd (or fewer if there are not 10 employees), sorted from highest to lowest salary_usd.
Return the following columns in the result:
- country_code
- emp_id
- name
- salary_original (the original salary in local currency)
- currency_code
- salary_usd (salary converted to USD using the chosen exchange rate)
Table employees_us
| column | type | constraints |
|---|---|---|
| emp_id | INT | NOT NULL |
| name | VARCHAR(100) | NOT NULL |
| salary | DECIMAL(15,2) | NOT NULL |
| currency_code | CHAR(3) | NOT NULL |
| country_code | CHAR(2) | NOT NULL |
Sample data:
| country_code | currency_code | emp_id | name | salary |
|---|---|---|---|---|
| US | USD | 1001 | Alice | 120000 |
| US | USD | 1002 | Bob | 90000 |
Table employees_uk
| column | type | constraints |
|---|---|---|
| emp_id | INT | NOT NULL |
| name | VARCHAR(100) | NOT NULL |
| salary | DECIMAL(15,2) | NOT NULL |
| currency_code | CHAR(3) | NOT NULL |
| country_code | CHAR(2) | NOT NULL |
Sample data:
| country_code | currency_code | emp_id | name | salary |
|---|---|---|---|---|
| GB | GBP | 2001 | Claire | 80000 |
| GB | GBP | 2002 | Dan | 95000 |
Table employees_jp
| column | type | constraints |
|---|---|---|
| emp_id | INT | NOT NULL |
| name | VARCHAR(100) | NOT NULL |
| salary | DECIMAL(15,2) | NOT NULL |
| currency_code | CHAR(3) | NOT NULL |
| country_code | CHAR(2) | NOT NULL |
Sample data:
| country_code | currency_code | emp_id | name | salary |
|---|---|---|---|---|
| JP | JPY | 3001 | Emi | 12000000 |
| JP | JPY | 3002 | Fumi | 8500000 |
Table exchange_rates
| column | type | constraints |
|---|---|---|
| currency_code | CHAR(3) | NOT NULL |
| rate_to_usd | DECIMAL(10,4) | NOT NULL |
| rate_date | DATE | NOT NULL |
Sample data:
| currency_code | rate_date | rate_to_usd |
|---|---|---|
| USD | 2025-08-15 | 1 |
| USD | 2025-08-31 | 1 |
| GBP | 2025-08-15 | 1.25 |
| GBP | 2025-08-31 | 1.27 |
| JPY | 2025-08-15 | 0.0067 |
| JPY | 2025-08-31 | 0.0068 |
| GBP | 2025-09-01 | 1.3 |
Expected output
Top employees by salary converted to USD as of 2025-08-31, using the most recent exchange rate on or before 2025-08-31 for each currency. Results are sorted by salary_usd descending and limited to the top 10 (here, only 6 employees exist).
| country_code | currency_code | emp_id | name | salary_original | salary_usd |
|---|---|---|---|---|---|
| GB | GBP | 2002 | Dan | 95000 | 120650 |
| US | USD | 1001 | Alice | 120000 | 120000 |
| GB | GBP | 2001 | Claire | 80000 | 101600 |
| US | USD | 1002 | Bob | 90000 | 90000 |
| JP | JPY | 3001 | Emi | 12000000 | 81600 |
| JP | JPY | 3002 | Fumi | 8500000 | 57800 |
Hints
- Start by UNION ALL-ing the three employee tables into a single CTE, then deduplicate by (country_code, emp_id) using ROW_NUMBER().
- To get the most recent rate per currency on or before 2025-08-31, use a correlated subquery or a window function (e.g., ROW_NUMBER() OVER (PARTITION BY currency_code ORDER BY rate_date DESC)) and filter to the first row.