Как сделать Currency Conversion в SQL

Зачем Currency Conversion

Global business — multi-currency revenue. Reporting в USD / EUR требует conversion. Wrong FX rate → wrong reporting → wrong decisions.

Базовый расчёт

Single rate (constant — only для simple cases):

SELECT
    transaction_id,
    amount,
    currency,
    CASE currency
        WHEN 'USD' THEN amount
        WHEN 'EUR' THEN amount * 1.10
        WHEN 'GBP' THEN amount * 1.27
        WHEN 'RUB' THEN amount * 0.011
        ELSE NULL
    END AS amount_usd
FROM transactions;

⚠️ Hardcoded rates быстро outdated. Use rate table.

Daily FX rates

-- fx_rates table: (date, currency, rate_to_usd)

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';

Multi-currency revenue

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;

Aggregate в USD

SELECT
    DATE_TRUNC('month', t.created_at) AS month,
    SUM(t.amount * fx.rate_to_usd) AS revenue_usd,
    COUNT(DISTINCT t.user_id) AS paying_users,
    SUM(t.amount * fx.rate_to_usd) / COUNT(DISTINCT t.user_id) AS arppu_usd
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
ORDER BY 1;

Spot vs Historical

-- Spot rate (today): consistent для current valuation
-- Historical: each txn at txn date's rate

-- Spot:
SELECT
    transaction_id,
    amount,
    amount * (SELECT rate_to_usd FROM fx_rates WHERE currency = t.currency AND DATE = CURRENT_DATE) AS amount_spot_usd
FROM transactions t;

-- Historical (actual)
-- (already shown above)

Historical — actual revenue в USD at time. Spot — current value.

Missing FX rate handling

SELECT
    t.transaction_id,
    t.amount,
    t.currency,
    t.created_at::DATE,
    -- Fallback chain: exact date → most recent → null
    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),
        NULL
    ) AS rate,
    t.amount * 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 amount_usd
FROM transactions t;

Effective FX impact на growth

WITH metrics AS (
    SELECT
        DATE_TRUNC('quarter', created_at) AS quarter,
        currency,
        SUM(amount) AS local_revenue,
        SUM(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
    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;

Частые ошибки

Ошибка 1. Hardcoded rates. 1.1 EUR/USD changes daily. Use rate table.

Ошибка 2. Single rate для year. Used January rate для full year — FX impacts hidden / amplified.

Ошибка 3. Spot всё. Re-valuing historical revenue at spot — distorts reality.

Ошибка 4. Missing dates. Weekend / holiday — no rate. Use most recent.

Ошибка 5. Rate source inconsistency. ECB vs OXR vs internal — different rates. Pick one.

Связанные темы

• Как посчитать revenue в SQL
• Как посчитать GMV в SQL
• Как посчитать geo distribution в SQL
• Как посчитать gross margin в SQL

FAQ

FX source?

ECB (free, EUR-centric). OXR (paid, accurate). Internal bank rate.

Daily, monthly, или quarter rate?

Daily — accurate. Monthly avg — common simplification.

Spot или Historical?

Historical для actuals. Spot для re-valuation.

Weekend rates?

Last business day or interpolate.

Crypto FX?

Volatile. Often hourly rates needed. Snapshot at transaction time.