AAPL ارننگ ڈے موو: رات کا گیپ اور PKT ٹائم لائن

WITH
reports AS
(
    SELECT DISTINCT filing_date AS report_date
    FROM global_markets.stocks_8k_text
    WHERE ticker = 'AAPL'
      AND startsWith(form_type, '8-K')
      AND filing_date >= '2023-09-01'
      AND (positionCaseInsensitive(items_text, 'Results of Operations') > 0
           OR positionCaseInsensitive(items_text, 'Item 2.02') > 0)
),
bars AS
(
    SELECT
        date,
        toFloat64(any(open))  AS open_px,
        toFloat64(any(close)) AS close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'AAPL'
      AND date >= '2023-08-01'
    GROUP BY date
),
seq AS
(
    SELECT
        date,
        open_px,
        close_px,
        row_number() OVER (ORDER BY date) AS n
    FROM bars
)
SELECT
    toString(d0.date)                               AS report_date,
    formatDateTime(d0.date, '%b %e, %Y')            AS report_label,
    round((d1.open_px / d0.close_px - 1) * 100, 2)  AS gap_pct,
    round((d1.close_px / d0.close_px - 1) * 100, 2) AS full_day_pct
FROM seq AS d0
INNER JOIN seq AS d1 ON d1.n = d0.n + 1
INNER JOIN reports AS r ON r.report_date = d0.date
ORDER BY d0.date

WITH
reports AS
(
    SELECT DISTINCT filing_date AS report_date
    FROM global_markets.stocks_8k_text
    WHERE ticker = 'AAPL'
      AND startsWith(form_type, '8-K')
      AND filing_date >= '2023-09-01'
      AND (positionCaseInsensitive(items_text, 'Results of Operations') > 0
           OR positionCaseInsensitive(items_text, 'Item 2.02') > 0)
),
bars AS
(
    SELECT
        date,
        toFloat64(any(open))  AS open_px,
        toFloat64(any(close)) AS close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'AAPL'
      AND date >= '2023-08-01'
    GROUP BY date
),
seq AS
(
    SELECT date, open_px, close_px, row_number() OVER (ORDER BY date) AS n
    FROM bars
),
per_report AS
(
    SELECT
        d0.date                                  AS report_date,
        formatDateTime(d0.date, '%b %e, %Y')     AS report_label,
        abs(d1.open_px / d0.close_px - 1) * 100  AS abs_gap_pct,
        abs(d1.close_px / d0.close_px - 1) * 100 AS abs_full_day_pct
    FROM seq AS d0
    INNER JOIN seq AS d1 ON d1.n = d0.n + 1
    INNER JOIN reports AS r ON r.report_date = d0.date
),
stats AS
(
    SELECT
        round(quantileDeterministic(0.5)(abs_gap_pct, toUInt64(toUnixTimestamp(report_date))), 2)      AS median_gap,
        round(quantileDeterministic(0.5)(abs_full_day_pct, toUInt64(toUnixTimestamp(report_date))), 2) AS median_day,
        round(max(abs_gap_pct), 2)                                                                     AS max_gap,
        round(max(abs_full_day_pct), 2)                                                                AS max_day,
        argMax(report_label, abs_gap_pct)                                                              AS max_gap_report,
        argMax(report_label, abs_full_day_pct)                                                         AS max_day_report
    FROM per_report
)
SELECT
    ['اوورنائٹ گیپ', 'پورے دن کی حرکت'][idx] AS label,
    [median_gap, median_day][idx]            AS median_abs_pct,
    [max_gap, max_day][idx]                  AS largest_abs_pct,
    [max_gap_report, max_day_report][idx]    AS largest_on
FROM stats
ARRAY JOIN [1, 2] AS idx

WITH
reports AS
(
    SELECT DISTINCT filing_date AS report_date
    FROM global_markets.stocks_8k_text
    WHERE ticker = 'AAPL'
      AND startsWith(form_type, '8-K')
      AND filing_date >= '2023-09-01'
      AND (positionCaseInsensitive(items_text, 'Results of Operations') > 0
           OR positionCaseInsensitive(items_text, 'Item 2.02') > 0)
),
bars AS
(
    SELECT
        date,
        toFloat64(any(open))  AS open_px,
        toFloat64(any(close)) AS close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'AAPL'
      AND date >= '2023-08-01'
    GROUP BY date
),
seq AS
(
    SELECT date, open_px, close_px, row_number() OVER (ORDER BY date) AS n
    FROM bars
),
per_report AS
(
    SELECT
        d0.date                                  AS report_date,
        abs(d1.close_px / d0.close_px - 1) * 100 AS abs_full_day_pct
    FROM seq AS d0
    INNER JOIN seq AS d1 ON d1.n = d0.n + 1
    INNER JOIN reports AS r ON r.report_date = d0.date
),
atm_raw AS
(
    SELECT
        date,
        days_to_expiry,
        implied_volatility
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND date >= '2023-09-01'
      AND iv_converged = 1
      AND volume > 0
      AND days_to_expiry BETWEEN 1 AND 10
      AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.025
),
nearest AS
(
    SELECT date, min(days_to_expiry) AS dte
    FROM atm_raw
    GROUP BY date
),
atm AS
(
    SELECT
        a.date                                                                   AS date,
        round(avg(a.implied_volatility * sqrt(a.days_to_expiry / 365)) * 100, 2) AS implied_move_pct
    FROM atm_raw AS a
    INNER JOIN nearest AS n ON n.date = a.date AND a.days_to_expiry = n.dte
    GROUP BY a.date
    HAVING count() > 0
)
SELECT
    toString(p.report_date)                                   AS report_date,
    a.implied_move_pct                                        AS implied_move_pct,
    round(p.abs_full_day_pct, 2)                              AS realised_move_pct,
    round(p.abs_full_day_pct - a.implied_move_pct, 2)         AS realised_minus_implied_pct
FROM per_report AS p
INNER JOIN atm AS a ON a.date = p.report_date
ORDER BY p.report_date

WITH
reports AS
(
    SELECT DISTINCT filing_date AS report_date
    FROM global_markets.stocks_8k_text
    WHERE ticker = 'AAPL'
      AND startsWith(form_type, '8-K')
      AND filing_date >= '2023-09-01'
      AND (positionCaseInsensitive(items_text, 'Results of Operations') > 0
           OR positionCaseInsensitive(items_text, 'Item 2.02') > 0)
),
bars AS
(
    SELECT
        date,
        toFloat64(any(open))  AS open_px,
        toFloat64(any(close)) AS close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'AAPL'
      AND date >= '2023-08-01'
    GROUP BY date
),
seq AS
(
    SELECT date, open_px, close_px, row_number() OVER (ORDER BY date) AS n
    FROM bars
),
atm_raw AS
(
    SELECT date, days_to_expiry, implied_volatility
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND date >= '2023-09-01'
      AND iv_converged = 1
      AND volume > 0
      AND days_to_expiry BETWEEN 1 AND 10
      AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.025
),
nearest AS
(
    SELECT date, min(days_to_expiry) AS dte
    FROM atm_raw
    GROUP BY date
),
atm AS
(
    SELECT
        a.date                                                                   AS date,
        round(avg(a.implied_volatility * sqrt(a.days_to_expiry / 365)) * 100, 2) AS implied_move_pct
    FROM atm_raw AS a
    INNER JOIN nearest AS n ON n.date = a.date AND a.days_to_expiry = n.dte
    GROUP BY a.date
),
per_report AS
(
    SELECT
        d0.date                                  AS report_date,
        abs(d1.open_px / d0.close_px - 1) * 100  AS abs_gap_pct,
        abs(d1.close_px / d0.close_px - 1) * 100 AS abs_full_day_pct,
        sign(d1.open_px - d0.close_px)           AS gap_dir,
        sign(d1.close_px - d0.close_px)          AS day_dir,
        ifNull(a.implied_move_pct, 0)            AS implied_move_pct
    FROM seq AS d0
    INNER JOIN seq AS d1 ON d1.n = d0.n + 1
    INNER JOIN reports AS r ON r.report_date = d0.date
    LEFT JOIN atm AS a ON a.date = d0.date
),
tally AS
(
    SELECT
        countIf(abs_full_day_pct > 0 AND abs_gap_pct / abs_full_day_pct >= 0.8)   AS gap_heavy,
        countIf(gap_dir = day_dir)                                               AS same_dir,
        countIf(abs_gap_pct >= 3)                                                AS big_gap,
        countIf(implied_move_pct > 0 AND abs_full_day_pct < implied_move_pct)     AS under_implied,
        count()                                                                  AS reports_total
    FROM per_report
)
SELECT
    ['گیپ نے دن کی حرکت کا 80% یا اس سے زیادہ پکڑا',
     'گیپ اور دن کی بندش ایک ہی سمت میں',
     'گیپ 3% یا اس سے بڑا',
     'اصل حرکت متوقع حرکت سے کم رہی',
     'ونڈو کی کل رپورٹیں'][idx]                                 AS label,
    [gap_heavy, same_dir, big_gap, under_implied, reports_total][idx] AS report_count
FROM tally
ARRAY JOIN [1, 2, 3, 4, 5] AS idx

SELECT
    formatDateTime(toStartOfInterval(toTimeZone(sip_timestamp, 'America/New_York'), INTERVAL 10 MINUTE), '%H:%i') AS et_time,
    round(avg((toFloat64(ask_price) - toFloat64(bid_price))
              / ((toFloat64(ask_price) + toFloat64(bid_price)) / 2) * 10000), 2) AS avg_spread_bps,
    round(quantileDeterministic(0.95)((toFloat64(ask_price) - toFloat64(bid_price))
              / ((toFloat64(ask_price) + toFloat64(bid_price)) / 2) * 10000, toUInt64(sequence_number)), 2) AS p95_spread_bps,
    round(count() / 1000000, 3) AS quote_count_millions
FROM global_markets.cache_stocks_quotes
WHERE ticker = 'AAPL'
  AND sip_timestamp >= '2025-01-31 14:00:00'
  AND sip_timestamp <  '2025-01-31 16:00:00'
  AND bid_price > 0
  AND ask_price > bid_price
  AND bid_size > 0
  AND ask_size > 0
GROUP BY et_time
ORDER BY et_time