Share of Apple volume printed off exchange, by month

WITH
    off_exchange AS
    (
        SELECT
            toStartOfMonth(d) AS m,
            sum(daily_shares) AS off_exchange_shares
        FROM
        (
            SELECT
                date              AS d,
                max(total_volume) AS daily_shares
            FROM global_markets.stocks_short_volume
            WHERE ticker = 'AAPL'
              AND date >= '2025-08-01'
              AND date <  '2026-08-01'
            GROUP BY date
        )
        GROUP BY m
    ),
    consolidated AS
    (
        SELECT
            toStartOfMonth(date) AS m,
            sum(volume)          AS total_shares
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'AAPL'
          AND date >= '2025-08-01'
          AND date <  '2026-08-01'
        GROUP BY m
    )
SELECT
    formatDateTime(o.m, '%Y-%m')                                                 AS month,
    round(100 * toFloat64(o.off_exchange_shares) / toFloat64(c.total_shares), 1) AS off_exchange_pct
FROM off_exchange AS o
INNER JOIN consolidated AS c ON c.m = o.m
ORDER BY month