2,170 answered market questions

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