Build SQL pivot with lookups and currency conversion
Company: Other
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.
Quick Answer: 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.