공매도 잔고 상위 종목 및 커버 데이즈 분석

SELECT ticker,
       round(days_to_cover, 1) AS days_to_cover,
       round(short_interest / 1e6, 1) AS shares_short_m,
       round(avg_daily_volume / 1e6, 1) AS avg_daily_volume_m,
       toString(settlement_date) AS settled
FROM global_markets.stocks_short_interest
WHERE settlement_date = (SELECT max(settlement_date) FROM global_markets.stocks_short_interest)
  AND avg_daily_volume >= 5000000
  AND days_to_cover IS NOT NULL
  AND ticker NOT IN ('SPCX')
ORDER BY days_to_cover DESC, ticker
LIMIT 10

WITH dates AS (
    SELECT DISTINCT settlement_date AS d
    FROM global_markets.stocks_short_interest
    ORDER BY d DESC
    LIMIT 8
),
top3 AS (
    SELECT ticker, row_number() OVER (ORDER BY days_to_cover DESC, ticker) AS rank
    FROM global_markets.stocks_short_interest
    WHERE settlement_date = (SELECT max(d) FROM dates)
      AND avg_daily_volume >= 5000000
      AND days_to_cover IS NOT NULL
      AND ticker NOT IN ('SPCX')
    ORDER BY days_to_cover DESC, ticker
    LIMIT 3
)
SELECT toString(settlement_date) AS settlement_date,
       maxIf(round(days_to_cover, 1), ticker = (SELECT ticker FROM top3 WHERE rank = 1)) AS current_leader_dtc,
       maxIf(round(days_to_cover, 1), ticker = (SELECT ticker FROM top3 WHERE rank = 2)) AS second_place_dtc,
       maxIf(round(days_to_cover, 1), ticker = (SELECT ticker FROM top3 WHERE rank = 3)) AS third_place_dtc
FROM global_markets.stocks_short_interest
WHERE ticker IN (SELECT ticker FROM top3)
  AND settlement_date IN (SELECT d FROM dates)
GROUP BY settlement_date
ORDER BY settlement_date

SELECT ticker,
       round(short_interest / 1e6, 1) AS shares_short_m,
       round(days_to_cover, 1) AS days_to_cover,
       round(avg_daily_volume / 1e6, 1) AS avg_daily_volume_m
FROM global_markets.stocks_short_interest
WHERE settlement_date = (SELECT max(settlement_date) FROM global_markets.stocks_short_interest)
  AND avg_daily_volume >= 5000000
  AND short_interest IS NOT NULL
  AND ticker NOT IN ('SPCX')
ORDER BY short_interest DESC, ticker
LIMIT 10

WITH dates AS (
    SELECT DISTINCT settlement_date AS d
    FROM global_markets.stocks_short_interest
    ORDER BY d DESC
    LIMIT 2
)
SELECT ticker,
       round(anyIf(days_to_cover, settlement_date = (SELECT min(d) FROM dates)), 1) AS prior_dtc,
       round(anyIf(days_to_cover, settlement_date = (SELECT max(d) FROM dates)), 1) AS latest_dtc,
       round(anyIf(days_to_cover, settlement_date = (SELECT max(d) FROM dates))
             - anyIf(days_to_cover, settlement_date = (SELECT min(d) FROM dates)), 1) AS dtc_change
FROM global_markets.stocks_short_interest
WHERE settlement_date IN (SELECT d FROM dates)
  AND avg_daily_volume >= 5000000
  AND days_to_cover IS NOT NULL
  AND ticker NOT IN ('SPCX')
GROUP BY ticker
HAVING count() = 2 AND dtc_change >= 0.1
ORDER BY dtc_change DESC, ticker
LIMIT 8

WITH top5 AS (
    SELECT ticker, round(days_to_cover, 1) AS dtc
    FROM global_markets.stocks_short_interest
    WHERE settlement_date = (SELECT max(settlement_date) FROM global_markets.stocks_short_interest)
      AND avg_daily_volume >= 5000000
      AND days_to_cover IS NOT NULL
      AND ticker NOT IN ('SPCX')
    ORDER BY days_to_cover DESC, ticker
    LIMIT 5
),
daily AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS session,
           argMax(close, window_start) AS rth_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN (SELECT ticker FROM top5)
      AND window_start >= now() - INTERVAL 35 DAY
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
          + toMinute(toTimeZone(window_start, 'America/New_York')) BETWEEN 570 AND 959
    GROUP BY ticker, session
),
moves AS (
    SELECT ticker,
           count() AS sessions,
           toString(min(session)) AS from_session,
           toString(max(session)) AS to_session,
           round((argMax(rth_close, session) / argMin(rth_close, session) - 1) * 100, 1) AS return_pct
    FROM daily
    GROUP BY ticker
    HAVING sessions >= 15
)
SELECT m.ticker AS ticker,
       t.dtc AS days_to_cover,
       m.return_pct AS return_pct,
       m.from_session AS from_session,
       m.to_session AS to_session
FROM moves m
INNER JOIN top5 t ON m.ticker = t.ticker
ORDER BY t.dtc DESC, m.ticker

SELECT toString(settlement_date) AS settlement_date,
       round(short_interest / 1e6, 1) AS shares_short_m,
       round(avg_daily_volume / 1e6, 1) AS avg_daily_volume_m,
       round(days_to_cover, 1) AS days_to_cover
FROM global_markets.stocks_short_interest
WHERE ticker = 'GME'
  AND settlement_date BETWEEN '2020-11-01' AND '2021-03-31'
ORDER BY settlement_date

WITH dates AS (
    SELECT DISTINCT settlement_date AS d
    FROM global_markets.stocks_short_interest
    ORDER BY d DESC
    LIMIT 2
),
top10_dtc_latest AS (
    SELECT ticker FROM global_markets.stocks_short_interest
    WHERE settlement_date = (SELECT max(d) FROM dates) AND avg_daily_volume >= 5000000
      AND days_to_cover IS NOT NULL AND ticker NOT IN ('SPCX')
    ORDER BY days_to_cover DESC, ticker LIMIT 10
),
top10_dtc_prior AS (
    SELECT ticker FROM global_markets.stocks_short_interest
    WHERE settlement_date = (SELECT min(d) FROM dates) AND avg_daily_volume >= 5000000
      AND days_to_cover IS NOT NULL AND ticker NOT IN ('SPCX')
    ORDER BY days_to_cover DESC, ticker LIMIT 10
),
size10_latest AS (
    SELECT ticker, days_to_cover FROM global_markets.stocks_short_interest
    WHERE settlement_date = (SELECT max(d) FROM dates) AND avg_daily_volume >= 5000000
      AND short_interest IS NOT NULL AND ticker NOT IN ('SPCX')
    ORDER BY short_interest DESC, ticker LIMIT 10
)
SELECT
    (SELECT count() FROM global_markets.stocks_short_interest
     WHERE settlement_date = (SELECT max(d) FROM dates)) AS tickers_reported,
    (SELECT count() FROM global_markets.stocks_short_interest
     WHERE settlement_date = (SELECT max(d) FROM dates) AND avg_daily_volume >= 5000000) AS liquid_tickers,
    (SELECT round(quantileDeterministic(0.5)(days_to_cover, cityHash64(ticker)), 1)
     FROM global_markets.stocks_short_interest
     WHERE settlement_date = (SELECT max(d) FROM dates) AND avg_daily_volume >= 5000000
       AND days_to_cover IS NOT NULL) AS liquid_median_dtc,
    (SELECT count() FROM top10_dtc_latest
     WHERE ticker IN (SELECT ticker FROM top10_dtc_prior)) AS dtc_top10_repeats,
    (SELECT round(min(days_to_cover), 1) FROM size10_latest) AS size_top10_min_dtc