2,178 answered market questions

WITH screened AS (
    SELECT settlement_date, ticker
    FROM global_markets.stocks_short_interest
    WHERE settlement_date >= today() - 400
      AND settlement_date <= today() - 55
      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')
),
split_hits AS (
    SELECT s.settlement_date AS sd, s.ticker AS tkr
    FROM screened s
    INNER JOIN global_markets.stocks_splits sp ON sp.ticker = s.ticker
    WHERE sp.execution_date > s.settlement_date + 11
      AND sp.execution_date <= s.settlement_date + 50
),
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 DISTINCT ticker FROM screened)
      AND window_start >= now() - INTERVAL 400 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 ticker, session
),
paired AS (
    SELECT s.settlement_date AS sd,
           s.ticker AS tkr,
           (argMaxIf(d.rth_close, d.session, d.session <= s.settlement_date + 20)
            / argMinIf(d.rth_close, d.session, d.session <= s.settlement_date + 20) - 1) * 100 AS prior_pct,
           (argMaxIf(d.rth_close, d.session, d.session >= s.settlement_date + 20)
            / argMinIf(d.rth_close, d.session, d.session >= s.settlement_date + 20) - 1) * 100 AS next_pct
    FROM screened s
    INNER JOIN daily d ON d.ticker = s.ticker
    WHERE d.session > s.settlement_date + 11
      AND d.session <= s.settlement_date + 50
    GROUP BY sd, tkr
    HAVING countIf(d.session <= s.settlement_date + 20) >= 4
       AND countIf(d.session >= s.settlement_date + 20) >= 15
),
outcomes AS (
    SELECT multiIf(next_pct < -20, 1,
                   next_pct < -10, 2,
                   next_pct < 0, 3,
                   next_pct < 10, 4,
                   next_pct < 20, 5, 6) AS bucket,
           multiIf(next_pct < -20, 'Fell more than 20%',
                   next_pct < -10, 'Fell 10% to 20%',
                   next_pct < 0, 'Fell 0% to 10%',
                   next_pct < 10, 'Rose 0% to 10%',
                   next_pct < 20, 'Rose 10% to 20%', 'Rose more than 20%') AS move_over_30_days
    FROM paired
    WHERE prior_pct > 0
      AND (sd, tkr) NOT IN (SELECT sd, tkr FROM split_hits)
),
tallied AS (
    SELECT bucket, move_over_30_days, count() AS name_count
    FROM outcomes
    GROUP BY bucket, move_over_30_days
)
SELECT move_over_30_days,
       name_count,
       round(100.0 * sum(name_count) OVER (ORDER BY bucket) / sum(name_count) OVER (), 1) AS cumulative_share_pct
FROM tallied
ORDER BY bucket