family_paths

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 monthly AS
(
    SELECT
        ticker,
        toStartOfMonth(date)  AS month_start,
        avg(toFloat64(close)) AS avg_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('USO', 'GLD', 'DBA')
      AND date >= toStartOfMonth(today() - 730)
      AND date <  today() - 1
    GROUP BY ticker, month_start
),
base AS
(
    SELECT
        ticker,
        argMin(avg_close, month_start) AS first_close
    FROM monthly
    GROUP BY ticker
)
SELECT
    formatDateTime(m.month_start, '%Y-%m')                               AS month,
    formatDateTime(m.month_start, '%b %Y')                               AS month_label,
    round(maxIf(m.avg_close / b.first_close, m.ticker = 'USO') * 100, 1) AS crude_uso_index,
    round(maxIf(m.avg_close / b.first_close, m.ticker = 'GLD') * 100, 1) AS gold_gld_index,
    round(maxIf(m.avg_close / b.first_close, m.ticker = 'DBA') * 100, 1) AS agric_dba_index
FROM monthly AS m
INNER JOIN base AS b ON b.ticker = m.ticker
GROUP BY m.month_start
HAVING count() = 3
ORDER BY m.month_start