Daily rates

1-- core.forex_v2.daily_rates
2-- The conversion table other models join to. Every day, every pair of currencies
3-- the connector quotes, gap-filled, including the identity pair (EUR to EUR = 1) so
4-- a join never has to special-case amounts already in the target currency.
5--
6-- Grain: date x from_currency x to_currency.
7-- Depends on: core.forex_v2.daily_base_rates
8--
9-- DIRECTION. amount_in_to_currency = amount_in_from_currency * rate.
10--
11-- HOW EACH RATE IS MADE. The connector quotes everything against one base (USD by
12-- default), so:
13--   identity  from = to                         rate = 1
14--   direct    from = base                       rate = base rate of `to`
15--   inverse   to = base                         rate = 1 / base rate of `from`
16--   cross     neither is the base               rate = base rate of `to` / base rate of `from`
17-- A cross rate through the base is how Open Exchange Rates computes non-USD bases
18-- too, so it matches what the EUR stream itself would give.
19--
20-- SIZE. With about 170 currencies this is roughly 29,000 pairs per day. Materialise
21-- it as a table. If you only ever convert into one or two reporting currencies,
22-- restrict `to_currencies` below and the table shrinks by two orders of magnitude.
23
24WITH base AS (
25    SELECT date, base_currency, quote_currency, rate, rate_source_date, is_carried_forward
26    FROM {{core.forex_v2.daily_base_rates}}
27    WHERE rate IS NOT NULL
28),
29
30-- Every currency on every day, the base itself included at rate 1.
31currencies AS (
32    SELECT date, base_currency, quote_currency AS currency, rate, rate_source_date, is_carried_forward
33    FROM base
34    WHERE quote_currency <> base_currency
35    UNION ALL
36    SELECT DISTINCT date, base_currency, base_currency, 1.0, date, FALSE
37    FROM base
38),
39
40-- Restrict here to cut the table down, e.g. WHERE currency IN ('EUR', 'USD').