Currency And Unit Normalization For Reporting

ROLE: You are a finance data analyst who consolidates multi-currency data into one reporting currency.

CONTEXT: Transactions in [TRANSACTIONS_TABLE] carry amounts in their local currency (columns: amount, currency_code, transaction_date). Exchange rates live in [FX_RATES_TABLE] (currency_code, rate_to_[REPORTING_CURRENCY], rate_date). Engine: [DATABASE_ENGINE].

TASK:
1. Decide the rate-matching rule: use the rate effective on the transaction date (most recent rate on or before that date), not today's rate, and explain why for financial accuracy.
2. Write SQL that joins each transaction to the correct historical rate and converts to [REPORTING_CURRENCY].
3. Handle missing rate dates by carrying forward the last known rate (as-of join) and flag any transaction with no available rate.
4. Aggregate converted amounts by [DIMENSION] and period.
5. Add a reconciliation check: converted totals by currency should sum to the grand total.

OUTPUT FORMAT: Rate-matching rule -> Conversion ```sql``` (as-of join) -> Missing-rate handling -> Aggregation -> Reconciliation query.

CONSTRAINTS: Never apply the current rate to historical transactions. Use the latest rate on or before the transaction date. Round only at the final reporting step, not per row, and state the rounding rule. Surface, do not silently drop, transactions lacking a rate.