Daily Exchange Rate Dataset

Attached is a snippet that 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;