Build SQL pivot with lookups and currency conversion
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given the following schema and sample data. Use SQL (or Python with SQL-like transforms) to answer the tasks below. Treat amounts as gross revenue. Use the most recent FX rate on or before txn_date to convert to USD. If no rate exists on or before txn_date, the transaction should be flagged as missing FX.
transactions
+--------+------------+-------------+------------+--------+----------+--------+
| txn_id | txn_date | customer_id | product_id | region | currency | amount |
+--------+------------+-------------+------------+--------+----------+--------+
| 1 | 2025-08-28 | C1 | P1 | NA | USD | 1200 |
| 2 | 2025-08-30 | C2 | P2 | EU | EUR | 900 |
| 3 | 2025-07-15 | C3 | P1 | APAC | JPY | 150000 |
| 4 | 2025-06-20 | C1 | P3 | NA | USD | 500 |
| 5 | 2025-08-05 | C3 | P2 | EU | EUR | 700 |
| 6 | 2025-07-31 | C2 | P3 | NA | USD | 300 |
+--------+------------+-------------+------------+--------+----------+--------+
fx_rates (rate_to_usd = USD per 1 unit of currency)
+----------+------------+-------------+
| currency | rate_date | rate_to_usd |
+----------+------------+-------------+
| EUR | 2025-08-29 | 1.10 |
| EUR | 2025-07-31 | 1.12 |
| JPY | 2025-07-10 | 0.0065 |
| JPY | 2025-06-30 | 0.0068 |
+----------+------------+-------------+
products
+------------+--------------+----------+
| product_id | product_name | category |
+------------+--------------+----------+
| P1 | Alpha | SaaS |
| P2 | Beta | Services |
| P3 | Gamma | Hardware |
+------------+--------------+----------+
customers
+-------------+---------+------------+
| customer_id | name | segment |
+-------------+---------+------------+
| C1 | Acme | Enterprise |
| C2 | Globex | Mid-Market |
| C3 | Initech | SMB |
+-------------+---------+------------+
Tasks:
A) Write SQL to convert all transactions to USD using the latest rate on or before txn_date (point-in-time join). Output: txn_id, txn_date, customer_id, product_id, region, category, segment, amount_usd, fx_missing_flag.
B) Produce a pivot-like monthly report for 2025-06 through 2025-08 with rows = category and columns = region (NA, EU, APAC) plus a Total column, summing amount_usd. Do not hardcode months; derive them from txn_date.
C) For August 2025 only, return the top 1 customer by amount_usd within each region. Break ties by: (1) highest single-transaction amount_usd in that month, then (2) customer_id ascending.
D) Data-quality check: list any transactions where no FX rate exists on or before txn_date within the prior 30 days for non-USD currencies (these should have fx_missing_flag = 1), and explain in one sentence how you would monitor this in production.
Overview: This question evaluates proficiency in data manipulation using SQL or SQL-like Python transforms, covering point-in-time currency conversion, temporal joins for FX lookups, joins to enrich product/customer metadata, aggregation and pivot-style reporting, ranking, and data-quality checks for missing FX.
Read the full Data Scientist interview experience this question came from
Convert transactions to USD using point-in-time FX rates
Using the tables below, write a SQL query that converts all transactions to USD using the most recent fx_rates record with the same currency whose rate_date is on or before txn_date (a point-in-time join). If no such FX rate exists for a non-USD currency, set amount_usd to NULL and fx_missing_flag to 1; otherwise fx_missing_flag should be 0. Treat USD transactions as having rate_to_usd = 1 and fx_missing_flag = 0. Output columns: txn_id, txn_date, customer_id, product_id, region, category, segment, amount_usd, fx_missing_flag.
Tables
transactions(txn_id INT, txn_date DATE, customer_id VARCHAR(10), product_id VARCHAR(10), region VARCHAR(10), currency VARCHAR(3), amount DECIMAL(12,2))
fx_rates(currency VARCHAR(3), rate_date DATE, rate_to_usd DECIMAL(10,4))
products(product_id VARCHAR(10), product_name VARCHAR(50), category VARCHAR(50))
customers(customer_id VARCHAR(10), name VARCHAR(50), segment VARCHAR(50))
Hints
- Use a window function over fx_rates joined to transactions to pick the latest rate_date on or before each txn_date.
- Treat USD as a special case with an implicit rate_to_usd of 1 and no missing FX.
Monthly category-by-region USD revenue pivot
Using the same conversion logic as in Question 1, produce a monthly report for the period from 2025-06-01 through 2025-08-31 (inclusive). For each month (derived from txn_date) and product category, compute the sum of amount_usd by region and a grand total. The output should have one row per (month, category) and columns: month (formatted as 'YYYY-MM'), category, na_amount_usd, eu_amount_usd, apac_amount_usd, total_amount_usd. Do not hardcode the months; derive them from txn_date, but filter to the 2025-06 to 2025-08 range.
Tables
transactions(txn_id INT, txn_date DATE, customer_id VARCHAR(10), product_id VARCHAR(10), region VARCHAR(10), currency VARCHAR(3), amount DECIMAL(12,2))
fx_rates(currency VARCHAR(3), rate_date DATE, rate_to_usd DECIMAL(10,4))
products(product_id VARCHAR(10), product_name VARCHAR(50), category VARCHAR(50))
customers(customer_id VARCHAR(10), name VARCHAR(50), segment VARCHAR(50))
Hints
- Reuse the USD conversion logic in a CTE, then aggregate by DATE_TRUNC('month', txn_date) and category.
- Use conditional aggregation (SUM with CASE) to pivot regions into separate columns.
Top customer by USD revenue per region in August 2025
For August 2025 only (txn_date between 2025-08-01 and 2025-08-31 inclusive), using USD-converted amounts as in Question 1, return the top 1 customer by total amount_usd within each region. Break ties by: (1) highest single-transaction amount_usd in that month, then (2) customer_id ascending. Output columns: region, customer_id, customer_name, total_amount_usd.
Tables
transactions(txn_id INT, txn_date DATE, customer_id VARCHAR(10), product_id VARCHAR(10), region VARCHAR(10), currency VARCHAR(3), amount DECIMAL(12,2))
fx_rates(currency VARCHAR(3), rate_date DATE, rate_to_usd DECIMAL(10,4))
products(product_id VARCHAR(10), product_name VARCHAR(50), category VARCHAR(50))
customers(customer_id VARCHAR(10), name VARCHAR(50), segment VARCHAR(50))
Hints
- First aggregate August 2025 transactions to get total and max single-transaction USD amounts per (region, customer).
- Use ROW_NUMBER() partitioned by region with the specified ORDER BY to enforce the tie-breaking rules and pick the top row per region.
Detect transactions missing recent FX rates
Write a SQL query that lists any transactions where, for a non-USD currency, there is no fx_rates record for the same currency with rate_date between txn_date - 30 days and txn_date (inclusive). These transactions should correspond to rows that would have fx_missing_flag = 1 in Question 1. Output columns: txn_id, txn_date, currency, amount, fx_missing_flag. Also, in one sentence (you may include it as a SQL comment), explain how you would monitor this issue in production.
Tables
transactions(txn_id INT, txn_date DATE, customer_id VARCHAR(10), product_id VARCHAR(10), region VARCHAR(10), currency VARCHAR(3), amount DECIMAL(12,2))
fx_rates(currency VARCHAR(3), rate_date DATE, rate_to_usd DECIMAL(10,4))
products(product_id VARCHAR(10), product_name VARCHAR(50), category VARCHAR(50))
customers(customer_id VARCHAR(10), name VARCHAR(50), segment VARCHAR(50))
Hints
- Use a NOT EXISTS or LEFT JOIN/IS NULL pattern from transactions to fx_rates filtered to the 30-day lookback window.
- Remember to exclude USD transactions since they do not rely on fx_rates.