Coding questions

Build SQL pivot with lookups and currency conversion

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.

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.

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.

Table `transactions`

column	type	constraints
txn_id	INT	PRIMARY KEY, NOT NULL
txn_date	DATE	NOT NULL
customer_id	VARCHAR(10)	NOT NULL
product_id	VARCHAR(10)	NOT NULL
region	VARCHAR(10)	NOT NULL
currency	VARCHAR(3)	NOT NULL
amount	DECIMAL(12,2)	NOT NULL

Sample data:

amount	currency	customer_id	product_id	region	txn_date	txn_id
1200	USD	C1	P1	NA	2025-08-28	1
900	EUR	C2	P2	EU	2025-08-30	2
150000	JPY	C3	P1	APAC	2025-07-15	3
500	USD	C1	P3	NA	2025-06-20	4
700	EUR	C3	P2	EU	2025-08-05	5
300	USD	C2	P3	NA	2025-07-31	6
400	GBP	C2	P1	EU	2025-08-10	7

Table `fx_rates`

column	type	constraints
currency	VARCHAR(3)	NOT NULL
rate_date	DATE	NOT NULL
rate_to_usd	DECIMAL(10,4)	NOT NULL

Sample data:

currency	rate_date	rate_to_usd
EUR	2025-08-29	1.1
EUR	2025-07-31	1.12
JPY	2025-07-10	0.0065
JPY	2025-06-30	0.0068

Table `products`

column	type	constraints
product_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
product_name	VARCHAR(50)	NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	product_id	product_name
SaaS	P1	Alpha
Services	P2	Beta
Hardware	P3	Gamma

Table `customers`

column	type	constraints
customer_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
name	VARCHAR(50)	NOT NULL
segment	VARCHAR(50)	NOT NULL

Sample data:

customer_id	name	segment
C1	Acme	Enterprise
C2	Globex	Mid-Market
C3	Initech	SMB

Expected output

For each transaction, show its details with the product category and customer segment, the USD-equivalent amount using the latest FX rate on or before txn_date, and a flag indicating whether FX was missing (1) or available (0).

amount_usd	category	customer_id	fx_missing_flag	product_id	region	segment	txn_date	txn_id
1200	SaaS	C1	0	P1	NA	Enterprise	2025-08-28	1
990	Services	C2	0	P2	EU	Mid-Market	2025-08-30	2
975	SaaS	C3	0	P1	APAC	SMB	2025-07-15	3
500	Hardware	C1	0	P3	NA	Enterprise	2025-06-20	4
784	Services	C3	0	P2	EU	SMB	2025-08-05	5
300	Hardware	C2	0	P3	NA	Mid-Market	2025-07-31	6
NULL	SaaS	C2	1	P1	EU	Mid-Market	2025-08-10	7

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.

Table `transactions`

column	type	constraints
txn_id	INT	PRIMARY KEY, NOT NULL
txn_date	DATE	NOT NULL
customer_id	VARCHAR(10)	NOT NULL
product_id	VARCHAR(10)	NOT NULL
region	VARCHAR(10)	NOT NULL
currency	VARCHAR(3)	NOT NULL
amount	DECIMAL(12,2)	NOT NULL

Sample data:

amount	currency	customer_id	product_id	region	txn_date	txn_id
1200	USD	C1	P1	NA	2025-08-28	1
900	EUR	C2	P2	EU	2025-08-30	2
150000	JPY	C3	P1	APAC	2025-07-15	3
500	USD	C1	P3	NA	2025-06-20	4
700	EUR	C3	P2	EU	2025-08-05	5
300	USD	C2	P3	NA	2025-07-31	6
400	GBP	C2	P1	EU	2025-08-10	7

Table `fx_rates`

column	type	constraints
currency	VARCHAR(3)	NOT NULL
rate_date	DATE	NOT NULL
rate_to_usd	DECIMAL(10,4)	NOT NULL

Sample data:

currency	rate_date	rate_to_usd
EUR	2025-08-29	1.1
EUR	2025-07-31	1.12
JPY	2025-07-10	0.0065
JPY	2025-06-30	0.0068

Table `products`

column	type	constraints
product_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
product_name	VARCHAR(50)	NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	product_id	product_name
SaaS	P1	Alpha
Services	P2	Beta
Hardware	P3	Gamma

Table `customers`

column	type	constraints
customer_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
name	VARCHAR(50)	NOT NULL
segment	VARCHAR(50)	NOT NULL

Sample data:

customer_id	name	segment
C1	Acme	Enterprise
C2	Globex	Mid-Market
C3	Initech	SMB

Expected output

For each month from 2025-06 to 2025-08 and each product category with activity, show the total USD revenue by region (NA, EU, APAC) and a grand total. Transactions with missing FX (amount_usd NULL) do not contribute to the sums.

apac_amount_usd	category	eu_amount_usd	month	na_amount_usd	total_amount_usd
0	Hardware	0	2025-06	500	500
0	Hardware	0	2025-07	300	300
975	SaaS	0	2025-07	0	975
0	SaaS	0	2025-08	1200	1200
0	Services	1774	2025-08	0	1774

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.

Table `transactions`

column	type	constraints
txn_id	INT	PRIMARY KEY, NOT NULL
txn_date	DATE	NOT NULL
customer_id	VARCHAR(10)	NOT NULL
product_id	VARCHAR(10)	NOT NULL
region	VARCHAR(10)	NOT NULL
currency	VARCHAR(3)	NOT NULL
amount	DECIMAL(12,2)	NOT NULL

Sample data:

amount	currency	customer_id	product_id	region	txn_date	txn_id
1200	USD	C1	P1	NA	2025-08-28	1
900	EUR	C2	P2	EU	2025-08-30	2
150000	JPY	C3	P1	APAC	2025-07-15	3
500	USD	C1	P3	NA	2025-06-20	4
700	EUR	C3	P2	EU	2025-08-05	5
300	USD	C2	P3	NA	2025-07-31	6
400	GBP	C2	P1	EU	2025-08-10	7

Table `fx_rates`

column	type	constraints
currency	VARCHAR(3)	NOT NULL
rate_date	DATE	NOT NULL
rate_to_usd	DECIMAL(10,4)	NOT NULL

Sample data:

currency	rate_date	rate_to_usd
EUR	2025-08-29	1.1
EUR	2025-07-31	1.12
JPY	2025-07-10	0.0065
JPY	2025-06-30	0.0068

Table `products`

column	type	constraints
product_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
product_name	VARCHAR(50)	NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	product_id	product_name
SaaS	P1	Alpha
Services	P2	Beta
Hardware	P3	Gamma

Table `customers`

column	type	constraints
customer_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
name	VARCHAR(50)	NOT NULL
segment	VARCHAR(50)	NOT NULL

Sample data:

customer_id	name	segment
C1	Acme	Enterprise
C2	Globex	Mid-Market
C3	Initech	SMB

Expected output

For August 2025, the top customer by USD revenue for NA and EU regions, using tie-breaking rules (total revenue, then largest single transaction, then customer_id). APAC has no August transactions, so it does not appear.

customer_id	customer_name	region	total_amount_usd
C2	Globex	EU	990
C1	Acme	NA	1200

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.

Table `transactions`

column	type	constraints
txn_id	INT	PRIMARY KEY, NOT NULL
txn_date	DATE	NOT NULL
customer_id	VARCHAR(10)	NOT NULL
product_id	VARCHAR(10)	NOT NULL
region	VARCHAR(10)	NOT NULL
currency	VARCHAR(3)	NOT NULL
amount	DECIMAL(12,2)	NOT NULL

Sample data:

amount	currency	customer_id	product_id	region	txn_date	txn_id
1200	USD	C1	P1	NA	2025-08-28	1
900	EUR	C2	P2	EU	2025-08-30	2
150000	JPY	C3	P1	APAC	2025-07-15	3
500	USD	C1	P3	NA	2025-06-20	4
700	EUR	C3	P2	EU	2025-08-05	5
300	USD	C2	P3	NA	2025-07-31	6
400	GBP	C2	P1	EU	2025-08-10	7

Table `fx_rates`

column	type	constraints
currency	VARCHAR(3)	NOT NULL
rate_date	DATE	NOT NULL
rate_to_usd	DECIMAL(10,4)	NOT NULL

Sample data:

currency	rate_date	rate_to_usd
EUR	2025-08-29	1.1
EUR	2025-07-31	1.12
JPY	2025-07-10	0.0065
JPY	2025-06-30	0.0068

Table `products`

column	type	constraints
product_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
product_name	VARCHAR(50)	NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	product_id	product_name
SaaS	P1	Alpha
Services	P2	Beta
Hardware	P3	Gamma

Table `customers`

column	type	constraints
customer_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
name	VARCHAR(50)	NOT NULL
segment	VARCHAR(50)	NOT NULL

Sample data:

customer_id	name	segment
C1	Acme	Enterprise
C2	Globex	Mid-Market
C3	Initech	SMB

Expected output

All non-USD transactions where there is no FX rate for the same currency with rate_date in the 30 days leading up to and including txn_date. In the sample data, only the GBP transaction on 2025-08-10 is missing any corresponding FX rate.

amount	currency	fx_missing_flag	txn_date	txn_id
400	GBP	1	2025-08-10	7

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.

Convert transactions to USD using point-in-time FX rates

Table transactions

Table fx_rates

Table products

Table customers

Expected output

Hints

Monthly category-by-region USD revenue pivot

Table transactions

Table fx_rates

Table products

Table customers

Expected output

Hints

Top customer by USD revenue per region in August 2025

Table transactions

Table fx_rates

Table products

Table customers

Expected output

Hints

Detect transactions missing recent FX rates

Table transactions

Table fx_rates

Table products

Table customers

Expected output

Hints

Table `transactions`

Table `fx_rates`

Table `products`

Table `customers`

Table `transactions`

Table `fx_rates`

Table `products`

Table `customers`

Table `transactions`

Table `fx_rates`

Table `products`

Table `customers`

Table `transactions`

Table `fx_rates`

Table `products`

Table `customers`