Convert Currencies

SQL recipe for converting financial data between currencies using DataHawk's exchange rate referential table.

See Daily Exchange Rate Dataset for details on the underlying exchange rate table.

The snippet below performs the conversion of financial data on a dataset without a currency field by joining to the marketplace referential table that contains the currency field.

SET target_currency = 'EUR';

SELECT
    s.workspace_id       AS workspace_id,
    s.marketplace_key    AS marketplace_key,
    CAST(s.date AS DATE) AS date_day,

    s.visits             AS clicks,
    s.views              AS views,
    s.sales              AS sales,
    s.units              AS units,

    mp.currency_code     AS local_currency,
    $target_currency     AS target_currency,

    -- Local -> USD (divide since rate is base_to_usd)
    fx_local.rate        AS fx_rate_local_to_usd,
    s.sales / fx_local.rate AS sales_usd,

    -- USD -> Target currency (multiply since rate is target_to_usd)
    fx_target.rate       AS fx_rate_target_to_usd,
    (s.sales / fx_local.rate) * fx_target.rate AS sales_target

FROM ADVERTISING.AMZN_BRAND_STORE_METRICS s

LEFT JOIN REFERENTIAL.REFERENTIAL_MARKETPLACE mp
    ON s.marketplace_key = mp.marketplace_key

-- Local currency -> USD
LEFT JOIN REFERENTIAL.REFERENTIAL_CURRENCY_RATE fx_local
    ON CAST(s.date AS DATE) = CAST(fx_local.date_day AS DATE)
   AND mp.currency_code = fx_local.currency

-- Target currency -> USD (used to convert USD -> target)
LEFT JOIN REFERENTIAL.REFERENTIAL_CURRENCY_RATE fx_target
    ON CAST(s.date AS DATE) = CAST(fx_target.date_day AS DATE)
   AND fx_target.currency = $target_currency;