Katkat
Back

Build SQL pivot with lookups and currency conversion

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

Last updated

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.

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.