Katkat
Back

Consolidate and Rank Global Salaries in USD

AmazonData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.

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.