Foreign listings during New York hours, sorted by the US large cap move

WITH day_moves AS
(
    SELECT
        ticker,
        date,
        (toFloat64(close) / toFloat64(open) - 1) * 100 AS open_to_close_pct
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'JPM', 'XOM', 'KO', 'SONY', 'SAP')
      AND date >= '2021-08-01'
      AND date <  '2026-08-01'
      AND open > 0
),
paired AS
(
    SELECT
        date,
        avgIf(open_to_close_pct, ticker IN ('AAPL', 'MSFT', 'JPM', 'XOM', 'KO')) AS us_large_cap_pct,
        avgIf(open_to_close_pct, ticker = 'SONY')                                AS japan_pct,
        avgIf(open_to_close_pct, ticker = 'SAP')                                 AS europe_pct
    FROM day_moves
    GROUP BY date
    HAVING countIf(ticker IN ('AAPL', 'MSFT', 'JPM', 'XOM', 'KO')) = 5
       AND countIf(ticker = 'SONY') = 1
       AND countIf(ticker = 'SAP')  = 1
)
SELECT
    multiIf(us_large_cap_pct <= -1.0, 'US large caps: -1% or lower',
            us_large_cap_pct <= -0.3, 'US large caps: -1% to -0.3%',
            us_large_cap_pct <   0.3, 'US large caps: -0.3% to +0.3%',
            us_large_cap_pct <   1.0, 'US large caps: +0.3% to +1%',
                                      'US large caps: +1% or higher') AS us_market_move_bucket,
    round(avg(japan_pct), 3)  AS japan_adr_pct,
    round(avg(europe_pct), 3) AS europe_adr_pct,
    count()                   AS observation_count
FROM paired
GROUP BY us_market_move_bucket
ORDER BY min(us_large_cap_pct)