2,170 answered market questions

WITH latest AS (
    SELECT max(settlement_date) AS d FROM global_markets.stocks_short_interest
),
sessions AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS session
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= now() - INTERVAL 20 DAY
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
        + toMinute(toTimeZone(window_start, 'America/New_York')) >= 570
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
        + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
    GROUP BY session
    HAVING count() >= 380
    ORDER BY session DESC
    LIMIT 6
),
crowded AS (
    SELECT ticker, days_to_cover, short_interest
    FROM global_markets.stocks_short_interest
    WHERE settlement_date = (SELECT d FROM latest)
      AND avg_daily_volume >= 5000000
      AND days_to_cover >= 5
      AND ticker NOT IN ('SPCX')
      AND ticker NOT IN ('KORU','SOXL','SOXS','TQQQ','SQQQ','NVDL','NVDS','NVD','TSLL','TSLQ','TSLZ','SPXL','SPXS','UPRO','SPXU','LABU','LABD','FAS','FAZ','TNA','TZA','YINN','YANG','UDOW','SDOW','BOIL','KOLD','UCO','SCO','USD','SSO','SDS','QLD','QID','ERX','ERY','DRN','DRV','CURE','SOXY','MUU','SNXX','UVXY','SVXY','UVIX','SVIX','BULZ','WEBL','WEBS','DPST','DRIP','GUSH','AGQ','ZSL','BITX','ETHU','MSTX','MSTU','CONL','DUST','JNUG','JDST','NUGT')
      AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits
                         WHERE execution_date BETWEEN today() - 60 AND today())
),
tape 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 crowded)
      AND toDate(toTimeZone(window_start, 'America/New_York')) IN (SELECT session FROM sessions)
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
        + toMinute(toTimeZone(window_start, 'America/New_York')) >= 570
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
        + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
    GROUP BY ticker, session
),
moves AS (
    SELECT ticker,
           round((argMax(rth_close, session) / argMin(rth_close, session) - 1) * 100, 1) AS return_5d_pct
    FROM tape
    GROUP BY ticker
    HAVING count() = 6 AND return_5d_pct > 0
)
SELECT c.ticker AS ticker,
       round(c.days_to_cover, 1) AS days_to_cover,
       round(c.short_interest / 1e6, 1) AS shares_short_m,
       m.return_5d_pct AS return_5d_pct
FROM crowded c
INNER JOIN moves m ON m.ticker = c.ticker
ORDER BY c.days_to_cover DESC, c.ticker
LIMIT 12