Weekly share turnover across US companies, four-week average

SELECT
    multiIf(
        weekly_turnover_pct < 1, 'under 1%',
        weekly_turnover_pct < 2, '1% to 2%',
        weekly_turnover_pct < 4, '2% to 4%',
        weekly_turnover_pct < 8, '4% to 8%',
                                 '8% or more') AS turnover_bucket,
    count()                                    AS companies
FROM
(
    SELECT 100 * (v.weekly_volume / s.shares_out) AS weekly_turnover_pct
    FROM
    (
        SELECT
            ticker,
            toFloat64(sum(volume)) / 4 AS weekly_volume
        FROM global_markets.stocks_daily_aggs
        WHERE date >= today() - 32
          AND date <  today() - 4
          AND ticker NOT IN ('SPCX')
        GROUP BY ticker
        HAVING weekly_volume > 0
    ) AS v
    INNER JOIN
    (
        SELECT
            arrayJoin(tickers)                                                     AS ticker,
            toFloat64(argMax(basic_shares_outstanding, (filing_date, period_end))) AS shares_out,
            max(period_end)                                                        AS latest_period
        FROM global_markets.stocks_income_statements
        GROUP BY ticker
        HAVING shares_out > 1000000
           AND latest_period >= today() - 400
    ) AS s ON s.ticker = v.ticker
)
GROUP BY turnover_bucket
ORDER BY min(weekly_turnover_pct)