brecha

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH nke AS
(
    SELECT
        date,
        argMax(toFloat64(close), _ingest_time) AS px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'NKE'
      AND date >= '2021-01-01'
    GROUP BY date
),
euro AS
(
    SELECT
        date,
        argMax(toFloat64(close), _ingest_time) AS px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'FXE'
      AND date >= '2021-01-01'
    GROUP BY date
),
diario AS
(
    SELECT
        n.date       AS date,
        n.px         AS usd,
        n.px / e.px  AS eur
    FROM nke AS n
    INNER JOIN euro AS e ON e.date = n.date
),
extremos AS
(
    SELECT
        argMax(date, usd)                          AS fecha_pico_usd,
        max(usd)                                   AS pico_usd,
        argMax(date, eur)                          AS fecha_pico_eur,
        max(eur)                                   AS pico_eur,
        argMax(usd, date)                          AS ultimo_usd,
        argMax(eur, date)                          AS ultimo_eur,
        argMinIf(usd, date, date >= today() - 365) AS hace12m_usd,
        argMinIf(eur, date, date >= today() - 365) AS hace12m_eur
    FROM diario
),
valles AS
(
    SELECT
        minIf(d.usd, d.date >= x.fecha_pico_usd) AS valle_usd,
        minIf(d.eur, d.date >= x.fecha_pico_eur) AS valle_eur
    FROM diario AS d
    CROSS JOIN extremos AS x
)
SELECT
    t.1                                        AS medida,
    round(t.2, 1)                              AS variacion_en_dolares,
    round(t.3, 1)                              AS variacion_en_euros,
    round(round(t.3, 1) - round(t.2, 1), 1)    AS brecha_en_puntos
FROM
(
    SELECT arrayJoin([
        ('Del maximo al minimo',        (v.valle_usd  / x.pico_usd    - 1) * 100, (v.valle_eur  / x.pico_eur    - 1) * 100),
        ('Del maximo al ultimo cierre', (x.ultimo_usd / x.pico_usd    - 1) * 100, (x.ultimo_eur / x.pico_eur    - 1) * 100),
        ('Ultimos doce meses',          (x.ultimo_usd / x.hace12m_usd - 1) * 100, (x.ultimo_eur / x.hace12m_eur - 1) * 100)
    ]) AS t
    FROM extremos AS x
    CROSS JOIN valles AS v
)