FINRA空売り残高とは？カバー日数の見方

WITH (SELECT max(settlement_date) FROM global_markets.stocks_short_interest) AS latest
SELECT concat(monthName(latest), ' ', toString(toDayOfMonth(latest)), ', ', toString(toYear(latest))) AS latest_settlement_date,
       count() AS tickers_reported,
       multiIf(count() < 1000, toString(count()),
               concat(toString(intDiv(count(), 1000)), ',', lpad(toString(count() % 1000), 3, '0'))) AS tickers_reported_fmt,
       round(sum(si) / 1e9, 1) AS total_shares_short_b
FROM
(
    SELECT ticker, max(short_interest) AS si
    FROM global_markets.stocks_short_interest
    WHERE settlement_date = latest
    GROUP BY ticker
)

SELECT concat(monthName(d), ' ', toString(toDayOfMonth(d)), ', ', toString(toYear(d))) AS settlement_date,
       weekday,
       securities_on_file,
       multiIf(securities_on_file < 1000, toString(securities_on_file),
               concat(toString(intDiv(securities_on_file, 1000)), ',', lpad(toString(securities_on_file % 1000), 3, '0'))) AS securities_on_file_fmt,
       publication_lag_days
FROM
(
    SELECT settlement_date AS d,
           formatDateTime(settlement_date, '%W') AS weekday,
           uniqExact(ticker) AS securities_on_file,
           dateDiff('day', settlement_date, toDate(min(_ingest_time))) AS publication_lag_days
    FROM global_markets.stocks_short_interest
    WHERE settlement_date >= today() - INTERVAL 5 MONTH
    GROUP BY settlement_date
)
ORDER BY d

SELECT concat(monthName(d), ' ', toString(toDayOfMonth(d)), ', ', toString(toYear(d))) AS settlement_date,
       short_interest_m_shares,
       avg_daily_volume_m_shares
FROM
(
    SELECT settlement_date AS d,
           round(max(short_interest) / 1e6, 1) AS short_interest_m_shares,
           round(max(avg_daily_volume) / 1e6, 1) AS avg_daily_volume_m_shares
    FROM global_markets.stocks_short_interest
    WHERE ticker = 'TSLA'
      AND settlement_date >= today() - INTERVAL 2 YEAR
    GROUP BY settlement_date
)
ORDER BY d

WITH (SELECT max(settlement_date) FROM global_markets.stocks_short_interest) AS latest,
shares AS
(
    SELECT tk AS ticker,
           argMax(basic_shares_outstanding, (filing_date, period_end)) AS shares_out
    FROM global_markets.stocks_income_statements
    ARRAY JOIN tickers AS tk
    WHERE tk IN ('AAPL', 'KO', 'MSFT', 'NVDA', 'TSLA', 'GME')
      AND timeframe = 'quarterly'
      AND filing_date >= today() - INTERVAL 1 YEAR
      AND basic_shares_outstanding > 0
    GROUP BY tk
),
si AS
(
    SELECT ticker,
           max(short_interest) AS shares_short,
           max(days_to_cover) AS dtc
    FROM global_markets.stocks_short_interest
    WHERE settlement_date = latest
      AND ticker IN ('AAPL', 'KO', 'MSFT', 'NVDA', 'TSLA', 'GME')
    GROUP BY ticker
)
SELECT si.ticker AS ticker,
       round(si.shares_short / 1e6, 1) AS shares_short_m,
       round(shares.shares_out / 1e6, 0) AS shares_outstanding_m,
       multiIf(shares.shares_out < 1e9, toString(toUInt64(round(shares.shares_out / 1e6, 0))),
               concat(toString(intDiv(toUInt64(round(shares.shares_out / 1e6, 0)), 1000)), ',',
                      lpad(toString(toUInt64(round(shares.shares_out / 1e6, 0)) % 1000), 3, '0'))) AS shares_outstanding_m_fmt,
       round(100.0 * si.shares_short / shares.shares_out, 2) AS short_pct_of_shares_out,
       round(si.dtc, 2) AS days_to_cover
FROM si
INNER JOIN shares ON si.ticker = shares.ticker
ORDER BY indexOf(['AAPL', 'KO', 'MSFT', 'NVDA', 'TSLA', 'GME'], si.ticker)

WITH (SELECT max(settlement_date) FROM global_markets.stocks_short_interest) AS latest
SELECT count() AS liquid_tickers,
       multiIf(count() < 1000, toString(count()),
               concat(toString(intDiv(count(), 1000)), ',', lpad(toString(count() % 1000), 3, '0'))) AS liquid_tickers_fmt,
       round(quantileExact(0.5)(dtc), 1) AS median_days_to_cover,
       round(quantileExact(0.9)(dtc), 1) AS p90_days_to_cover,
       min(dtc) AS lowest_days_to_cover,
       countIf(dtc >= 10) AS names_at_10_plus
FROM
(
    SELECT ticker, max(days_to_cover) AS dtc, max(avg_daily_volume) AS adv
    FROM global_markets.stocks_short_interest
    WHERE settlement_date = latest
    GROUP BY ticker
)
WHERE adv >= 1000000

WITH (SELECT max(settlement_date) FROM global_markets.stocks_short_interest) AS latest,
si AS
(
    SELECT ticker,
           max(short_interest) AS shares_short,
           max(avg_daily_volume) AS adv,
           max(days_to_cover) AS dtc
    FROM global_markets.stocks_short_interest
    WHERE settlement_date = latest
    GROUP BY ticker
    HAVING adv >= 1000000
),
shares AS
(
    SELECT tk AS ticker,
           argMax(basic_shares_outstanding, (filing_date, period_end)) AS shares_out
    FROM global_markets.stocks_income_statements
    ARRAY JOIN tickers AS tk
    WHERE timeframe = 'quarterly'
      AND filing_date >= today() - INTERVAL 9 MONTH
      AND basic_shares_outstanding > 0
    GROUP BY tk
    HAVING shares_out >= 10000000
),
joined AS
(
    SELECT si.ticker AS ticker,
           100.0 * si.shares_short / shares.shares_out AS short_pct,
           si.dtc AS dtc
    FROM si
    INNER JOIN shares ON si.ticker = shares.ticker
    WHERE 100.0 * si.shares_short / shares.shares_out <= 50
)
SELECT multiIf(short_pct < 2, 'Under 2%',
               short_pct < 5, '2-5%',
               short_pct < 10, '5-10%',
               short_pct < 20, '10-20%',
               '20%+') AS short_pct_bucket,
       count() AS tickers,
       round(100.0 * count() / sum(count()) OVER (), 1) AS pct_of_tickers,
       round(quantileExact(0.5)(dtc), 2) AS median_days_to_cover
FROM joined
GROUP BY short_pct_bucket
ORDER BY min(short_pct)

WITH px AS
(
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS day,
           argMax(toFloat64(close), window_start) AS close_px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'GME'
      AND window_start >= '2020-11-01 00:00:00'
      AND window_start < '2021-04-02 00:00:00'
      AND toHour(toTimeZone(window_start, 'America/New_York')) BETWEEN 9 AND 15
    GROUP BY day
),
si AS
(
    SELECT settlement_date,
           round(max(short_interest) / 1e6, 1) AS shares_short_m,
           round(max(days_to_cover), 1) AS days_to_cover
    FROM global_markets.stocks_short_interest
    WHERE ticker = 'GME'
      AND settlement_date >= '2020-11-01'
      AND settlement_date <= '2021-03-31'
    GROUP BY settlement_date
)
SELECT concat(monthName(si.settlement_date), ' ', toString(toDayOfMonth(si.settlement_date)), ', ', toString(toYear(si.settlement_date))) AS settlement_date,
       si.shares_short_m AS shares_short_m,
       si.days_to_cover AS days_to_cover,
       round(px.close_px, 2) AS gme_close
FROM si
INNER JOIN px ON si.settlement_date = px.day
ORDER BY si.settlement_date