How should I practice Daily USD Totals with Effective-Dated Exchange Rates?

Start by reading the full statement, solve the visible examples, then compare the pattern against related Microsoft onsite interview problems in FastPrep.

Is Daily USD Totals with Effective-Dated Exchange Rates associated with Microsoft?

FastPrep tags this as a Microsoft onsite interview coding practice problem when the problem catalog has that company and stage context.

Daily USD Totals with Effective-Dated Exchange Rates

Microsoft● MediumFULLTIMEONSITE INTERVIEW

Problem statement

The transactions table records payments in many currencies. The exchange_rates table records how many US dollars one unit of a currency is worth, but each row has only a start_date.

A rate takes effect on its start_date and stays in effect until the day before the next start_date for the same currency. The latest rate for a currency has no end date. Convert each transaction to US dollars with the rate of its currency that is in effect on its transaction_date: amount * rate_to_usd.

For each date that has at least one transaction, return transaction_date and total_usd, the sum of that day's converted amounts. Add the exact converted amounts first, then round each daily total to two decimal places, with halves rounded away from zero. Sort the rows by transaction_date from earliest to latest.

Table schema

MySQLPostgreSQLPandas

Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.

transactions

Column	Type	Nullable	Description
transaction_idPK	Integer	No	—
transaction_date	Date	No	—
currency	Text	No	—
amount	Decimal	No	—

exchange_rates

Column	Type	Nullable	Description
currency	Text	No	—
start_date	Date	No	—
rate_to_usd	Decimal	No	—

Expected result

Your query or function must return these columns.

Column	Type	Nullable	Description
transaction_date	Date	No	—
total_usd	Decimal	No	—

Row order: must match exactly. Numeric tolerance: 0.

Constraints

transaction_id is a unique positive integer. Each (currency, start_date) pair in exchange_rates is unique.
Currencies are three-letter uppercase codes. US dollar transactions use USD, which has its own rows in exchange_rates.
Every transaction has at least one rate for its currency whose start_date is on or before its transaction_date.
amount is a positive decimal of at most 1000000.00 with at most two decimal places. rate_to_usd is a positive decimal of at most 100000 with at most six decimal places. No value is null.
Each table has at most 2000 rows.