How to handle Currency Conversion in SQL

Why Currency Conversion matters

It is Friday evening and your CFO pings: "Q1 revenue grew eight percent in USD, but the European team says EUR revenue is flat. Which number do I take to the board on Monday?" Both are true, and the gap is foreign exchange. A multi-currency business — Stripe collecting in forty-plus currencies, Airbnb settling hosts locally, Notion billing teams in EUR and GBP — has to reconcile what customers paid in their own currency with what the holding company reports in USD.

Currency conversion in SQL looks trivial on the whiteboard and brutal in production. The whiteboard answer is amount * rate. The production answer involves a daily rates table, missing-date fallbacks, a choice between spot and historical, and a policy on which FX source to trust. This post walks the recipe an analyst at Stripe or Airbnb would ship.

The SQL formula

In plain English: the USD value of a transaction is the local amount multiplied by the FX rate from local currency to USD on the date the transaction settled. Three columns drive it — amount, currency, and a date — plus a rates table keyed by (date, currency). The simple version is one multiplication. The hard version is everything that protects you when a rate is missing, when the day is a weekend, or when the source disagrees with finance.

The trap most analysts hit is hardcoding rates as constants inside a CASE statement. It works for one quarter and silently rots for the next. Below is the smallest correct shape — a join from transactions to a daily rates table.

SELECT
    t.transaction_id,
    t.amount,
    t.currency,
    t.created_at::DATE AS txn_date,
    fx.rate_to_usd,
    t.amount * fx.rate_to_usd AS amount_usd
FROM transactions t
JOIN fx_rates fx
  ON fx.currency = t.currency
 AND fx.DATE = t.created_at::DATE
WHERE t.created_at >= CURRENT_DATE - INTERVAL '30 days';

The schema assumption is fx_rates(date, currency, rate_to_usd) — one row per (date, currency) pair, rate_to_usd is the multiplier that turns one unit of currency into USD. EUR at 1.10 means one euro is worth one dollar ten cents. Some teams store the inverse — units of local currency per USD — and getting the direction wrong is the single most common bug in this family of queries. Document the direction in the table comment.

Most finance teams seed the rates table from the European Central Bank (free, daily, EUR-centric), OpenExchangeRates or a similar paid API (closer to interbank, hourly available), or an internal treasury feed from your settlement bank. Pick one as the system of record. Mixing sources across reports is how two analysts produce two different revenue numbers from the same transactions.

Multi-currency revenue

The first rollup stakeholders ask for is monthly revenue by currency, with both local totals and a USD-converted view. Local totals matter because regional teams plan in local currency; USD totals matter because the board reports in USD.

SELECT
    DATE_TRUNC('month', t.created_at) AS month,
    t.currency,
    SUM(t.amount) AS local_total,
    SUM(t.amount * fx.rate_to_usd) AS usd_total
FROM transactions t
JOIN fx_rates fx
  ON fx.currency = t.currency
 AND fx.DATE = t.created_at::DATE
WHERE t.status = 'paid'
  AND t.created_at >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY 1, 2
ORDER BY 1, 2;

The status = 'paid' filter is doing real work. A multi-currency transactions table is usually a superset of attempts — authorizations, captures, refunds, chargebacks, three-D-secure failures. Including any of those in a revenue rollup is a finance ticket waiting to happen. The cleanest pattern is a CTE that defines "what counts as revenue" once, referenced everywhere downstream.

Spot vs historical

This is the decision interviewers love to probe. Historical conversion values every transaction at the rate on its own settlement date — what actually hit the bank account in USD. Spot conversion values every transaction at one current rate — a re-valuation of past activity in today's money. Both are correct. They answer different questions.

-- Historical: each transaction at its own date's rate
SELECT
    t.transaction_id,
    t.amount,
    t.amount * fx.rate_to_usd AS amount_historical_usd
FROM transactions t
JOIN fx_rates fx
  ON fx.currency = t.currency
 AND fx.DATE = t.created_at::DATE;

-- Spot: every transaction re-valued at today's rate
SELECT
    t.transaction_id,
    t.amount,
    t.amount * fx_today.rate_to_usd AS amount_spot_usd
FROM transactions t
JOIN fx_rates fx_today
  ON fx_today.currency = t.currency
 AND fx_today.DATE = CURRENT_DATE;

Historical is the right answer for revenue reporting, accounting, audit, and anything that gets compared against bank statements. Spot is the right answer for re-valuing inventory and for stripping out FX volatility to isolate volume changes. A common mistake is reporting historical revenue and spot-valued cost in the same dashboard, then claiming margin compression when the only thing that compressed was a strong dollar. Lock the policy per metric.

Missing FX rate handling

Weekends and bank holidays are the obvious gap. The ECB does not publish on Saturday, Sunday, or Christmas. A naive inner join drops every weekend transaction from the dashboard, and the discrepancy with finance lands as "your USD revenue is two percent low — fix it".

SELECT
    t.transaction_id,
    t.amount,
    t.currency,
    t.created_at::DATE AS txn_date,
    COALESCE(
        (SELECT rate_to_usd FROM fx_rates
         WHERE currency = t.currency
           AND DATE = t.created_at::DATE),
        (SELECT rate_to_usd FROM fx_rates
         WHERE currency = t.currency
           AND DATE <= t.created_at::DATE
         ORDER BY DATE DESC
         LIMIT 1)
    ) AS rate
FROM transactions t;

The fallback chain — exact date, then most recent prior date — matches the policy most finance teams already use for closing the books. Friday's rate carries Saturday and Sunday. The correlated subqueries are slow at scale; the production version uses a fx_rates_filled table where every calendar day has a row. Add an alert when the gap exceeds three days — that almost always means an ingestion failure, not a long holiday. For currencies the source does not cover, return NULL and let downstream reporting decide. Silently coercing missing rates to one is a finance crime.

FX impact on growth

When a CFO asks "how much of revenue growth was real and how much was FX?", the answer is a decomposition. The window-function pattern below computes the quarterly FX move per currency, which feeds the variance attribution.

WITH metrics AS (
    SELECT
        DATE_TRUNC('quarter', t.created_at) AS quarter,
        t.currency,
        SUM(t.amount) AS local_revenue,
        SUM(t.amount * fx.rate_to_usd) AS usd_revenue,
        AVG(fx.rate_to_usd) AS avg_rate
    FROM transactions t
    JOIN fx_rates fx
      ON fx.currency = t.currency
     AND fx.DATE = t.created_at::DATE
    WHERE t.status = 'paid'
    GROUP BY 1, 2
)
SELECT
    quarter,
    currency,
    local_revenue,
    usd_revenue,
    LAG(avg_rate) OVER (PARTITION BY currency ORDER BY quarter) AS prev_rate,
    avg_rate,
    avg_rate - LAG(avg_rate) OVER (PARTITION BY currency ORDER BY quarter) AS fx_change
FROM metrics
ORDER BY quarter, currency;

Pair this view with constant-currency growth — local revenue this quarter versus last — and the board narrative writes itself. Negative fx_change means a stronger dollar hurt the headline; positive means a weaker dollar flattered it.

Common pitfalls

The most expensive pitfall is hardcoding rates inside a CASE expression and forgetting they ever existed. EUR drifted from 1.10 to 1.04 over a single quarter in 2022; a query frozen on 1.10 silently overstated European revenue in USD by six percent, and nobody caught it until the audit. The fix is a daily rates table and a hard rule that no rate ever lives in application code. If you inherit a codebase with hardcoded rates, prioritize replacing them above almost any other technical-debt item.

The second pitfall is using a single rate for a long window — a January rate to value the entire year, or an end-of-quarter rate for all transactions in that quarter. It hides FX volatility entirely and produces a revenue line that diverges from cash by tens of percent on volatile pairs. The defensible policy is daily historical for revenue and a single spot rate only for explicit re-valuation exercises. Never mix them in a single chart.

The third pitfall is re-valuing historical revenue at spot and calling it actuals. A quarter that grew ten percent in USD historical might shrink five percent under spot revaluation if the dollar strengthened. Both numbers are right; only one is actuals. Reporting the spot number as "revenue" instead of "constant-currency revenue" will get caught in a finance review at any company over a hundred million in annualized revenue.

The fourth pitfall is missing-rate handling. An inner join that drops weekend transactions, a LEFT JOIN that leaves a NULL rate, a fallback that uses last year's rate when the feed breaks — each failure is invisible until it is enormous. Add a row-count assertion to the rates pipeline: every business day, every currency you transact in, must have a non-null rate. Freeze dependent dashboards when the assertion fails.

The fifth pitfall is source inconsistency. Finance uses ECB rates because the auditor accepts them. Product pulls OpenExchangeRates because the API is convenient. Treasury uses the bank's internal feed. Three sources produce three USD revenue numbers from identical transactions. Pick one source per metric, write it into the metric definition, and resist switching mid-year — consistency over time matters more than absolute precision.

Optimization tips

On a billion-row transactions table the join in every conversion query is the bottleneck. The first lever is a composite index on fx_rates(currency, date) — without it Postgres will hash the whole rates table per query and you pay the cost on every dashboard refresh. Snowflake and BigQuery handle this with clustering keys on the same two columns.

The second lever is a fx_rates_filled materialized view. Generate one row per (currency, calendar_day) pair, carry forward the most recent published rate across weekends and holidays, and refresh nightly. Every downstream conversion query then becomes a single inner join with no COALESCE or correlated subquery — latency drops by an order of magnitude, and the carry-forward logic lives in one place where it can be audited.

The third lever is a pre-aggregated daily_revenue_usd table refreshed by a dbt model overnight, shaped (date, currency, local_revenue, usd_revenue). Most monthly and quarterly rollups become small joins on this aggregate instead of full scans of transactions, and the same model feeds ARPPU, AOV, and cohort analysis without re-running the FX join.

Related reading

• How to calculate ARPU in SQL
• How to calculate ARPPU in SQL
• How to calculate AOV in SQL
• How to calculate Cohort Revenue in SQL
• SQL window functions interview questions

To drill multi-currency SQL daily, NAILDD is launching with 500+ SQL problems on this pattern.

FAQ

Which FX source should I use — ECB, OpenExchangeRates, or my bank's feed?

ECB is the default for European-headquartered companies because auditors expect it; it is free, daily, and EUR-centric, which means non-EUR pairs are derived rather than direct. OpenExchangeRates and similar paid feeds are closer to the interbank market and offer hourly granularity, which matters for crypto and emerging-market currencies. An internal treasury feed from your settlement bank is the most defensible source for revenue recognition because it matches what moved between accounts. Pick one source per metric, document it, and never silently switch.

Should I use daily, monthly average, or quarterly rates?

Daily is the right default for revenue and any metric compared against cash. Monthly averages are a common simplification for cohort and longitudinal analysis where the noise of daily rates obscures the trend. Quarterly averages show up in board decks for the same smoothing reason but are inappropriate for anything that touches accounting. Use the most granular rate the metric can tolerate without becoming noisy.

When does spot conversion make sense?

Spot conversion makes sense any time the question is "what is this worth today?" — inventory revaluation, balance-sheet translation, scenario modeling, or cross-market comparisons where historical FX would distort the comparison. It does not make sense for revenue reporting, because revenue is a flow that already happened at historical rates. The classic misuse is re-valuing last year's revenue at today's spot and reporting it as growth. Label any spot-converted metric explicitly as "constant-currency".

How should I handle weekend and holiday rates?

Carry the most recent published rate forward across the gap. The ECB stops publishing on Friday evening and resumes Monday morning, so a Saturday or Sunday transaction is valued at Friday's rate. Build this into a fx_rates_filled materialized view rather than re-implementing the fallback in every query. Add an alert that fires when the gap exceeds three calendar days, because that usually means ingestion broke rather than a long bank holiday.

How do I convert crypto transactions?

Crypto rates move fast enough that daily granularity is rarely adequate — a single Bitcoin transaction valued at the daily close can be off by ten percent from the rate at the moment of settlement during a volatile day. The defensible pattern is to snapshot the rate at the timestamp of the transaction and store it on the transaction row itself, so downstream queries do not need to re-derive it from a feed that may have changed retroactively. Hourly rates from a reputable exchange are the minimum granularity.

How do refunds work across currency moves?

A refund issued today for a charge made three months ago raises a real question — value the refund at today's rate or the original rate? The accounting answer is the original rate, because the refund offsets revenue recognized at that rate. The cash answer is today's rate, because that is what leaves the bank account. The gap is FX gain or loss and lands on a separate line in the income statement. In SQL, attach the original created_at and currency to the refund row at ingestion time.