scoreboard

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

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