공매도 잔고 데이터 지연 이유

WITH first_arrival AS (
    SELECT settlement_date,
           toDate(min(_ingest_time)) AS arrived
    FROM global_markets.stocks_short_interest
    GROUP BY settlement_date
),
bulk_days AS (
    SELECT arrived
    FROM first_arrival
    GROUP BY arrived
    HAVING count() > 5
)
SELECT settlement_date,
       toString(arrived) AS arrived_here,
       dateDiff('day', settlement_date, arrived) AS publication_lag_days
FROM first_arrival
WHERE arrived NOT IN (SELECT arrived FROM bulk_days)
ORDER BY settlement_date

WITH first_arrival AS (
    SELECT settlement_date,
           toDate(min(_ingest_time)) AS arrived
    FROM global_markets.stocks_short_interest
    GROUP BY settlement_date
),
bulk_days AS (
    SELECT arrived
    FROM first_arrival
    GROUP BY arrived
    HAVING count() > 5
),
organic AS (
    SELECT settlement_date,
           dateDiff('day', settlement_date, arrived) AS lag
    FROM first_arrival
    WHERE arrived NOT IN (SELECT arrived FROM bulk_days)
)
SELECT count() AS settlements_measured,
       min(lag) AS fastest_lag_days,
       round(quantileDeterministic(0.5)(lag, cityHash64(settlement_date)), 1) AS median_lag_days,
       max(lag) AS slowest_lag_days,
       (SELECT count() FROM first_arrival WHERE arrived IN (SELECT arrived FROM bulk_days)) AS settlements_bulk_loaded,
       (SELECT toString(max(arrived)) FROM bulk_days) AS bulk_load_date
FROM organic

WITH (SELECT max(settlement_date) FROM global_markets.stocks_short_interest) AS latest
SELECT toString(latest) AS latest_settlement_on_file,
       (SELECT count() FROM global_markets.stocks_short_interest
        WHERE settlement_date = latest) AS securities_in_that_print,
       (SELECT dateDiff('day', latest, toDate(min(_ingest_time))) FROM global_markets.stocks_short_interest
        WHERE settlement_date = latest) AS its_publication_lag_days,
       (SELECT count() FROM global_markets.stocks_short_interest
        WHERE settlement_date > latest AND settlement_date <= latest + 16) AS next_settlement_rows_on_file,
       (SELECT count(DISTINCT date) FROM global_markets.stocks_short_volume
        WHERE date > latest) AS daily_short_volume_files_since,
       (SELECT dateDiff('day', latest, max(date)) FROM global_markets.stocks_short_volume) AS days_from_settlement_to_newest_daily_file

WITH latest AS (SELECT max(settlement_date) AS d FROM global_markets.stocks_short_interest),
arrived AS (
    SELECT toDate(min(_ingest_time)) AS a
    FROM global_markets.stocks_short_interest
    WHERE settlement_date = (SELECT d FROM latest)
),
daily AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS session,
           argMax(toFloat64(close), window_start) AS rth_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL', 'TSLA', 'NVDA', 'GME', 'MU')
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= (SELECT d FROM latest)
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= (SELECT a FROM arrived)
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
          + toMinute(toTimeZone(window_start, 'America/New_York')) BETWEEN 570 AND 959
    GROUP BY ticker, session
)
SELECT si.ticker AS ticker,
       round(si.short_interest / 1e6, 1) AS shares_short_m,
       round(argMin(d.rth_close, d.session), 2) AS close_at_settlement,
       round(argMax(d.rth_close, d.session), 2) AS close_when_published,
       round((argMax(d.rth_close, d.session) / argMin(d.rth_close, d.session) - 1) * 100, 1) AS move_while_pending_pct,
       count() AS sessions_in_window
FROM global_markets.stocks_short_interest AS si
INNER JOIN daily AS d ON d.ticker = si.ticker
WHERE si.settlement_date = (SELECT d FROM latest)
GROUP BY si.ticker, si.short_interest
ORDER BY abs(move_while_pending_pct) DESC, si.ticker ASC

WITH latest AS (SELECT max(settlement_date) AS d FROM global_markets.stocks_short_interest),
arrived AS (
    SELECT toDate(min(_ingest_time)) AS a
    FROM global_markets.stocks_short_interest
    WHERE settlement_date = (SELECT d FROM latest)
),
tape AS (
    SELECT count(DISTINCT toDate(toTimeZone(window_start, 'America/New_York'))) AS sessions,
           sum(toFloat64(volume)) AS shares
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'GME'
      AND toDate(toTimeZone(window_start, 'America/New_York')) > (SELECT d FROM latest)
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= (SELECT a FROM arrived)
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
          + toMinute(toTimeZone(window_start, 'America/New_York')) BETWEEN 570 AND 959
)
SELECT toString((SELECT d FROM latest)) AS settlement,
       round(si.short_interest / 1e6, 1) AS shares_short_m,
       round(si.avg_daily_volume / 1e6, 2) AS file_adv_m,
       round(si.days_to_cover, 2) AS reported_days_to_cover,
       round(tape.shares / tape.sessions / 1e6, 2) AS tape_adv_since_settlement_m,
       round(si.short_interest / (tape.shares / tape.sessions), 2) AS days_to_cover_on_recent_volume,
       round(abs(si.short_interest / (tape.shares / tape.sessions) - si.days_to_cover), 2) AS days_of_difference,
       tape.sessions AS sessions_measured
FROM global_markets.stocks_short_interest AS si, tape
WHERE si.ticker = 'GME'
  AND si.settlement_date = (SELECT d FROM latest)

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS session,
           argMax(toFloat64(close), window_start) AS cl
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'GME'
      AND window_start >= toDateTime('2020-12-01 00:00:00')
      AND window_start < toDateTime('2021-03-15 00:00:00')
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
          + toMinute(toTimeZone(window_start, 'America/New_York')) BETWEEN 570 AND 959
    GROUP BY session
),
ranked AS (
    SELECT session, cl, row_number() OVER (ORDER BY session) AS n
    FROM daily
)
SELECT toString(si.settlement_date) AS settlement,
       round(si.short_interest / 1e6, 1) AS shares_short_m,
       round(r0.cl, 2) AS close_at_settlement,
       round(r8.cl, 2) AS close_8_sessions_later,
       round((r8.cl / r0.cl - 1) * 100, 1) AS move_while_pending_pct
FROM global_markets.stocks_short_interest AS si
INNER JOIN ranked AS r0 ON r0.session = si.settlement_date
INNER JOIN ranked AS r8 ON r8.n = r0.n + 8
WHERE si.ticker = 'GME'
  AND si.settlement_date >= toDate('2020-12-15')
  AND si.settlement_date <= toDate('2021-02-26')
ORDER BY si.settlement_date