3,256 answered market questions

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