Share of consolidated volume reported off exchange, by month

WITH
facility AS
(
    SELECT
        ticker,
        date,
        max(toFloat64(total_volume)) AS facility_shares
    FROM global_markets.stocks_short_volume
    WHERE date >= '2025-08-01'
      AND date <  '2026-08-01'
      AND ticker IN ('AAPL', 'KO', 'SPY')
    GROUP BY ticker, date
),
tape AS
(
    SELECT
        ticker,
        date,
        max(toFloat64(volume)) AS consolidated_shares
    FROM global_markets.stocks_daily_aggs
    WHERE date >= '2025-08-01'
      AND date <  '2026-08-01'
      AND ticker IN ('AAPL', 'KO', 'SPY')
    GROUP BY ticker, date
),
joined AS
(
    SELECT
        f.ticker              AS ticker,
        f.date                AS date,
        f.facility_shares     AS facility_shares,
        t.consolidated_shares AS consolidated_shares
    FROM facility AS f
    INNER JOIN tape AS t ON f.ticker = t.ticker AND f.date = t.date
)
SELECT
    formatDateTime(date, '%Y-%m') AS month,
    formatDateTime(date, '%b %Y') AS month_label,
    round(100 * sumIf(facility_shares, ticker = 'AAPL') / sumIf(consolidated_shares, ticker = 'AAPL'), 1) AS aapl_pct,
    round(100 * sumIf(facility_shares, ticker = 'KO')   / sumIf(consolidated_shares, ticker = 'KO'), 1)   AS ko_pct,
    round(100 * sumIf(facility_shares, ticker = 'SPY')  / sumIf(consolidated_shares, ticker = 'SPY'), 1)  AS spy_pct
FROM joined
GROUP BY month, month_label
HAVING countIf(ticker = 'AAPL') > 0
   AND countIf(ticker = 'KO') > 0
   AND countIf(ticker = 'SPY') > 0
ORDER BY month