Latest days to cover, and where it sits in five years of the same name's history

WITH history AS
(
    SELECT
        ticker,
        settlement_date,
        max(toFloat64(days_to_cover))  AS dtc,
        max(toFloat64(short_interest)) AS si
    FROM global_markets.stocks_short_interest
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'KO', 'XOM', 'JNJ')
      AND settlement_date >= today() - 1825
      AND days_to_cover > 0
    GROUP BY ticker, settlement_date
),
latest AS
(
    SELECT
        ticker,
        argMax(dtc, settlement_date)   AS latest_dtc,
        argMax(si, settlement_date)    AS latest_si,
        toString(max(settlement_date)) AS snapshot_label
    FROM history
    GROUP BY ticker
)
SELECT
    l.ticker                                                 AS symbol,
    l.snapshot_label                                         AS snapshot_label,
    round(l.latest_si / 1e6, 1)                              AS short_interest_millions,
    round(l.latest_dtc, 2)                                   AS days_to_cover_ratio,
    round(100 * countIf(h.dtc <= l.latest_dtc) / count(), 0) AS percentile_5y_pct
FROM history AS h
INNER JOIN latest AS l ON h.ticker = l.ticker
GROUP BY l.ticker, l.snapshot_label, l.latest_si, l.latest_dtc
ORDER BY percentile_5y_pct DESC