일평균 거래량(ADV)의 정의와 계산 방법

WITH per_day AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
           sumIf(toFloat64(volume), formatDateTime(toTimeZone(window_start, 'America/New_York'), '%H:%i') >= '09:30'
                                AND formatDateTime(toTimeZone(window_start, 'America/New_York'), '%H:%i') < '16:00') AS rth_shares,
           sumIf(toFloat64(close) * toFloat64(volume), formatDateTime(toTimeZone(window_start, 'America/New_York'), '%H:%i') >= '09:30'
                                                   AND formatDateTime(toTimeZone(window_start, 'America/New_York'), '%H:%i') < '16:00') AS rth_dollars,
           sum(toFloat64(volume)) AS all_shares
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'AAPL', 'MU', 'KO', 'CATO')
      AND window_start >= toDateTime('2026-06-11 00:00:00', 'America/New_York')
      AND window_start < toDateTime('2026-07-11 00:00:00', 'America/New_York')
    GROUP BY ticker, et_date
)
SELECT ticker,
       round(avg(rth_shares) / 1e6, 2) AS adv_shares_m,
       round(avg(rth_dollars) / 1e9, 2) AS adv_dollars_bn,
       round(100 * (sum(all_shares) - sum(rth_shares)) / sum(all_shares), 1) AS extended_hours_pct,
       count() AS sessions
FROM per_day
GROUP BY ticker
ORDER BY adv_shares_m DESC

SELECT session_date,
       shares_m,
       round(sum(shares_m) OVER (), 1) AS five_day_total_m,
       round(avg(shares_m) OVER (), 1) AS adv_m,
       round(avg(shares_m) OVER () * 0.01 * 1000, 0) AS one_pct_of_adv_k
FROM (
    SELECT formatDateTime(toDate(toTimeZone(window_start, 'America/New_York')), '%Y-%m-%d') AS session_date,
           round(sumIf(toFloat64(volume), formatDateTime(toTimeZone(window_start, 'America/New_York'), '%H:%i') >= '09:30'
                                      AND formatDateTime(toTimeZone(window_start, 'America/New_York'), '%H:%i') < '16:00') / 1e6, 1) AS shares_m
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'KO'
      AND window_start >= toDateTime('2026-07-06 00:00:00', 'America/New_York')
      AND window_start < toDateTime('2026-07-11 00:00:00', 'America/New_York')
    GROUP BY session_date
)
ORDER BY session_date

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
           sum(toFloat64(volume)) AS day_shares
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'MU'
      AND window_start >= toDateTime('2025-08-01 00:00:00', 'America/New_York')
      AND window_start < toDateTime('2026-07-11 00:00:00', 'America/New_York')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY et_date
),
rolled AS (
    SELECT et_date,
           avg(day_shares) OVER (ORDER BY et_date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS adv20,
           avg(day_shares) OVER (ORDER BY et_date ROWS BETWEEN 89 PRECEDING AND CURRENT ROW) AS adv90,
           row_number() OVER (ORDER BY et_date) AS rn,
           count() OVER () AS total_rows
    FROM daily
)
SELECT formatDateTime(et_date, '%Y-%m-%d') AS date,
       round(adv20 / 1e6, 1) AS adv_20_session_m,
       round(adv90 / 1e6, 1) AS adv_90_session_m
FROM rolled
WHERE rn > 90 AND (rn % 3 = total_rows % 3)
ORDER BY et_date

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
           sum(toFloat64(volume)) AS day_shares
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'MU'
      AND window_start >= toDateTime('2025-11-01 00:00:00', 'America/New_York')
      AND window_start < toDateTime('2026-07-11 00:00:00', 'America/New_York')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY et_date
),
ranked AS (
    SELECT et_date, day_shares,
           day_shares / avg(day_shares) OVER (ORDER BY et_date ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING) AS shock_ratio,
           row_number() OVER (ORDER BY et_date) AS rn
    FROM daily
),
biggest AS (
    SELECT et_date, day_shares, shock_ratio
    FROM ranked
    WHERE rn > 20 AND et_date >= toDate('2026-01-01')
    ORDER BY shock_ratio DESC, et_date ASC
    LIMIT 1
),
before AS (
    SELECT avg(day_shares) AS adv FROM (
        SELECT day_shares FROM daily WHERE et_date < (SELECT et_date FROM biggest) ORDER BY et_date DESC LIMIT 20
    )
),
after AS (
    SELECT avg(day_shares) AS adv FROM (
        SELECT day_shares FROM daily WHERE et_date >= (SELECT et_date FROM biggest) ORDER BY et_date ASC LIMIT 20
    )
)
SELECT formatDateTime((SELECT et_date FROM biggest), '%Y-%m-%d') AS event_date,
       round((SELECT day_shares FROM biggest) / 1e6, 1) AS event_day_shares_m,
       round((SELECT adv FROM before) / 1e6, 1) AS adv_20_before_m,
       round((SELECT shock_ratio FROM biggest), 1) AS event_vs_prior_adv_x,
       round((SELECT adv FROM after) / 1e6, 1) AS adv_20_after_m,
       round(100 * ((SELECT adv FROM after) / (SELECT adv FROM before) - 1), 0) AS adv_shift_pct

WITH per_ticker AS (
    SELECT ticker, avg(day_dollars) AS adv_dollars
    FROM (
        SELECT ticker,
               toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
               sumIf(toFloat64(close) * toFloat64(volume), formatDateTime(toTimeZone(window_start, 'America/New_York'), '%H:%i') >= '09:30'
                                                       AND formatDateTime(toTimeZone(window_start, 'America/New_York'), '%H:%i') < '16:00') AS day_dollars
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE window_start >= toDateTime('2026-06-11 00:00:00', 'America/New_York')
          AND window_start < toDateTime('2026-07-11 00:00:00', 'America/New_York')
          AND match(ticker, '^[A-Z]+$')
        GROUP BY ticker, et_date
    )
    GROUP BY ticker
    HAVING count() >= 15
)
SELECT count() AS names,
       round(quantileDeterministic(0.25)(adv_dollars, cityHash64(ticker)) / 1e6, 2) AS p25_m,
       round(quantileDeterministic(0.50)(adv_dollars, cityHash64(ticker)) / 1e6, 1) AS median_m,
       round(quantileDeterministic(0.75)(adv_dollars, cityHash64(ticker)) / 1e6, 1) AS p75_m,
       round(quantileDeterministic(0.90)(adv_dollars, cityHash64(ticker)) / 1e6, 1) AS p90_m,
       round(quantileDeterministic(0.99)(adv_dollars, cityHash64(ticker)) / 1e6, 0) AS p99_m,
       countIf(adv_dollars >= 1e6) AS names_above_1m,
       countIf(adv_dollars >= 10e6) AS names_above_10m,
       countIf(adv_dollars >= 100e6) AS names_above_100m,
       countIf(adv_dollars >= 1e9) AS names_above_1bn
FROM per_ticker

WITH our_adv AS (
    SELECT ticker, avg(day_shares) AS adv
    FROM (
        SELECT ticker,
               toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
               sumIf(toFloat64(volume), formatDateTime(toTimeZone(window_start, 'America/New_York'), '%H:%i') >= '09:30'
                                    AND formatDateTime(toTimeZone(window_start, 'America/New_York'), '%H:%i') < '16:00') AS day_shares
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker IN ('MU', 'KO')
          AND window_start >= toDateTime('2026-06-11 00:00:00', 'America/New_York')
          AND window_start < toDateTime('2026-07-11 00:00:00', 'America/New_York')
        GROUP BY ticker, et_date
    )
    GROUP BY ticker
),
si AS (
    SELECT ticker,
           max(short_interest) AS short_interest,
           max(avg_daily_volume) AS finra_adv,
           max(days_to_cover) AS finra_dtc
    FROM global_markets.stocks_short_interest
    WHERE settlement_date = '2026-06-30' AND ticker IN ('MU', 'KO')
    GROUP BY ticker
)
SELECT si.ticker AS ticker,
       round(si.short_interest / 1e6, 1) AS shares_short_m,
       round(si.finra_adv / 1e6, 1) AS finra_adv_m,
       round(si.finra_dtc, 2) AS finra_days_to_cover,
       round(our_adv.adv / 1e6, 1) AS rth_adv_20_session_m,
       round(si.short_interest / our_adv.adv, 2) AS days_to_cover_recomputed
FROM si
INNER JOIN our_adv ON si.ticker = our_adv.ticker
ORDER BY ticker

WITH daily AS (
    SELECT multiIf(ticker LIKE 'O:SPY2%', 'SPY', ticker LIKE 'O:AAPL2%', 'AAPL', ticker LIKE 'O:MU2%', 'MU', 'KO') AS underlying,
           toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
           sum(toFloat64(volume)) AS contracts
    FROM global_markets.options_minute_aggs
    WHERE (ticker LIKE 'O:SPY2%' OR ticker LIKE 'O:AAPL2%' OR ticker LIKE 'O:MU2%' OR ticker LIKE 'O:KO2%')
      AND window_start >= toDateTime('2026-06-11 00:00:00', 'America/New_York')
      AND window_start < toDateTime('2026-07-11 00:00:00', 'America/New_York')
    GROUP BY underlying, et_date
)
SELECT underlying,
       round(avg(contracts) / 1e3, 1) AS avg_daily_contracts_k,
       round(avg(contracts) * 100 / 1e6, 1) AS share_equivalent_m,
       count() AS sessions
FROM daily
GROUP BY underlying
ORDER BY avg_daily_contracts_k DESC