Amazon·Data Scientist·Technical Phone Screen·Senior

Senior

Aug 2025Remote

Summary

Amazon data scientist technical screen, heavy SQL and Python. One big multi-part question that took up basically the whole session. Felt like a take-home problem crammed into a live interview.

Questions Asked (2)

You have separate employee tables for different countries (US, UK, Japan). Write a single SQL query that unions them all together and returns the top 10 employees ranked by salary converted to USD, using the most recent available exchange rate per currency on or before a given date. Output should include country code, employee ID, name, original salary, currency code, and the USD salary.

Data ModelingTechnical Trade-offsSystem Design

Author's notes

This took me a while to structure cleanly.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by outlining the data model: separate employee tables per country, each with a currency code, and an exchange rate table with currency, rate, and effective date. Then describe a query that unions the employee tables, joins to the most recent exchange rate on or before the given date using a correlated subquery or window function, converts salaries to USD, and ranks to return the top 10. Emphasize correctness, performance, and handling edge cases like missing rates.

Pro tip: Mention that you would validate the exchange rate join by checking for missing rates or duplicate effective dates, and consider using a window function like ROW_NUMBER() partitioned by currency ordered by date descending to efficiently pick the latest rate.

1. Clarify the schema and assumptions

Confirm the structure of the employee tables (columns like employee_id, name, salary, currency_code) and the exchange rate table (currency_code, rate, effective_date). State assumptions about data types and that each employee table has a consistent schema.

2. Union the employee tables

Use UNION ALL to combine the three country-specific employee tables into a single result set, ensuring column order and data types align. Include a country code literal for each table to identify the source.

3. Join to the most recent exchange rate

For each employee's currency, find the exchange rate with the maximum effective_date that is less than or equal to the given date. This can be done with a correlated subquery or a window function (e.g., ROW_NUMBER() OVER (PARTITION BY currency_code ORDER BY effective_date DESC)).

4. Convert salary to USD and rank

Multiply the original salary by the exchange rate to get the USD salary. Then use ORDER BY usd_salary DESC and LIMIT 10 (or equivalent) to return the top 10 employees.

5. Handle edge cases and optimize

Discuss handling missing exchange rates (e.g., exclude or flag), duplicate rates on the same date, and performance considerations such as indexing on currency_code and effective_date. Mention that the query should be efficient for large datasets.

Key Points to Mention

Use UNION ALL instead of UNION to avoid unnecessary deduplication overhead.
Join to the exchange rate table using a subquery or window function to get the most recent rate on or before the given date.
Include the country code as a literal in each SELECT of the union to identify the source table.
Convert salary to USD by multiplying by the exchange rate, and alias the result clearly.
Order by the converted USD salary descending and limit to 10 rows.
Consider performance implications: indexing on (currency_code, effective_date) and avoiding correlated subqueries if possible.

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

Write a Python solution using pandas that reads all employee CSV files from a directory (matching a filename pattern), concatenates them, joins to an exchange rates CSV, computes the USD salary, and returns the top 10 employees by that value.

Data ModelingTechnical Trade-offs

Author's notes

Straightforward once you know glob.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the requirements and assumptions, then outline a modular pandas solution that handles file I/O, concatenation, joining, computation, and ranking. Emphasize code quality, edge cases, and performance considerations, and be prepared to discuss trade-offs.

Pro tip: Mention that you would validate the schema of each CSV and handle missing or malformed files gracefully, as real-world data is often messy. Also, discuss the trade-offs between using pandas vs. other tools like Dask for scalability.

1. Clarify Requirements and Assumptions

Ask about the filename pattern, directory structure, CSV schemas, join keys, and expected output format. Confirm assumptions about data consistency and error handling.

2. Read and Concatenate Employee Files

Use glob or pathlib to list files matching the pattern, read each with pd.read_csv, and concatenate into a single DataFrame. Consider adding a source column for traceability.

3. Join with Exchange Rates

Read the exchange rates CSV, ensure the join key (e.g., currency and date) is consistent, and perform a left join to attach rates to each employee record.

4. Compute USD Salary and Rank

Calculate USD salary by multiplying local salary by the exchange rate, handle any missing rates, then sort and select the top 10 employees.

5. Discuss Edge Cases and Performance

Address missing files, duplicate records, currency mismatches, and large data scalability. Mention optimizations like using categorical dtypes or chunking.

Key Points to Mention

Use of glob or pathlib for file pattern matching
Efficient concatenation with pd.concat and ignoring index
Correct join type (left join) and handling of missing exchange rates
Data validation and error handling for malformed CSVs
Performance considerations for large datasets (e.g., Dask, chunking)
Code modularity and readability (functions, docstrings)

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