இந்த வாரம் ஷார்ட் ஸ்க்வீஸ் பங்குகள்

WITH prints AS (
    SELECT DISTINCT settlement_date AS d
    FROM global_markets.stocks_short_interest
    ORDER BY d DESC
    LIMIT 2
),
loaded 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
),
legs AS (
    SELECT arrayJoin([
        ('Short interest, previous settlement', (SELECT min(d) FROM prints)),
        ('Short interest, latest settlement', (SELECT max(d) FROM prints)),
        ('Price tape, last fully loaded session', (SELECT max(session) FROM loaded))]) AS row
)
SELECT formatDateTime(row.2, '%b %e, %Y') AS as_of_date,
       row.1 AS leg,
       dateDiff('day', row.2, today()) AS age_days
FROM legs

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

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
),
reported AS (
    SELECT ticker, days_to_cover, avg_daily_volume
    FROM global_markets.stocks_short_interest
    WHERE settlement_date = (SELECT d FROM latest)
),
liquid AS (
    SELECT ticker, days_to_cover
    FROM reported
    WHERE avg_daily_volume >= 5000000
      AND days_to_cover IS NOT NULL
      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())
),
crowded AS (
    SELECT ticker FROM liquid WHERE days_to_cover >= 5
),
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
),
rising 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
),
tally AS (
    SELECT (SELECT count() FROM reported) AS all_names,
           (SELECT count() FROM liquid) AS liquid_names,
           (SELECT count() FROM crowded) AS crowded_names,
           (SELECT count() FROM rising) AS rising_names
)
SELECT step.1 AS label, step.2 AS names
FROM (
    SELECT arrayJoin([
        ('Every name in the settlement file', all_names),
        ('Liquid: five million shares a day, no leveraged funds', liquid_names),
        ('Crowded: five or more days to cover', crowded_names),
        ('Rising: a positive move over the price window', rising_names)]) AS step
    FROM tally
)

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
),
rising 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
),
share_count AS (
    SELECT ticker, market_cap / price AS shares_out
    FROM global_markets.stocks_ratios
    WHERE date = (SELECT max(date) FROM global_markets.stocks_ratios)
      AND market_cap > 0
      AND price > 0
)
SELECT c.ticker AS ticker,
       round(100.0 * c.short_interest / s.shares_out, 1) AS si_pct_shares_out,
       round(c.short_interest / 1e6, 1) AS shares_short_m,
       round(s.shares_out / 1e6, 0) AS shares_out_m,
       round(c.days_to_cover, 1) AS days_to_cover
FROM crowded c
INNER JOIN rising r ON r.ticker = c.ticker
INNER JOIN share_count s ON s.ticker = c.ticker
ORDER BY si_pct_shares_out DESC, c.ticker
LIMIT 10

WITH dates AS (
    SELECT DISTINCT settlement_date AS d
    FROM global_markets.stocks_short_interest
    ORDER BY d DESC
    LIMIT 12
)
SELECT toString(settlement_date) AS settlement_date,
       countIf(days_to_cover >= 5) AS names_5plus_dtc,
       countIf(days_to_cover >= 10) AS names_10plus_dtc,
       round(quantileExact(0.5)(days_to_cover), 2) AS median_dtc_liquid
FROM global_markets.stocks_short_interest
WHERE settlement_date IN (SELECT d FROM dates)
  AND avg_daily_volume >= 5000000
  AND days_to_cover IS NOT NULL
  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')
GROUP BY settlement_date
ORDER BY settlement_date

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